Transactions and Connection Management

Managing Transactions

A newly constructed Session may be said to be in the “begin” state. In this state, the Session has not established any connection or transactional state with any of the Engine objects that may be associated with it.

The Session then receives requests to operate upon a database connection. Typically, this means it is called upon to execute SQL statements using a particular Engine, which may be via Session.query(), Session.execute(), or within a flush operation of pending data, which occurs when such state exists and Session.commit() or Session.flush() is called.

As these requests are received, each new Engine encountered is associated with an ongoing transactional state maintained by the Session. When the first Engine is operated upon, the Session can be said to have left the “begin” state and entered “transactional” state. For each Engine encountered, a Connection is associated with it, which is acquired via the Engine.contextual_connect() method. If a Connection was directly associated with the Session (see Joining a Session into an External Transaction (such as for test suites) for an example of this), it is added to the transactional state directly.

For each Connection, the Session also maintains a Transaction object, which is acquired by calling Connection.begin() on each Connection, or if the Session object has been established using the flag twophase=True, a TwoPhaseTransaction object acquired via Connection.begin_twophase(). These transactions are all committed or rolled back corresponding to the invocation of the Session.commit() and Session.rollback() methods. A commit operation will also call the TwoPhaseTransaction.prepare() method on all transactions if applicable.

When the transactional state is completed after a rollback or commit, the Session releases all Transaction and Connection resources, and goes back to the “begin” state, which will again invoke new Connection and Transaction objects as new requests to emit SQL statements are received.

The example below illustrates this lifecycle:

engine = create_engine("...")
Session = sessionmaker(bind=engine)

# new session.   no connections are in use.
session = Session()
try:
    # first query.  a Connection is acquired
    # from the Engine, and a Transaction
    # started.
    item1 = session.query(Item).get(1)

    # second query.  the same Connection/Transaction
    # are used.
    item2 = session.query(Item).get(2)

    # pending changes are created.
    item1.foo = 'bar'
    item2.bar = 'foo'

    # commit.  The pending changes above
    # are flushed via flush(), the Transaction
    # is committed, the Connection object closed
    # and discarded, the underlying DBAPI connection
    # returned to the connection pool.
    session.commit()
except:
    # on rollback, the same closure of state
    # as that of commit proceeds.
    session.rollback()
    raise
finally:
    # close the Session.  This will expunge any remaining
    # objects as well as reset any existing SessionTransaction
    # state.  Neither of these steps are usually essential.
    # However, if the commit() or rollback() itself experienced
    # an unanticipated internal failure (such as due to a mis-behaved
    # user-defined event handler), .close() will ensure that
    # invalid state is removed.
    session.close()

Using SAVEPOINT

SAVEPOINT transactions, if supported by the underlying engine, may be delineated using the Session.begin_nested() method:

Session = sessionmaker()
session = Session()
session.add(u1)
session.add(u2)

session.begin_nested() # establish a savepoint
session.add(u3)
session.rollback()  # rolls back u3, keeps u1 and u2

session.commit() # commits u1 and u2

Session.begin_nested() may be called any number of times, which will issue a new SAVEPOINT with a unique identifier for each call. For each Session.begin_nested() call, a corresponding Session.rollback() or Session.commit() must be issued. (But note that if the return value is used as a context manager, i.e. in a with-statement, then this rollback/commit is issued by the context manager upon exiting the context, and so should not be added explicitly.)

When Session.begin_nested() is called, a Session.flush() is unconditionally issued (regardless of the autoflush setting). This is so that when a Session.rollback() occurs, the full state of the session is expired, thus causing all subsequent attribute/instance access to reference the full state of the Session right before Session.begin_nested() was called.

Session.begin_nested(), in the same manner as the less often used Session.begin() method, returns a SessionTransaction object which works as a context manager. It can be succinctly used around individual record inserts in order to catch things like unique constraint exceptions:

for record in records:
    try:
        with session.begin_nested():
            session.merge(record)
    except:
        print("Skipped record %s" % record)
session.commit()

Autocommit Mode

