Transactions and Connection Management

Managing Transactions

Changed in version 1.4: Session transaction management has been revised to be clearer and easier to use. In particular, it now features “autobegin” operation, which means the point at which a transaction begins may be controlled, without using the legacy “autocommit” mode.

The Session tracks the state of a single “virtual” transaction at a time, using an object called SessionTransaction. This object then makes use of the underyling Engine or engines to which the Session object is bound in order to start real connection-level transactions using the Connection object as needed.

This “virtual” transaction is created automatically when needed, or can alternatively be started using the Session.begin() method. To as great a degree as possible, Python context manager use is supported both at the level of creating Session objects as well as to maintain the scope of the SessionTransaction.

Below, assume we start with a Session:

  1. from sqlalchemy.orm import Session
  2. session = Session(engine)

We can now run operations within a demarcated transaction using a context manager:

  1. with session.begin():
  2.     session.add(some_object())
  3.     session.add(some_other_object())
  4. # commits transaction at the end, or rolls back if there
  5. # was an exception raised

At the end of the above context, assuming no exceptions were raised, any pending objects will be flushed to the database and the database transaction will be committed. If an exception was raised within the above block, then the transaction would be rolled back. In both cases, the above Session subsequent to exiting the block is ready to be used in subsequent transactions.

The Session.begin() method is optional, and the Session may also be used in a commit-as-you-go approach, where it will begin transactions automatically as needed; these only need be committed or rolled back:

  1. session = Session(engine)
  3. session.add(some_object())
  4. session.add(some_other_object())
  6. session.commit()  # commits
  8. # will automatically begin again
  9. result = session.execute(< some select statment >)
  10. session.add_all([more_objects, ...])
  11. session.commit()  # commits
  13. session.add(still_another_object)
  14. session.flush()  # flush still_another_object
  15. session.rollback()   # rolls back still_another_object

The Session itself features a Session.close() method. If the Session is begun within a transaction that has not yet been committed or rolled back, this method will cancel (i.e. rollback) that transaction, and also expunge all objects contained within the Session object’s state. If the Session is being used in such a way that a call to Session.commit() or Session.rollback() is not guaranteed (e.g. not within a context manager or similar), the close method may be used to ensure all resources are released:

  1. # expunges all objects, releases all transactions unconditionally
  2. # (with rollback), releases all database connections back to their
  3. # engines
  4. session.close()

Finally, the session construction / close process can itself be run via context manager. This is the best way to ensure that the scope of a Session object’s use is scoped within a fixed block. Illustrated via the Session constructor first:

  1. with Session(engine) as session:
  2.     session.add(some_object())
  3.     session.add(some_other_object())
  5.     session.commit()  # commits
  7.     session.add(still_another_object)
  8.     session.flush()  # flush still_another_object
  10.     session.commit()  # commits
  12.     result = session.execute(<some SELECT statement>)
  14. # remaining transactional state from the .execute() call is
  15. # discarded

Similarly, the sessionmaker can be used in the same way:

  1. Session = sesssionmaker(engine)
  3. with Session() as session:
  4.     with session.begin():
  5.         session.add(some_object)
  6.     # commits
  8. # closes the Session

sessionmaker itself includes a sessionmaker.begin() method to allow both operations to take place at once:

  1. with Session.begin() as session:
  2.     session.add(some_object):

Using SAVEPOINT

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

  1. Session = sessionmaker()
  3. with Session.begin() as session:
  4.     session.add(u1)
  5.     session.add(u2)
  7.     nested = session.begin_nested() # establish a savepoint
  8.     session.add(u3)
  9.     nested.rollback()  # rolls back u3, keeps u1 and u2
  11. # commits u1 and u2

Each time Session.begin_nested() is called, a new “BEGIN SAVEPOINT” command is emitted to the database wih a unique identifier. When SessionTransaction.commit() is called, “RELEASE SAVEPOINT” is emitted on the database, and if instead SessionTransaction.rollback() is called, “ROLLBACK TO SAVEPOINT” is emitted.

