{"action":"created","issue":{"url":"https://api.github.com/repos/oar-team/oar/issues/60","labels_url":"https://api.github.com/repos/oar-team/oar/issues/60/labels{/name}","comments_url":"https://api.github.com/repos/oar-team/oar/issues/60/comments","events_url":"https://api.github.com/repos/oar-team/oar/issues/60/events","html_url":"https://github.com/oar-team/oar/issues/60","id":108318423,"number":60,"title":"Consecutive oarnodesetting commands results in deadlock in oar Postgres database","user":{"login":"clement-parisot","id":6361556,"avatar_url":"https://avatars.githubusercontent.com/u/6361556?v=3","gravatar_id":"","url":"https://api.github.com/users/clement-parisot","html_url":"https://github.com/clement-parisot","followers_url":"https://api.github.com/users/clement-parisot/followers","following_url":"https://api.github.com/users/clement-parisot/following{/other_user}","gists_url":"https://api.github.com/users/clement-parisot/gists{/gist_id}","starred_url":"https://api.github.com/users/clement-parisot/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/clement-parisot/subscriptions","organizations_url":"https://api.github.com/users/clement-parisot/orgs","repos_url":"https://api.github.com/users/clement-parisot/repos","events_url":"https://api.github.com/users/clement-parisot/events{/privacy}","received_events_url":"https://api.github.com/users/clement-parisot/received_events","type":"User","site_admin":false},"labels":[{"url":"https://api.github.com/repos/oar-team/oar/labels/2.5","name":"2.5","color":"fef2c0"},{"url":"https://api.github.com/repos/oar-team/oar/labels/g5k","name":"g5k","color":"c7def8"},{"url":"https://api.github.com/repos/oar-team/oar/labels/question","name":"question","color":"cc317c"}],"state":"open","locked":false,"assignee":{"login":"capitn","id":8918129,"avatar_url":"https://avatars.githubusercontent.com/u/8918129?v=3","gravatar_id":"","url":"https://api.github.com/users/capitn","html_url":"https://github.com/capitn","followers_url":"https://api.github.com/users/capitn/followers","following_url":"https://api.github.com/users/capitn/following{/other_user}","gists_url":"https://api.github.com/users/capitn/gists{/gist_id}","starred_url":"https://api.github.com/users/capitn/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/capitn/subscriptions","organizations_url":"https://api.github.com/users/capitn/orgs","repos_url":"https://api.github.com/users/capitn/repos","events_url":"https://api.github.com/users/capitn/events{/privacy}","received_events_url":"https://api.github.com/users/capitn/received_events","type":"User","site_admin":false},"milestone":null,"comments":4,"created_at":"2015-09-25T12:43:46Z","updated_at":"2015-10-08T09:35:52Z","closed_at":null,"body":"Hello,\r\nI've got the following problem when executing several `oarnodesetting` commands that modify **State** of a node and another node property called **comment** (char property of a node).\r\n\r\nThe command:\r\n```bash\r\noarnodesetting -h \"graphene-37.nancy.grid5000.fr\" -p \"comment=OK\"\r\n```\r\nError on the client side:\r\n```\r\nUpdate property comment with value OK on node graphene-37.nancy.grid5000.fr ...DBD::Pg::db do failed: ERROR:  deadlock detected\r\nDETAIL:  Process 6950 waits for ShareLock on transaction 188143; blocked by process 6949.\r\nProcess 6949 waits for ShareLock on transaction 188144; blocked by process 6950.\r\nHINT:  See server log for query details. at /usr/share/perl5/OAR/IO.pm line 5529.\r\nERROR (wrong property or wrong value)\r\n```\r\n\r\nError on postgres server:\r\n```SQL\r\n2015-09-25 13:37:03 CEST [unknown] oar@172.16.79.101(49642) ERROR:  deadlock detected\r\n2015-09-25 13:37:03 CEST [unknown] oar@172.16.79.101(49642) DETAIL:  Process 6950 waits for ShareLock on transaction 188143; blocked by process 6949.\r\n\tProcess 6949 waits for ShareLock on transaction 188144; blocked by process 6950.\r\n\tProcess 6950: UPDATE resources\r\n\t                                    SET comment = 'OK'\r\n\t                                    WHERE \r\n\t                                        resources.network_address = 'graphene-37.nancy.grid5000.fr'\r\n\t                                   \r\n\tProcess 6949:   UPDATE resources\r\n\t                SET state = 'Alive', finaud_decision = 'NO', state_num = 1\r\n\t                WHERE\r\n\t                    resource_id = 2293\r\n\t             \r\n2015-09-25 13:37:03 CEST [unknown] oar@172.16.79.101(49642) HINT:  See server log for query details.\r\n2015-09-25 13:37:03 CEST [unknown] oar@172.16.79.101(49642) STATEMENT:  UPDATE resources\r\n\t                                    SET comment = 'OK'\r\n\t                                    WHERE \r\n\t                                        resources.network_address = 'graphene-37.nancy.grid5000.fr'\r\n\t                                   \r\n```"},"comment":{"url":"https://api.github.com/repos/oar-team/oar/issues/comments/146472837","html_url":"https://github.com/oar-team/oar/issues/60#issuecomment-146472837","issue_url":"https://api.github.com/repos/oar-team/oar/issues/60","id":146472837,"user":{"login":"lnussbaum","id":12544,"avatar_url":"https://avatars.githubusercontent.com/u/12544?v=3","gravatar_id":"","url":"https://api.github.com/users/lnussbaum","html_url":"https://github.com/lnussbaum","followers_url":"https://api.github.com/users/lnussbaum/followers","following_url":"https://api.github.com/users/lnussbaum/following{/other_user}","gists_url":"https://api.github.com/users/lnussbaum/gists{/gist_id}","starred_url":"https://api.github.com/users/lnussbaum/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/lnussbaum/subscriptions","organizations_url":"https://api.github.com/users/lnussbaum/orgs","repos_url":"https://api.github.com/users/lnussbaum/repos","events_url":"https://api.github.com/users/lnussbaum/events{/privacy}","received_events_url":"https://api.github.com/users/lnussbaum/received_events","type":"User","site_admin":false},"created_at":"2015-10-08T09:35:52Z","updated_at":"2015-10-08T09:35:52Z","body":"En fait le problème n'est pas que deux transactions touchent aux mêmes lignes. Ca, c'est géré par postgresql. Le problème est que deux transactions modifient des lignes de la table resources, dans un ordre différent. Donc on est exactement dans le cas décrit au 13.3.4. Deadlocks de http://www.postgresql.org/docs/9.4/static/explicit-locking.html\r\n\r\nIl y a deux solutions:\r\n1) (propre) identifier toutes les mises à jour de la table resources, et vérifier qu'à l'intérieur d'une même transaction, elles sont faites dans le même ordre de resource_id. Actuellement, ce n'est le cas ni dans oarnodesetting, ni dans le code d'Almighty qui réagit aux changements d'état, je crois.\r\n2) (sale) réintroduire des locks par table (plutot que par row) avec des commandes LOCK TABLE explicites, au moins pour la table resources."}}