Deprecated since version 1.4: “autocommit” mode is a legacy mode of use and should not be considered for new projects. The feature will be deprecated in SQLAlchemy 1.4 and removed in version 2.0; both versions provide a more refined “autobegin” approach that allows the Session.begin() method to be used normally. If autocommit mode is used, it is strongly advised that the application at least ensure that transaction scope is made present via the Session.begin() method, rather than using the session in pure autocommit mode.

The examples of session lifecycle at Managing Transactions refer to a Session that runs in its default mode of autocommit=False. In this mode, the Session begins new transactions automatically as soon as it needs to do work upon a database connection; the transaction then stays in progress until the Session.commit() or Session.rollback() methods are called.

The Session also features an older legacy mode of use called autocommit mode, where a transaction is not started implicitly, and unless the Session.begin() method is invoked, the Session will perform each database operation on a new connection checked out from the connection pool, which is then released back to the pool immediately after the operation completes. This refers to methods like Session.execute() as well as when executing a query returned by Session.query(). For a flush operation, the Session starts a new transaction for the duration of the flush, and commits it when complete.

Modern usage of “autocommit mode” tends to be for framework integrations that wish to control specifically when the “begin” state occurs. A session which is configured with autocommit=True may be placed into the “begin” state using the Session.begin() method. After the cycle completes upon Session.commit() or Session.rollback(), connection and transaction resources are released and the Session goes back into “autocommit” mode, until Session.begin() is called again:

Session = sessionmaker(bind=engine, autocommit=True)
session = Session()
session.begin()
try:
    item1 = session.query(Item).get(1)
    item2 = session.query(Item).get(2)
    item1.foo = 'bar'
    item2.bar = 'foo'
    session.commit()
except:
    session.rollback()
    raise

The Session.begin() method also returns a transactional token which is compatible with the with statement:

Session = sessionmaker(bind=engine, autocommit=True)
session = Session()
with session.begin():
    item1 = session.query(Item).get(1)
    item2 = session.query(Item).get(2)
    item1.foo = 'bar'
    item2.bar = 'foo'

Using Subtransactions with Autocommit

Deprecated since version 1.4: The Session.begin.subtransactions flag will be deprecated in SQLAlchemy 1.4 and removed in SQLAlchemy 2.0. For background on migrating away from the “subtransactions” pattern see the next section Migrating from the “subtransaction” pattern.

A subtransaction indicates usage of the Session.begin() method in conjunction with the Session.begin.subtransactions flag set to True. This produces a non-transactional, delimiting construct that allows nesting of calls to Session.begin() and Session.commit(). Its purpose is to allow the construction of code that can function within a transaction both independently of any external code that starts a transaction, as well as within a block that has already demarcated a transaction.

subtransactions=True is generally only useful in conjunction with autocommit, and is equivalent to the pattern described at Nesting of Transaction Blocks, where any number of functions can call Connection.begin() and Transaction.commit() as though they are the initiator of the transaction, but in fact may be participating in an already ongoing transaction:

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

# method_b also starts a transaction, but when
# called from method_a participates in the ongoing
# transaction.
def method_b(session):
    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().
        raise

# create a Session and call method_a
session = Session(autocommit=True)
method_a(session)
session.close()

Subtransactions are used by the Session.flush() process to ensure that the flush operation takes place within a transaction, regardless of autocommit. When autocommit is disabled, it is still useful in that it forces the Session into a “pending rollback” state, as a failed flush cannot be resumed in mid-operation, where the end user still maintains the “scope” of the transaction overall.

Migrating from the “subtransaction” pattern

The “subtransaction” pattern will be deprecated in SQLAlchemy 1.4 and removed in version 2.0 as a public API. This pattern has been shown to be confusing in real world applications, and it is preferable for an application to ensure that the top-most level of database operations are performed with a single begin/commit pair.

To provide backwards compatibility for applications that make use of this pattern, the following context manager or a similar implementation based on a decorator may be used. It relies on autocommit mode within SQLAlchemy 1.3 but not in SQLAlchemy 1.4:

import contextlib