Session.begin_nested() may also be used as a context manager in the same manner as that of the Session.begin() method:

  1. for record in records:
  2.     try:
  3.         with session.begin_nested():
  4.             session.merge(record)
  5.     except:
  6.         print("Skipped record %s" % record)
  7. session.commit()

When Session.begin_nested() is called, a Session.flush() is unconditionally issued (regardless of the autoflush setting). This is so that when a rollback on this nested transaction 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.

See also

NestedTransaction - the NestedTransaction class is the Core-level construct that is used by the Session internally to produce SAVEPOINT blocks.

Session-level vs. Engine level transaction control

As of SQLAlchemy 1.4, the sessionmaker and Core Engine objects both support 2.0 style operation, by making use of the Session.future flag as well as the create_engine.future flag so that these two objects assume 2.0-style semantics.

When using future mode, there should be equivalent semantics between the two packages, at the level of the sessionmaker vs. the Engine, as well as the Session vs. the Connection. The following sections detail these scenarios based on the following scheme:

  1. ORM (using future Session)                    Core (using future engine)
  2. -----------------------------------------     -----------------------------------
  3. sessionmaker                                  Engine
  4. Session                                       Connection
  5. sessionmaker.begin()                          Engine.begin()
  6. some_session.commit()                         some_connection.commit()
  7. with some_sessionmaker() as session:          with some_engine.connect() as conn:
  8. with some_sessionmaker.begin() as session:    with some_engine.begin() as conn:
  9. with some_session.begin_nested() as sp:       with some_connection.begin_nested() as sp:

Commit as you go

Both Session and Connection feature Connection.commit() and Connection.rollback() methods. Using SQLAlchemy 2.0-style operation, these methods affect the outermost transaction in all cases.

Engine:

  1. engine = create_engine("postgresql://user:pass@host/dbname", future=True)
  3. with engine.connect() as conn:
  4.     conn.execute(
  5.         some_table.insert(),
  6.         [
  7.             {"data": "some data one"},
  8.             {"data": "some data two"},
  9.             {"data": "some data three"}
  10.         ]
  11.     )
  12.     conn.commit()

Session:

  1. Session = sessionmaker(engine, future=True)
  3. with Session() as session:
  4.     session.add_all([
  5.         SomeClass(data="some data one"),
  6.         SomeClass(data="some data two"),
  7.         SomeClass(data="some data three")
  8.     ])
  9.     session.commit()

Begin Once

Both sessionmaker and Engine feature a Engine.begin() method that will both procure a new object with which to execute SQL statements (the Session and Connection, respectively) and then return a context manager that will maintain a begin/commit/rollback context for that object.

Engine:

  1. engine = create_engine("postgresql://user:pass@host/dbname", future=True)
  3. with engine.begin() as conn:
  4.     conn.execute(
  5.         some_table.insert(),
  6.         [
  7.             {"data": "some data one"},
  8.             {"data": "some data two"},
  9.             {"data": "some data three"}
  10.         ]
  11.     )
  12. # commits and closes automatically

Session:

  1. Session = sessionmaker(engine, future=True)
  3. with Session.begin() as session:
  4.     session.add_all([
  5.         SomeClass(data="some data one"),
  6.         SomeClass(data="some data two"),
  7.         SomeClass(data="some data three")
  8.     ])
  9. # commits and closes automatically

Nested Transaction

When using a SAVEPOINT via the Session.begin_nested() or Connection.begin_nested() methods, the transaction object returned must be used to commit or rollback the SAVEPOINT. Calling the Session.commit() or Connection.commit() methods will always commit the outermost transaction; this is a SQLAlchemy 2.0 specific behavior that is reversed from the 1.x series.

Engine:

  1. engine = create_engine("postgresql://user:pass@host/dbname", future=True)
  3. with engine.begin() as conn:
  4.     savepoint = conn.begin_nested()
  5.     conn.execute(
  6.         some_table.insert(),
  7.         [
  8.             {"data": "some data one"},
  9.             {"data": "some data two"},
  10.             {"data": "some data three"}
  11.         ]
  12.     )
  13.     savepoint.commit()  # or rollback
  15. # commits automatically

