Recover from PendingRollbackError and allow subsequent queries

Question

• We have a pyramid web application.
• We use [email protected] with Zope transactions.

In our application, it is possible for an error to occur during flush as described here which causes any subsequent usage of the SQLAlchemy session to throw a PendingRollbackError. The error which occurs during a flush is unintentional (a bug), and is raised to our exception handling view... which tries to use data from the SQLAlchemy session, which then throws a PendingRollbackError.

Is it possible to "recover" from a PendingRollbackError if you have not framed your transaction management correctly? The SQLAclhemy documentation says to avoid this situation you essentially "just need to do things the right way". Unfortunately, this is a large codebase, and developers don't always follow correct transaction management. This issue is also complicated if savepoints/nested transactions are used.

def some_view():
    # constraint violation
    session.add_all([Foo(id=1), Foo(id=1)])
    session.commit()  # Error is raised during flush
    return {'data': 'some data'}


def exception_handling_view():  # Wired in via pyramid framework, error ^ enters here.
    session.query(... does a query to get some data)  # This throws a `PendingRollbackError`

I am wondering if we can do something like the below, but don't understand pyramid + SQLAlchemy + Zope transactions well enough to know the implications (when considering the potential for nested transactions etc).

def exception_handling_view():  # Wired in via pyramid framework, error ^ enters here.
    def _query():
        session.query(... does a query to get some data)

    try:
        _query()
    except PendingRollbackError:
        session.rollback()
        _query()

Answer 1

Instead of trying to execute your query, just try to get the connection:

def exception_handling_view():
    try:
        _ = session.connection()
    except PendingRollbackError:
        session.rollback()

    session.query(...)

session.rollback() only rolls back the innermost transaction, as is usually expected — assuming nested transactions are used intentionally via the explicit session.begin_nested().

You don't have to rollback parent transactions, but if you decide to do that, you can:

while session.registry().in_transaction():
    session.rollback()