@contextlib.contextmanager
def transaction(session):
    assert session.autocommit, (
        "this pattern expects the session to be in autocommit mode. "
        "This assertion can be removed for SQLAlchemy 1.4."
    )
    if not session.transaction:
        with session.begin():
            yield
    else:
        yield

The above context manager may be used in the same way the “subtransaction” flag works, such as in the following example:

# method_a starts a transaction and calls method_b
def method_a(session):
    with transaction(session):
        method_b(session)

# method_b also starts a transaction, but when
# called from method_a participates in the ongoing
# transaction.
def method_b(session):
    with transaction(session):
        session.add(SomeObject('bat', 'lala'))

Session = sessionmaker(engine, autocommit=True)

# create a Session and call method_a
session = Session()
try:
    method_a(session)
finally:
    session.close()

To compare towards the preferred idiomatic pattern, the begin block should be at the outermost level. This removes the need for individual functions or methods to be concerned with the details of transaction demarcation:

def method_a(session):
    method_b(session)

def method_b(session):
    session.add(SomeObject('bat', 'lala'))

Session = sessionmaker(engine)

# create a Session and call method_a
session = Session()
try:
    # Session "begins" the transaction automatically, so the
    # .transaction attribute may be used as a context manager.
    with session.transaction:
        method_a(session)
finally:
    session.close()

SQLAlchemy 1.4 will feature an improved API for the above transactional patterns.

See also

Migrating from the “nesting” pattern - similar pattern based on Core only

Enabling Two-Phase Commit

For backends which support two-phase operation (currently MySQL and PostgreSQL), the session can be instructed to use two-phase commit semantics. This will coordinate the committing of transactions across databases so that the transaction is either committed or rolled back in all databases. You can also Session.prepare() the session for interacting with transactions not managed by SQLAlchemy. To use two phase transactions set the flag twophase=True on the session:

engine1 = create_engine('postgresql://db1')
engine2 = create_engine('postgresql://db2')

Session = sessionmaker(twophase=True)

# bind User operations to engine 1, Account operations to engine 2
Session.configure(binds={User:engine1, Account:engine2})

session = Session()

# .... work with accounts and users

# commit.  session will issue a flush to all DBs, and a prepare step to all DBs,
# before committing both transactions
session.commit()

Setting Transaction Isolation Levels / DBAPI AUTOCOMMIT

Most DBAPIs support the concept of configurable transaction isolation levels. These are traditionally the four levels “READ UNCOMMITTED”, “READ COMMITTED”, “REPEATABLE READ” and “SERIALIZABLE”. These are usually applied to a DBAPI connection before it begins a new transaction, noting that most DBAPIs will begin this transaction implicitly when SQL statements are first emitted.

DBAPIs that support isolation levels also usually support the concept of true “autocommit”, which means that the DBAPI connection itself will be placed into a non-transactional autocommit mode. This usually means that the typical DBAPI behavior of emitting “BEGIN” to the database automatically no longer occurs, but it may also include other directives. When using this mode, the DBAPI does not use a transaction under any circumstances. SQLAlchemy methods like .begin(), .commit() and .rollback() pass silently.

SQLAlchemy’s dialects support settable isolation modes on a per-Engine or per-Connection basis, using flags at both the create_engine() level as well as at the Connection.execution_options() level.

When using the ORM Session, it acts as a facade for engines and connections, but does not expose transaction isolation directly. So in order to affect transaction isolation level, we need to act upon the Engine or Connection as appropriate.

Setting Isolation For A Sessionmaker / Engine Wide

To set up a Session or sessionmaker with a specific isolation level globally, the first technique is that an Engine can be constructed against a specific isolation level in all cases, which is then used as the source of connectivity for a Session and/or sessionmaker:

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

eng = create_engine(
    "postgresql://scott:tiger@localhost/test",
    isolation_level='REPEATABLE READ'
)

Session = sessionmaker(eng)

Another option, useful if there are to be two engines with different isolation levels at once, is to use the Engine.execution_options() method, which will produce a shallow copy of the original Engine which shares the same connection pool as the parent engine. This is often preferable when operations will be separated into “transactional” and “autocommit” operations:

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

