CODEWITHSUNDEEP
pythonsqlalchemy
fbessho
fbessho

Reputation: 1532

Why do I get SQLAlchemy nested rollback error?

I got an error as follows in my code of python (which collects twitter statuses and store in database).

sqlalchemy.exc.InvalidRequestError: This Session's transaction has been rolled back by a nested rollback() call.  To begin a new transaction, issue Session.rollback() first.

I want to know what is the problem, why does it occur, and how can I solve it.

I have no idea about nested rollback. Is there any simple example which occurs nested rollback?

Upvotes: 56

Views: 58927

Answers (2)

DaleD
DaleD

Reputation: 111

As identified by @fbessho above, this is indeed the correct pattern:

try:
    <use session>
    session.commit()
except:
    session.rollback()

However, there are some subtleties that can derail the error handling.

In this example (an imaginary unique constraint violation), the rollback does not occur:

class Thing1(Base):
   id = Column(BigInteger, primary_key=True)


class Thing2(Base):
   id = Column(BigInteger, primary_key=True)


def do_something(s: session, thing_1: Thing1, duplicate_id):

   # imagine this violates a unique constraint on Thing2
   thing_2 = Thing2(id=duplicate_id)
   s.add(thing_2)

   try:
       # the exception will occur when the commit statement is executed
       s.commit()
   except Exception as ex:
       # this will log details of the exception
       logger.error(f"{ex.__class__.__name__}: {ex}")
       # referencing thing_1.id will raise a second exception
       logger.error(f"Commit failed.  Thing1 id was {thing_1.id}.")
       s.rollback()

This second Exception occurs even though thing_1 has nothing to do with the failed insert. Merely referencing thing_1 raises a second Exception which prevents the rollback from being executed.

Solution 1

This requires a little more overhead, but will always work.

def do_something_1(s: session, thing_1: Thing1, duplicate_id):

   # create a reference that does not rely on the data object
   id_for_thing = thing_1.id

   # imagine this violates a unique constraint on Thing2
   thing_2 = Thing2(id=duplicate_id)
   s.add(thing_2)

   try:
       # the exception will occur when the commit statement is executed
       s.commit()
   except Exception as ex:
       logger.error(f"{ex.__class__.__name__}: {ex}")
       # no direct reference to thing_1
       logger.error(f"Commit failed.  Thing1 id was {id_for_thing}.")
       s.rollback()

Solution 2

This will work as long as thing_1 is not affected by the rollback.

def do_something_2(s: session, thing_1: Thing1, duplicate_id):

   # imagine this violates a unique constraint on Thing2
   thing_2 = Thing2(id=duplicate_id)
   s.add(thing_2)

   try:
       # the exception will occur when the commit statement is executed
       s.commit()
   except Exception as ex:
       logger.error(f"{ex.__class__.__name__}: {ex}")
       s.rollback()
       # thing_1.id can be referenced after rollback
       logger.error(f"Commit failed.  Thing1 id was {thing_1.id}.")

Upvotes: 3

fbessho
fbessho

Reputation: 1532

The problem was solved. The point, in this case, is that rollback is not executed until we call rollback explicitly, so when we include commit(), we should write it in a try statement, and write rollback() in the exception statement (in most case) as written in https://docs.sqlalchemy.org/en/13/faq/sessions.html#this-session-s-transaction-has-been-rolled-back-due-to-a-previous-exception-during-flush-or-similar

And, here is the correct code example. I quoted this from the link above.

try:
    <use session>
    session.commit()
except:
    session.rollback()
    raise
finally:
    session.close()  # optional, depends on use case

Upvotes: 42

Related Questions