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). 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. The background here is that, lately, I have observed the application I am working on (https://github.com/pyfarm) sometimes doing the wrong thing when concurrent requests are involved. (Example: A job has zero or more tasks. When a request comes in to update a task's state to "done" or "failed", I need to update the task and then check whether the job it belongs to still has any tasks left that are not "failed" or "done", and do x if it doesn't. I have lately spotted some jobs that have no more active tasks left, but still x was never done for them.) 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". 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. Regards, Guido W. -- 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.
