This Session's transaction has been rolled back by a nested rollback() call.

[Martin Asell]

Hello. I have a error regarding subtransactions used in the flush method. given the example code i get a stack trace. The subtransaction in example code would be equivalent to the handling in sqlalchemy.orm.session.Session._flush in case of a exception.

#!/usr/bin/env python
# -*- coding: utf8 -*-

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker, scoped_session

MYSQL_DSN = 'mysql+pymysql://root:root@127.0.0.1/test?charset=utf8'
db_engine = create_engine(
    MYSQL_DSN,
    encoding='utf-8',
    pool_size=10,
    max_overflow=50,
    pool_recycle=600,
    pool_reset_on_return='rollback'
)
session = scoped_session(sessionmaker(db_engine))()

# Begin a subtransaction, like in _flush: https://bitbucket.org/zzzeek/sqlalchemy/src/c3d582d87b5bd404d380c5707f35b7a20c9828fc/lib/sqlalchemy/orm/session.py?at=master&fileviewer=file-view-default#session.py-2142
subtrans = session.begin(subtransactions=True)
# Rollback the transaction, like in _flush in case of exception. https://bitbucket.org/zzzeek/sqlalchemy/src/c3d582d87b5bd404d380c5707f35b7a20c9828fc/lib/sqlalchemy/orm/session.py?at=master&fileviewer=file-view-default#session.py-2183
subtrans.rollback()

# session is unusable unless i do another rollback call manually.
with session.begin_nested():
    pass

Traceback (most recent call last):
  File "trans.py", line 24, in <module>
    with session.begin_nested():
  File "/vagrant/sqltest/lib/python3.4/site-packages/sqlalchemy/orm/session.py", line 745, in begin_nested
    return self.begin(nested=True)
  File "/vagrant/sqltest/lib/python3.4/site-packages/sqlalchemy/orm/session.py", line 725, in begin
    nested=nested)
  File "/vagrant/sqltest/lib/python3.4/site-packages/sqlalchemy/orm/session.py", line 235, in _begin
    self._assert_active()
  File "/vagrant/sqltest/lib/python3.4/site-packages/sqlalchemy/orm/session.py", line 218, in _assert_active
    "This Session's transaction has been rolled back "
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.

[Mike Bayer]

this is the definition of what the subtransaction is for - a semantic "transaction" that is nonetheless linked to the outermost transaction which here, you've rolled back at the DB level but not semantically. See http://docs.sqlalchemy.org/en/latest/faq/sessions.html#this-session-s-transaction-has-been-rolled-back-due-to-a-previous-exception-during-flush-or-similar.

subtranscation=True is also a flag and not automatic because subtransactions are very confusing/misleading and not very useful to the outside world. if you want your Session to recover within the transaction after a flush() throws an exception, put the flush itself inside of a savepoint. the example here shows a Session that has not been fully rolled back.