Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to use nested transaction with scoped session in SQLAlchemy?

I have written code bellow for handling nested transaction throughout my application. But when it rollback once after that all the transaction get rollback till I restart application.

# method_a starts a transaction and calls method_b
def method_a():
    session.begin(subtransactions=True)
    try:
        method_b()
        session.commit()  # transaction is committed here
    except:
        session.rollback() # rolls back the transaction


# method_b also starts a transaction, but when
# called from method_a participates in the ongoing
# transaction.
def method_b():
    session.begin(subtransactions=True)
    try:
        session.add(SomeObject('bat', 'lala'))
        session.commit()  # transaction is not committed yet
    except:
        session.rollback() # rolls back the transaction, in this case
                       # the one that was initiated in method_a().


# create a Session and call method_a
session = Session(autocommit=True)
global session
method_a(session)
like image 392
pravin4659 Avatar asked Nov 11 '12 10:11

pravin4659


People also ask

What is scoped Session in SQLAlchemy?

The scoped_session object by default uses this object as storage, so that a single Session is maintained for all who call upon the scoped_session registry, but only within the scope of a single thread. Callers who call upon the registry in a different thread get a Session instance that is local to that other thread.

How do I create a transaction in SQLAlchemy?

The Session. begin() method may also be used to begin the Session level transaction; calling upon Session. connection() subsequent to that call may be used to set up the per-connection-transaction isolation level: sess = Session(bind=engine) with sess.

What is _sa_instance_state in SQLAlchemy?

_sa_instance_state is a non-database-persisted value used by SQLAlchemy internally (it refers to the InstanceState for the instance. While not directly relevant to this section, if we want to get at it, we should use the inspect() function to access it).

What does Session rollback () do?

Rolling Back. Session. rollback() rolls back the current transaction, if any. When there is no transaction in place, the method passes silently.


1 Answers

unless SAVEPOINT is being used, which is not the case here, session.rollback() rolls back the entire transaction, regardless of nesting. The purpose of nesting with "subtransactions" is so that several blocks of code can each specify that they "begin()" and "commit()" a transaction, independently of whether or not one of those methods calls the other. It is only the outermost begin()/commit() pair that has any effect, so the code here is equivalent to there being no begin()/commit() call in method_b() at all.

The "subtransactions" pattern exists mostly for the purposes of framework integrations and is not intended for general use.

like image 53
zzzeek Avatar answered Sep 18 '22 18:09

zzzeek