Re: [sqlalchemy] How do I start a transaction without regard to whether it is nested or not?

[Mike Bayer]

On Fri, Feb 19, 2016 at 1:12 PM, Daniel Fortunov <google...@danielfortunov.com> wrote:

Context

I would like to make use of nested transactions using SQLAlchemy (in postgres), but I want to write an independent function that doesn't care if the session already has a transaction or not -- it can start a nested transaction or outer transaction appropriately.

the good news is this is how the session works already.

Question

What is the most elegant syntax to begin a transaction, which will be either an outer or nested transaction, as appropriate?

So far the best I have seen is: session.begin(nested=session.is_active)

Is there something better than this?

The best way is to not use "autocommit=True" on the Session and not call begin() at all.  That way, all functions are already present in a transaction and they never have to ensure that they start one.   Only a function that explicitly needs to do something that may fail in the middle of this transaction can choose to call begin_nested() with a very local scope.  The documentation at http://docs.sqlalchemy.org/en/rel_1_0/orm/session_transaction.html#autocommit-mode tries to make it clear that autocommit mode is not for general use and is itself sort of a legacy mode of use.

All of that said, big projects like Openstack have gone ahead anyway and used the Session with autocommit=True, and then they call begin() all over the place.  In this style of use,  the begin()/commit() sequence is designed to support nesting, however this nesting uses *subtransactions*, not savepoints.  Savepoints shouldn't be used unless you definitely need to be able to emit a ROLLBACK within the middle of a larger transaction without rolling back the whole thing, and this is not a general use case.  

To use subtransactions, pass subtransactions=True to begin().   it means that if begin() was already called, this new begin() will only demarcate an additional block whereby commit() has to be called to exit the block, then the outermost commit() actually commits the transaction.

The reason the subtransactions flag is there is only to ensure that users aren't inadvertently using nested begin/commit pairs without knowing what they're doing.    It's again not a pattern that I consider to be very useful, and in Openstack I've worked hard to propose new patterns which don't rely upon this feature. The Python DBAPI produces a connection that itself is always effectively in a transaction, so explicit use of begin() is not needed.   commit() is of course still important but the concern of when transactions are committed should IMO be separate from the business cases that proceed within that transaction.

 

Discussion

I am using postgres, which does not natively support nested transactions, so nested transactions are implemented using the SAVEPOINT command.

Specifically, what the above code does (against postgres, at least) is: * If there is no transaction in progress, start a (non-nested) transaction (BEGIN) * If there is already a transaction in progress, begin a nested transaction (SAVEPOINT)

the Python DBAPI, in this case psycopg2, already emits BEGIN for you automatically whether or not you want it to.  As long as you are emitting SQL and aren't using it's special-use "autocommit" mode, there is a transaction in progress.  

 

Is there something better than this? Ideally I'd like to just call session.begin() and have it internally work out if it needs to be an outer transaction (BEGIN) or a nested transaction (SAVEPOINT) without me having to be explicit about it.

Motivation

I would like to write a function f(session) which takes a SQLAlchemy session and makes some changes within a transaction. Also, I want the ability to rollback the changes that f() has made (and only the changes that f() has made).

then have all f() functions which need to roll back without affecting the larger transaction emit begin_nested() and commit() at the end.

 

If the calling code has begun a transaction, and made changes, then I don't want a rollback within f() to discard changes that were made by the calling code.

Thanks in advance,

Dani

--
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.
To post to this group, send email to sqlal...@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.