{"action":"created","issue":{"url":"https://api.github.com/repos/hasgeek/coaster/issues/58","labels_url":"https://api.github.com/repos/hasgeek/coaster/issues/58/labels{/name}","comments_url":"https://api.github.com/repos/hasgeek/coaster/issues/58/comments","events_url":"https://api.github.com/repos/hasgeek/coaster/issues/58/events","html_url":"https://github.com/hasgeek/coaster/issues/58","id":117287177,"number":58,"title":"Handle IntegrityError from race conditions","user":{"login":"jace","id":26695,"avatar_url":"https://avatars.githubusercontent.com/u/26695?v=3","gravatar_id":"","url":"https://api.github.com/users/jace","html_url":"https://github.com/jace","followers_url":"https://api.github.com/users/jace/followers","following_url":"https://api.github.com/users/jace/following{/other_user}","gists_url":"https://api.github.com/users/jace/gists{/gist_id}","starred_url":"https://api.github.com/users/jace/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/jace/subscriptions","organizations_url":"https://api.github.com/users/jace/orgs","repos_url":"https://api.github.com/users/jace/repos","events_url":"https://api.github.com/users/jace/events{/privacy}","received_events_url":"https://api.github.com/users/jace/received_events","type":"User","site_admin":false},"labels":[],"state":"closed","locked":false,"assignee":null,"milestone":null,"comments":5,"created_at":"2015-11-17T05:40:30Z","updated_at":"2015-11-20T18:01:19Z","closed_at":"2015-11-20T18:01:19Z","body":"Every once in a while, a user manages to double click a submit button, causing their browser to send two identical requests, causing our web server to fire up two parallel workers to handle the request (workers A & B), which then perform this dance:\r\n\r\n* A checks for an existing record, finds none\r\n* B checks for an existing record, finds none\r\n* A makes a new record and attempts to commit\r\n* B makes a new record and attempts to commit\r\n* A's commit succeeds\r\n* B's commit fails with an IntegrityError (some unique constraint was violated)\r\n* A returns status to browser, but browser has since cancelled the request in favour of B\r\n* B returns failure, resulting in user seeing a failure message when the operation actually succeeded\r\n\r\nWhile we have changed our UI to disable submit buttons after the first submit (in hasgeek/baseframe@b03b666fd5b1052f62b583d2441d4de6a8736dff), the correct solution is to use a SAVEPOINT and roll-back just that failure. This has been documented in hasgeek/lastuser#130, but since it requires 7 lines of code, it's best abstracted into a base class. Here is the [code outline from Mike Bayer](https://groups.google.com/forum/m/#!topic/sqlalchemy/neg7nXagaMQ):\r\n\r\n```python\r\nsession.begin_nested() \r\ntry: \r\n     session.add(thing_that_may_exist_already) \r\n     session.commit()  # flushes, and commits only the \"savepoint\" \r\nexcept exc.IntegrityError: \r\n    session.rollback()     \r\n    thing_that_may_exist_already = session.query(Thing).filter_by(<criteiron>).one() \r\n```\r\n\r\nIn our case, we could have:\r\n\r\n```python\r\nclass AddAndCommitMixin(object):  # mixin class added to BaseMixin\r\n    @classmethod\r\n    def add_and_commit(cls, ob, loader, *args, **kwargs):\r\n        cls.query.session.begin_nested()\r\n        try:\r\n            cls.query.session.add(ob)\r\n            cls.query.session.commit()\r\n            return ob\r\n        except exc.IntegrityError:\r\n            cls.query.session.rollback()\r\n            return loader(*args, **kwargs)\r\n```\r\n\r\nTypical use would be like this (assuming Lastuser's UserEmail confirmation, where the confirmation happens in a GET request via their email client, and so has no protection from disabling submit buttons):\r\n\r\n```python\r\nuseremail = UserEmail(user=g.user, email=email)\r\nuseremail = UserEmail.add_and_commit(useremail, UserEmail.get, user=g.user, email=email)\r\n```"},"comment":{"url":"https://api.github.com/repos/hasgeek/coaster/issues/comments/158476342","html_url":"https://github.com/hasgeek/coaster/issues/58#issuecomment-158476342","issue_url":"https://api.github.com/repos/hasgeek/coaster/issues/58","id":158476342,"user":{"login":"jace","id":26695,"avatar_url":"https://avatars.githubusercontent.com/u/26695?v=3","gravatar_id":"","url":"https://api.github.com/users/jace","html_url":"https://github.com/jace","followers_url":"https://api.github.com/users/jace/followers","following_url":"https://api.github.com/users/jace/following{/other_user}","gists_url":"https://api.github.com/users/jace/gists{/gist_id}","starred_url":"https://api.github.com/users/jace/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/jace/subscriptions","organizations_url":"https://api.github.com/users/jace/orgs","repos_url":"https://api.github.com/users/jace/repos","events_url":"https://api.github.com/users/jace/events{/privacy}","received_events_url":"https://api.github.com/users/jace/received_events","type":"User","site_admin":false},"created_at":"2015-11-20T18:01:19Z","updated_at":"2015-11-20T18:01:19Z","body":"Fixed in #60."}}