Transaction within transaction

Question

I want to know if open a transaction inside another is safe and encouraged?

I have a method:

def foo():
    session.begin
    try:
          stuffs
    except Exception, e:
         session.rollback()
         raise e
    session.commit()

and a method that calls the first one, inside a transaction:

def bar():
    stuffs
    try:
         foo()   #<<<< there it is :)
         stuffs
    except Exception, e:
        session.rollback()
        raise e
    session.commit()

if I get and exception on the foo method, all the operations will be rolled back? and everything else will work just fine? thanks!!

Answer 1

There are two ways to nest transactions in SQLAlchemy. One is virtual transactions, where SQLAlchemy keeps track of how many begin's you have issued and issues the commit only when the outermost transaction commits. The rollback however is issued immediately. Because the transaction is virtual - i.e. the database knows nothing of the nesting, you can't do anything with that session after the rollback until you rollback all the outer transactions too. To allow the use virtual transactions add subtransactions=True argument to the begin() call. This feature exists to allow you to use transaction control inside functions that might call each other without keeping track if you are inside a transaction or not. For it to make sense, configure the session with autocommit=True and always issue a session.begin(subtransactions=True) in a transactional function.

The other way to nest transactions is to use real nested transactions. They are implemented using savepoints. If you rollback a nested transaction, all changes made within that transaction are rolled back, but the outer transaction remains usable and any changes made by the outer transaction are still there. To use nested transaction issue session.begin(nested=True) or just session.begin_nested(). Nested transactions aren't supported for all databases. SQLAlchemy's test suite library configuration function sqlalchemy.test.requires.savepoints says this about the support:

    emits_warning_on('mssql', 'Savepoint support in mssql is experimental and may lead to data loss.'),
    no_support('access', 'not supported by database'),
    no_support('sqlite', 'not supported by database'),
    no_support('sybase', 'FIXME: guessing, needs confirmation'),
    exclude('mysql', '<', (5, 0, 3), 'not supported by database')

On PostgreSQL SQLAlchemy nested transactions work just fine.