Pylons + SQLAlchemy problem: The transaction is inactive due to a rollback in a subtransaction

Question

I have a problem with Pylons + SQLAlchemy.

When something goes wrong (in my case it is integrity error, due to a race condition) and the database error is raised, all following requestы result in the error being raised:

InvalidRequestError: The transaction is inactive due to a rollback in a subtransaction.  Issue rollback() to cancel the transaction.

While I can prevent this original error (race condition) from happening, but I would like a more robust solution, I want to prevent a single error from crashing the entire application.

What is the best way to do this? Is there a way to tell Pylons to rollback the failed transaction?

Answer 1

Do you use in your controllers yoursapp.lib.base.BaseController?

You can look at http://stackoverflow.com/questions/3033234/handle-mysql-restart-in-sqlalchemy/3035882#3035882

Also you can catch SA exception in BaseController try-finally block and do session rollback()

In BaseController SA Session removed http://www.sqlalchemy.org/docs/05/session.html#lifespan-of-a-contextual-session

Answer 2

The easiest thing is to make sure you are using a new SQLAlchemy Session when you start work in your controller. in /project/lib/base.py, add a method for BaseController:

def __before__(self):
    model.Session.close()

Session.close() will clear out the session and close any open transactions if there are any. You want to make sure that each time you use a session it's cleared when you're done with your work in the controller. Doing it at the start of the controller's handling of the request will make sure that it's always cleared, even if the thread's previous request had an exception and there is a rollback waiting.