eng = create_engine("postgresql://scott:tiger@localhost/test")

autocommit_engine = eng.execution_options(isolation_level="AUTOCOMMIT")

transactional_session = sessionmaker(eng)
autocommit_session = sessionmaker(autocommit_engine)

Above, both “eng” and "autocommit_engine" share the same dialect and connection pool. However the “AUTOCOMMIT” mode will be set upon connections when they are acquired from the autocommit_engine. The two sessionmaker objects “transactional_session” and “autocommit_session" then inherit these characteristics when they work with database connections.

The “autocommit_session” continues to have transactional semantics, including that Session.commit() and Session.rollback() still consider themselves to be “committing” and “rolling back” objects, however the transaction will be silently absent. For this reason, it is typical, though not strictly required, that a Session with AUTOCOMMIT isolation be used in a read-only fashion, that is:

session = autocommit_session()
some_objects = session.query(cls1).filter(...).all()
some_other_objects = session.query(cls2).filter(...).all()
session.close()  # closes connection

Setting Isolation for Individual Sessions

When we make a new Session, either using the constructor directly or when we call upon the callable produced by a sessionmaker, we can pass the bind argument directly, overriding the pre-existing bind. We can for example create our Session from the “transactional_session” and pass the “autocommit_engine”:

session = transactional_session(bind=autocommit_engine)
# work with session
session.close()

For the case where the Session or sessionmaker is configured with multiple “binds”, we can either re-specify the binds argument fully, or if we want to only replace specific binds, we can use the Session.bind_mapper() or Session.bind_table() methods:

session = maker()
session.bind_mapper(User, autocommit_engine)

We can also use the individual transaction method that follows.

Setting Isolation for Individual Transactions

A key caveat regarding isolation level is that the setting cannot be safely modified on a Connection where a transaction has already started. Databases cannot change the isolation level of a transaction in progress, and some DBAPIs and SQLAlchemy dialects have inconsistent behaviors in this area.

Therefore it is preferable to use a Session that is up front bound to an engine with the desired isolation level. However, the isolation level on a per-connection basis can be affected by using the Session.connection() method at the start of a transaction:

from sqlalchemy.orm import Session

sess = Session(bind=engine)
with sess.begin():
    sess.connection(execution_options={'isolation_level': 'SERIALIZABLE'})

# commits transaction.  the connection is released
# and reverted to its previous isolation level.

Above, we first produce a Session using either the constructor or a sessionmaker. Then we explicitly set up the start of a transaction by calling upon Session.connection(), which provides for execution options that will be passed to the connection before the transaction is begun.

Tracking Transaction State with Events

See the section Transaction Events for an overview of the available event hooks for session transaction state changes.

Joining a Session into an External Transaction (such as for test suites)

If a Connection is being used which is already in a transactional state (i.e. has a Transaction established), a Session can be made to participate within that transaction by just binding the Session to that Connection. The usual rationale for this is a test suite that allows ORM code to work freely with a Session, including the ability to call Session.commit(), where afterwards the entire database interaction is rolled back:

from sqlalchemy.orm import sessionmaker
from sqlalchemy import create_engine
from unittest import TestCase

# global application scope.  create Session class, engine
Session = sessionmaker()

engine = create_engine('postgresql://...')

class SomeTest(TestCase):
    def setUp(self):
        # connect to the database
        self.connection = engine.connect()

        # begin a non-ORM transaction
        self.trans = self.connection.begin()

        # bind an individual Session to the connection
        self.session = Session(bind=self.connection)

    def test_something(self):
        # use the session in tests.

        self.session.add(Foo())
        self.session.commit()

    def tearDown(self):
        self.session.close()

        # rollback - everything that happened with the
        # Session above (including calls to commit())
        # is rolled back.
        self.trans.rollback()

        # return connection to the Engine
        self.connection.close()

Above, we issue Session.commit() as well as Transaction.rollback(). This is an example of where we take advantage of the Connection object’s ability to maintain subtransactions, or nested begin/commit-or-rollback pairs where only the outermost begin/commit pair actually commits the transaction, or if the outermost block rolls back, everything is rolled back.