Skip to content
Tutorials Center
My learning

Topics

Lesson 26 of 37 ยท Sessions and Transactions

Transactions, Savepoints, Isolation and Locking

Use session.begin(), commit and rollback correctly, nest work with savepoints, handle IntegrityError, pick isolation levels and prevent lost updates with version counters.

A transaction groups statements so they succeed or fail together. Transferring money is the classic example: the debit and the credit must both happen or neither. The session starts a transaction automatically the first time it talks to the database, and it stays open until you commit, roll back or close.

Commit, rollback and the context managers

There are two tidy ways to scope a transaction. Both guarantee a rollback if an exception escapes.

Python session.begin() commits or rolls back for you

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

with Session(engine) as s, s.begin(): reads as "open a session, run a transaction in it". When the block ends normally the transaction commits; on an exception it rolls back and the exception propagates. The second transfer changed Ana's balance in memory, but the rollback discarded it.

For short helpers you can also use sessionmaker(engine).begin(), which gives a session that commits at the end of the block:

Python
SessionLocal = sessionmaker(engine)
with SessionLocal.begin() as session:
    session.add(Account(owner="Cy", balance=0))

After an error you must roll back

If a flush raises (for example an IntegrityError), the session's transaction is now in a failed state and refuses more work until you call session.rollback(). Code that catches the exception and keeps going without a rollback gets PendingRollbackError. The with s.begin() form does the rollback for you.

Savepoints with begin_nested()

A savepoint is a mark inside a transaction you can roll back to without losing the earlier work. session.begin_nested() creates one. It is the right tool for "try this insert, and if it conflicts, carry on".

SQLite's Python driver needs a small recipe to make savepoints behave. The listeners below are the officially documented fix; on PostgreSQL and MySQL you do not need them.

Python Skip duplicates with begin_nested()

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Each failed insert rolls back only to its own savepoint, so the other tags stay in the transaction.

sequenceDiagram
    participant App
    participant DB
    App->>DB: BEGIN
    App->>DB: SAVEPOINT s1
    App->>DB: INSERT python
    App->>DB: RELEASE s1
    App->>DB: SAVEPOINT s2
    App->>DB: INSERT python (duplicate!)
    DB-->>App: IntegrityError
    App->>DB: ROLLBACK TO s2
    App->>DB: COMMIT

A failure rolls back to its savepoint only. The outer transaction continues and the commit keeps the good inserts.

Isolation levels

The isolation level controls how much one transaction can see of another's uncommitted or recent work: READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE. Higher levels prevent more anomalies and cost more concurrency. SQLAlchemy sets it per engine or per connection:

Python
engine = create_engine(url, isolation_level="REPEATABLE READ")

# or for a single connection / session
with engine.connect().execution_options(isolation_level="SERIALIZABLE") as conn:
    ...
session = Session(engine.execution_options(isolation_level="READ COMMITTED"))

SQLite supports only SERIALIZABLE (the default) and READ UNCOMMITTED. PostgreSQL defaults to READ COMMITTED, MySQL's InnoDB to REPEATABLE READ. You also have AUTOCOMMIT, a driver-level mode where each statement commits instantly, useful for statements such as CREATE DATABASE.

Lost updates and how to prevent them

Two users load the same row, both change it, and the second save silently overwrites the first. There are two defenses.

Pessimistic locking locks the row while you work: select(Account).where(...).with_for_update() emits SELECT ... FOR UPDATE on PostgreSQL and MySQL (SQLite ignores it; it locks the whole file). It blocks others, so keep the transaction short.

Optimistic locking lets everyone read freely and detects the conflict on write. Add a version column with version_id_col; SQLAlchemy increments it on every UPDATE and includes it in the WHERE. If someone else got there first, zero rows match and a StaleDataError is raised.

Python Optimistic locking with version_id_col

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The usual response to StaleDataError is to reload the object, show the user the newer data, and let them redo or merge their change. Optimistic locking works well for web apps where edits take minutes and conflicts are rare.

Practical guidance

  • Keep transactions short and never hold one open while waiting for a user or a network call.
  • One session per unit of work (a request, a job). Never share a session between threads.
  • Catch database errors outside the with s.begin() block so the rollback has already happened.
  • Use constraints (UNIQUE, CHECK, foreign keys) as the final safety net; they work under any isolation level.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What does with Session(engine) as s, s.begin(): do at the end of the block?

    session.begin() as a context manager commits when the block succeeds and rolls back on an exception.
  2. 2. When do you use begin_nested()?

    begin_nested() emits SAVEPOINT; a rollback inside returns to it while the outer transaction continues.
  3. 3. A flush raised IntegrityError and you keep using the session without rolling back. What happens?

    After a failed flush the transaction is inactive until rollback() is called.
  4. 4. How does version_id_col detect a lost update?

    The version counter is checked and incremented in the UPDATE itself, so a concurrent change makes the update match no row.

Summary

  • Use with Session(engine) as s, s.begin(): to commit on success and roll back on error.
  • After a failed flush call rollback() before reusing the session.
  • begin_nested() creates savepoints; SQLite needs the documented connect/begin event recipe.
  • Set isolation per engine, connection or session; SQLite supports only SERIALIZABLE and READ UNCOMMITTED.
  • Prevent lost updates with with_for_update() (pessimistic) or version_id_col (optimistic).

Next: Session Patterns and Events