Transaction within transaction

Question

Hello, 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

You can't, PostgreSQL doesn't support subtransactions. You might want to use savepoints, but thats something else.

Answer 2

there are two ways two 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 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.

Answer 3

On PostgreSQL nested transactions work just fine.

Well, you're not going to get an error (just a warning), that's true. But you can't commit the inner transaction and rollback the outer transaction, the outer transaction will also rollback the inner transaction.

BEGIN;

INSERT INTO x(foo) VALUES('John');

BEGIN; -- WARNING!

INSERT INTO y(bar) VALUES('Jane');

COMMIT; -- commit inner transaction

ROLLBACK; -- will rollback both inserts, not just the first, the one in table "x"

To my knowledge, Oracle is one of the few that has this option.