{"action":"created","issue":{"url":"https://api.github.com/repos/sequelize/sequelize/issues/4333","labels_url":"https://api.github.com/repos/sequelize/sequelize/issues/4333/labels{/name}","comments_url":"https://api.github.com/repos/sequelize/sequelize/issues/4333/comments","events_url":"https://api.github.com/repos/sequelize/sequelize/issues/4333/events","html_url":"https://github.com/sequelize/sequelize/issues/4333","id":101116026,"number":4333,"title":"findOrCreate hangs, if only one connection is available","user":{"login":"User4martin","id":7429624,"avatar_url":"https://avatars.githubusercontent.com/u/7429624?v=3","gravatar_id":"","url":"https://api.github.com/users/User4martin","html_url":"https://github.com/User4martin","followers_url":"https://api.github.com/users/User4martin/followers","following_url":"https://api.github.com/users/User4martin/following{/other_user}","gists_url":"https://api.github.com/users/User4martin/gists{/gist_id}","starred_url":"https://api.github.com/users/User4martin/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/User4martin/subscriptions","organizations_url":"https://api.github.com/users/User4martin/orgs","repos_url":"https://api.github.com/users/User4martin/repos","events_url":"https://api.github.com/users/User4martin/events{/privacy}","received_events_url":"https://api.github.com/users/User4martin/received_events","type":"User","site_admin":false},"labels":[{"url":"https://api.github.com/repos/sequelize/sequelize/labels/bug","name":"bug","color":"d7e102"}],"state":"open","locked":false,"assignee":null,"milestone":null,"comments":6,"created_at":"2015-08-14T22:34:53Z","updated_at":"2015-08-16T13:09:56Z","closed_at":null,"body":"In the example the maximum amount of conections is set to 1.\r\n\r\nfindOrCreate does not return (or maybe it will at the idle time...)\r\n\r\nIf \"max:2\", then it works (giving an exception due to unique violation.\r\n```\r\nvar S = require('sequelize');\r\n\r\nvar sequelize = new S('seq_test', 'passwd', '', {\r\n    host: 'localhost',\r\n    dialect: 'postgres',\r\n    pool: { max: 1, \r\n            min: 0, idle: 10000, },\r\n    define: {  },\r\n});\r\n\r\nvar User = sequelize.define('User', {\r\n    name: { type: S.STRING(50), allowNull: true, unique: true},\r\n    pass: { type: S.STRING(50), allowNull: true},\r\n});\r\n\r\nsequelize.sync()\r\n.then(function (){\r\n    User.create({ name: 'A', pass: 'A' })\r\n    .then(function (){\r\n        User.findOrCreate({\r\n          defaults: { name: 'A', pass: 'A' },\r\n          where: { name: 'B' }\r\n        })\r\n        .catch(function (e){\r\n            console.log('ERR', e);    \r\n        })\r\n        .then(function (){\r\n            console.log('DONE');\r\n            sequelize.close();\r\n        });\r\n    });\r\n});\r\n```"},"comment":{"url":"https://api.github.com/repos/sequelize/sequelize/issues/comments/131547740","html_url":"https://github.com/sequelize/sequelize/issues/4333#issuecomment-131547740","issue_url":"https://api.github.com/repos/sequelize/sequelize/issues/4333","id":131547740,"user":{"login":"User4martin","id":7429624,"avatar_url":"https://avatars.githubusercontent.com/u/7429624?v=3","gravatar_id":"","url":"https://api.github.com/users/User4martin","html_url":"https://github.com/User4martin","followers_url":"https://api.github.com/users/User4martin/followers","following_url":"https://api.github.com/users/User4martin/following{/other_user}","gists_url":"https://api.github.com/users/User4martin/gists{/gist_id}","starred_url":"https://api.github.com/users/User4martin/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/User4martin/subscriptions","organizations_url":"https://api.github.com/users/User4martin/orgs","repos_url":"https://api.github.com/users/User4martin/repos","events_url":"https://api.github.com/users/User4martin/events{/privacy}","received_events_url":"https://api.github.com/users/User4martin/received_events","type":"User","site_admin":false},"created_at":"2015-08-16T13:09:56Z","updated_at":"2015-08-16T13:09:56Z","body":"> The approach you suggested was my original approach #2518, but it does suffer from race conditions. We are going to rewrite it to use ON DUPLICATE KEY once postgres 9.5 is out\r\n\r\nOk, I read up a bit on this. Since \"ON DUPLICATE KEY \" is only for postgres 9.5 and up, I assume this is still relevant. I.e. I assume that older postgres will still be supported.\r\n\r\n- 1)\r\nYes the \"WITH\" has a race condition. But it only means you might get a unique constraint error.\r\nAnd the current code already issues a further \"select\" in this case. So the entry will be fetched anyway.\r\n\r\n- 2) \r\nThe current approach\r\n```\r\nCREATE OR REPLACE FUNCTION pg_temp.testfunc(OUT response \"Users\", OUT sequelize_caught_exception text) RETURNS RECORD AS $func_2fd2b475321945a393ec06970192d010$ \r\nBEGIN \r\n  INSERT INTO \"Users\" (\"id\",\"name\",\"pass\",\"updatedAt\",\"createdAt\") \r\n    VALUES (DEFAULT,'A','A','2015-08-16 11:38:33.789 +00:00','2015-08-16 11:38:33.789 +00:00') \r\n    RETURNING * INTO response; \r\n  EXCEPTION WHEN unique_violation THEN \r\n      GET STACKED DIAGNOSTICS sequelize_caught_exception = PG_EXCEPTION_DETAIL; END $func_2fd2b475321945a393ec06970192d010$ LANGUAGE plpgsql; \r\nSELECT (testfunc.response).*, testfunc.sequelize_caught_exception FROM pg_temp.testfunc(); DROP FUNCTION IF EXISTS pg_temp.testfunc();\r\n\r\n// currently outside transaction // only if testfunc failde to insert (duplicate key exception)\r\nSELECT \"id\", \"name\", \"pass\", \"createdAt\", \"updatedAt\" FROM \"Users\" AS \"User\" WHERE \"User\".\"name\" = 'B' LIMIT 1;\r\n``` \r\nsuffers a different race.\r\n\r\nIf I have 2 applications\r\n* app 1: Deletes (not paranoid, so really delete the row from db) the user \"A\"\r\n* app 2: Find or create the user \"A\"\r\n\r\nThe expected result is either (depending which one goes first)\r\n* 1) app2 got the old user (even though it is no longer in the db)\r\n* 2) app2 got a new user.\r\n\r\nIn either case app2 should have a user.\r\n\r\nBut if app 1 drops the user after the call to \"testfunc\", but before the final select, then app 2 ends up with no user at all. That is not expected either.\r\n\r\n---\r\nThe \"WITH\" query (if also followed by a further select, in case of an duplicate key error) does not have that issue, because it selects first.\r\n\r\nIt does however suffers in a different scenario: \r\nIt will also fail to return any user at all, if \r\n- during the with, another app inserted a user, that was not seen by the select, but that prevented the insert\r\n- and if the other app (or any other app) deleted this inserted user again, before it could be selected by the final select.\r\n\r\n--- \r\nTo really avoid any race, I guess a explicit lock will be needed.\r\n\r\n\r\n\r\n"}}