> On Nov 28, 2014, at 9:28 AM, Guido Winkelmann > <gu...@ambient-entertainment.de> wrote: > > Hi, > > What's the best strategy for retrying failed transactions when using > SQLAlchemy? > > Sometimes, transactions fail on commit, through no fault of their own, simply > because another concurrent but conflicting transaction finished slightly > before them. The commonly accepted best practice seems to be that you should > just retry your transaction – including all client side logic – in such cases > until it succeeds (or until you run into some timeout). >
This is the best practice. To encapsulate the full series of operations you’re doing in the transaction into a Python function, which you then invoke under a so-called “retry” decorator. This decorator runs the function, catches the SQL errors that you deem as appropriate for a “retry”, then it invokes the whole function again. It typically does this for up to a certain number of times before giving up completely and re-raising the exception. > The problem is, in SQLAlchemy, at least with a PostgreSQL+psycopg2 backend, > the exception I get when that happens is "InvalidRequestError". This is not > very helpful, as I cannot tell the difference between a genuinely invalid > request and a mere transaction failure like this. I also don't know if the > exception will be different on different backends. then that means you’re doing something incorrectly. The actual message of this exception as well as a stack trace and concise and self-contained examples of code would allow us to have some idea what this might be. InvalidRequestError is also a SQLAlchemy-generated exception, not a DBAPI error, so will be identical on all backends. > After some experimentation and research, it turned out that the root cause > here was that PostgreSQL's default transaction isolation level of "read > committed" was just not enough in my case, and I would need to increase it to > "serializable”. that is also suspect. serializable isolation is very tough to use due to the high degree of locking and it suggests there is some level of concurrency here that is probably better accommodated using either a correctly implemented optimistic approach (e.g. the retry) or if UPDATES are part of the issue, SELECT..FOR UPDATE can often help to lock rows ahead of time when updates will be needed. > Doing that, however, led to a rude awakening for me regarding handling of > failed transactions. They suddenly turned from something mostly theoretical, > that I know about but could safely ignore in practice almost all of the time, > into a very real thing that happens a lot in practice and that I need to deal > with somehow, because otherwise my application pretty much ceases to work for > all practical purposes. feel free to share more specifics as long as they are concise and self-contained. -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
