Understanding session.begin(subtransactions=True) as a context manager

Doug Farrell

unread,

Sep 28, 2017, 9:29:32 AM9/28/17

to sqlalchemy

Hi all,

I'm having trouble understanding how to use session.begin(subtransactions=True) as a context manager. I'm working in Python 2.7.5 with SQLAlchemy 1.1.14 in a Flask 0.12.2 application on CentOS 7 servers. I like session.begin() as a context manager, but things aren't working as I thought they would. For example:

with db.session.begin(subtransactions=True):

# create a model instance

thing = Thing(...)

db.session.add(thing)

I thought when the context manager went out of scope it would perform a db.session.commit() to persist the thing instance, but I haven't seen changes to the database. But if I change the code to this:

with db.session.begin(subtransactions=True):

# create a model instance

thing = Thing(...)

db.session.add(thing)

db.session.commit()

it raises a ResourceClosedError: This transaction is closed

What works for me is this:

with db.session.begin(subtransactions=True):

# create a model instance

thing = Thing(...)

db.session.add(thing)

db.session.commit()

Where the commit() is outside the scope of the context manager. But this seems contrary to me, and makes me think I'm doing something wrong as my expectation of what

with db.session.begin(...)

does is this (pseudocode):

begin transaction

try:

create some model instance

add them to the session

commit handled by leaving the scope of the context manager

except

rollback on exception

It would be very much appreciated is someone could point me in the right direction, give me some suggestions or references about what I'm missing.

Thanks,

Doug

Mike Bayer

unread,

Sep 28, 2017, 10:54:13 AM9/28/17

to sqlal...@googlegroups.com

On Thu, Sep 28, 2017 at 9:29 AM, Doug Farrell <doug.f...@gmail.com> wrote:
> Hi all,
>
> I'm having trouble understanding how to use
> session.begin(subtransactions=True) as a context manager. I'm working in
> Python 2.7.5 with SQLAlchemy 1.1.14 in a Flask 0.12.2 application on CentOS
> 7 servers. I like session.begin() as a context manager, but things aren't
> working as I thought they would. For example:
>
> with db.session.begin(subtransactions=True):
> # create a model instance
> thing = Thing(...)
> db.session.add(thing)

I would not recommend using the Session in this pattern. Leaving
the session with its default setting of "autocommit=False" means you
never have to call begin() at all; this is desirable because there's
no reason to use a Session outside of an explicit transaction (this
was not the case many years ago which is why begin() remains).
Methods that work with objects should not also be demarcating
transaction scope; there's general discussion of this at
http://docs.sqlalchemy.org/en/latest/orm/session_basics.html#when-do-i-construct-a-session-when-do-i-commit-it-and-when-do-i-close-it.

if you're doing Flask, the commit of the Session should be at the end
of the request, the begin is implicit, and that's it. If you
absolutely need a commit to occur at some special place before you've
reached the official end of the request + commit, you can just simply
call session.commit() at any time. The Session will start a new
transaction if and when additional work is performed with it.

Now, if you need your work to be in an **independent** transaction,
that is, totally separate from the "main" one, then you need to do
your work in a totally different Session object. This is an
acceptable pattern that is sometimes necessary.

>
> I thought when the context manager went out of scope it would perform a
> db.session.commit() to persist the thing instance, but I haven't seen
> changes to the database.

the commit() will happen if the begin() is the outermost begin(). If
not, then it's one of the "inner" blocks and no commit will occur.
If you haven't placed this session into autocommit=True, which is
required for making explicit use of the begin() method, then there is
already a transaction in progress and the block will never cause a
commit() to occur.

> But if I change the code to this:
>
> with db.session.begin(subtransactions=True):
> # create a model instance
> thing = Thing(...)
> db.session.add(thing)
> db.session.commit()
>
> it raises a ResourceClosedError: This transaction is closed

This is because that code interferes with the state of the context
manager. The context manager is referring to the current
SessionTransaction upon which it will call commit() at the end of the
block. Because you are calling commit() on it, that effectively ends
that SessionTransaction being in a usable state, and the context
manager then fails to do its final step.

>
> What works for me is this:
>
> with db.session.begin(subtransactions=True):
> # create a model instance
> thing = Thing(...)
> db.session.add(thing)
> db.session.commit()
>
> Where the commit() is outside the scope of the context manager. But this
> seems contrary to me, and makes me think I'm doing something wrong as my
> expectation of what

this sounds a lot like you have the session in its default mode of
autocommit=False, which is great, but you shouldn't be using the
begin() method.

If we go to the doc for begin:

http://docs.sqlalchemy.org/en/latest/orm/session_api.html?highlight=session%20begin#sqlalchemy.orm.session.Session.begin

the second line is: "The Session.begin() method is only meaningful if
this session is in autocommit mode prior to it being called; see
Autocommit Mode for background on this setting."

then if we go to autocommit mode at
http://docs.sqlalchemy.org/en/latest/orm/session_transaction.html#session-autocommit
there is a giant green/red warning box referring to the whole thing as
a legacy pattern. SQLAlchemy back in the 0.1, 0.2 days was
developed against Python 2.3/2.4, before context managers existed and
also when the Session had a more naive view of transactions and
flushing. Ideally the subtransactions concept would not have been
present at all nor would Session have a begin() method.

>
> with db.session.begin(...)
>
> does is this (pseudocode):
>
> begin transaction
> try:
> create some model instance
> add them to the session
> commit handled by leaving the scope of the context manager
> except
> rollback on exception
>
> It would be very much appreciated is someone could point me in the right
> direction, give me some suggestions or references about what I'm missing.
>
> Thanks,
> Doug
>
>
>
>
>
>
>

> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
> description.
> ---
> 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.

Doug Farrell

unread,

Sep 28, 2017, 12:50:20 PM9/28/17

to sqlalchemy

Hi Mike,

Thanks very much for the response and the clear explanation, and the link. All of that was very helpful to me, and I'm made some changes to my code accordingly. I am using Flask-SQLAlchemy by the way, so what you wrote makes even more sense in that context.

Doug

Reply all

Reply to author

Forward