Session:

  1. Session = sessionmaker(engine, future=True)
  3. with Session.begin() as session:
  4.     savepoint = session.begin_nested()
  5.     session.add_all([
  6.         SomeClass(data="some data one"),
  7.         SomeClass(data="some data two"),
  8.         SomeClass(data="some data three")
  9.     ])
  10.     savepoint.commit()  # or rollback
  11. # commits automatically

Explicit Begin

Changed in version 1.4: SQLAlchemy 1.4 deprecates “autocommit mode”, which is historically enabled by using the Session.autocommit flag. Going forward, a new approach to allowing usage of the Session.begin() method is new “autobegin” behavior so that the method may now be called when a Session is first constructed, or after the previous transaction has ended and before it begins a new one.

For background on migrating away from the “subtransaction” pattern for frameworks that rely upon nesting of begin()/commit() pairs, see the next section Migrating from the “subtransaction” pattern.

The Session features “autobegin” behavior, meaning that as soon as operations begin to take place, it ensures a SessionTransaction is present to track ongoing operations. This transaction is completed when Session.commit() is called.

It is often desirable, particularly in framework integrations, to control the point at which the “begin” operation occurs. To suit this, the Session uses an “autobegin” strategy, such that the Session.begin() method may be called directly for a Session that has not already had a transaction begun:

  1. Session = sessionmaker(bind=engine)
  2. session = Session()
  3. session.begin()
  4. try:
  5.     item1 = session.query(Item).get(1)
  6.     item2 = session.query(Item).get(2)
  7.     item1.foo = 'bar'
  8.     item2.bar = 'foo'
  9.     session.commit()
  10. except:
  11.     session.rollback()
  12.     raise

The above pattern is more idiomatically invoked using a context manager:

  1. Session = sessionmaker(bind=engine)
  2. session = Session()
  3. with session.begin():
  4.     item1 = session.query(Item).get(1)
  5.     item2 = session.query(Item).get(2)
  6.     item1.foo = 'bar'
  7.     item2.bar = 'foo'

The Session.begin() method and the session’s “autobegin” process use the same sequence of steps to begin the transaction. This includes that the SessionEvents.after_transaction_create() event is invoked when it occurs; this hook is used by frameworks in order to integrate their own transactional processes with that of the ORM Session.

Migrating from the “subtransaction” pattern

Deprecated since version 1.4: The Session.begin.subtransactions flag is deprecated. While the Session still uses the “subtransactions” pattern internally, it is not suitable for end-user use as it leads to confusion, and additionally it may be removed from the Session itself in version 2.0 once “autocommit” mode is removed.

The “subtransaction” pattern that was often used with autocommit mode is also deprecated in 1.4. This pattern allowed the use of the Session.begin() method when a transaction were already begun, resulting in a construct called a “subtransaction”, which was essentially a block that would prevent the Session.commit() method from actually committing.

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:

  1. import contextlib
  3. @contextlib.contextmanager
  4. def transaction(session):
  5.     if not session.in_transaction():
  6.         with session.begin():
  7.             yield
  8.     else:
  9.         yield

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

  1. # method_a starts a transaction and calls method_b
  2. def method_a(session):
  3.     with transaction(session):
  4.         method_b(session)
  6. # method_b also starts a transaction, but when
  7. # called from method_a participates in the ongoing
  8. # transaction.
  9. def method_b(session):
  10.     with transaction(session):
  11.         session.add(SomeObject('bat', 'lala'))
  13. Session = sessionmaker(engine)
  15. # create a Session and call method_a
  16. with Session() as session:
  17.     method_a(session)

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:

  1. def method_a(session):
  2.     method_b(session)
  4. def method_b(session):
  5.     session.add(SomeObject('bat', 'lala'))
  7. Session = sessionmaker(engine)
  9. # create a Session and call method_a
  10. with Session() as session:
  11.     with session.begin():
  12.         method_a(session)

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:

  1. engine1 = create_engine('postgresql://db1')
  2. engine2 = create_engine('postgresql://db2')
  4. Session = sessionmaker(twophase=True)
  6. # bind User operations to engine 1, Account operations to engine 2
  7. Session.configure(binds={User:engine1, Account:engine2})
  9. session = Session()
  11. # .... work with accounts and users
  13. # commit.  session will issue a flush to all DBs, and a prepare step to all DBs,
  14. # before committing both transactions
  15. 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:

  1. from sqlalchemy import create_engine
  2. from sqlalchemy.orm import sessionmaker
  4. eng = create_engine(
  5.     "postgresql://scott:tiger@localhost/test",
  6.     isolation_level='REPEATABLE READ'
  7. )
  9. 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:

  1. from sqlalchemy import create_engine
  2. from sqlalchemy.orm import sessionmaker
  4. eng = create_engine("postgresql://scott:tiger@localhost/test")
  6. autocommit_engine = eng.execution_options(isolation_level="AUTOCOMMIT")
  8. transactional_session = sessionmaker(eng)
  9. 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:

  1. with autocommit_session() as session:
  2.     some_objects = session.execute(<statement>)
  3.     some_other_objects = session.execute(<statement>)
  5. # 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”:

  1. with transactional_session(bind=autocommit_engine) as session:
  2.     # work with session

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:

  1. session = maker()
  2. 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:

  1. from sqlalchemy.orm import Session
  3. sess = Session(bind=engine)
  4. with sess.begin():
  5.     sess.connection(execution_options={'isolation_level': 'SERIALIZABLE'})
  7. # commits transaction.  the connection is released
  8. # 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.

Changed in version 1.4: This section introduces a new version of the “join into an external transaction” recipe that will work equally well for both 2.0 style and 1.x style engines and sessions. The recipe here from previous versions such as 1.3 will also continue to work for 1.x engines and sessions.

The recipe works by establishing a Connection within a transaction and optionally a SAVEPOINT, then passing it to a Session as the “bind”. The Session detects that the given Connection is already in a transaction and will not run COMMIT on it if the transaction is in fact an outermost transaction. Then when the test tears down, the transaction is rolled back so that any data changes throughout the test are reverted:

  1. from sqlalchemy.orm import sessionmaker
  2. from sqlalchemy import create_engine
  3. from unittest import TestCase
  5. # global application scope.  create Session class, engine
  6. Session = sessionmaker()
  8. engine = create_engine('postgresql://...')
  10. class SomeTest(TestCase):
  11.     def setUp(self):
  12.         # connect to the database
  13.         self.connection = engine.connect()
  15.         # begin a non-ORM transaction
  16.         self.trans = self.connection.begin()
  19.         # bind an individual Session to the connection
  20.         self.session = Session(bind=self.connection)
  23.         ###    optional     ###
  25.         # if the database supports SAVEPOINT (SQLite needs special
  26.         # config for this to work), starting a savepoint
  27.         # will allow tests to also use rollback within tests
  29.         self.nested = self.connection.begin_nested()
  31.         @event.listens_for(self.session, "after_transaction_end")
  32.         def end_savepoint(session, transaction):
  33.             if not self.nested.is_active:
  34.                 self.nested = self.connection.begin_nested()
  36.         ### ^^^ optional ^^^ ###
  38.     def test_something(self):
  39.         # use the session in tests.
  41.         self.session.add(Foo())
  42.         self.session.commit()
  44.     def test_something_with_rollbacks(self):
  45.         # if the SAVEPOINT steps are taken, then a test can also
  46.         # use session.rollback() and continue working with the database
  48.         self.session.add(Bar())
  49.         self.session.flush()
  50.         self.session.rollback()
  52.         self.session.add(Foo())
  53.         self.session.commit()
  55.     def tearDown(self):
  56.         self.session.close()
  58.         # rollback - everything that happened with the
  59.         # Session above (including calls to commit())
  60.         # is rolled back.
  61.         self.trans.rollback()
  63.         # return connection to the Engine
  64.         self.connection.close()

The above recipe is part of SQLAlchemy’s own CI to ensure that it remains working as expected.