Transactions, Savepoints, Isolation

6 interview angles 6 min read source

Transactions, Savepoints, Isolation

SQLAlchemy’s transaction handling sits on top of the DB’s. The session abstracts most of it; you should know the underlying mapping for interview questions and for debugging.

For pure DB-side transaction theory (isolation levels, ACID), see ../08_transactions_isolation.md. This file is the SQLAlchemy-specific side.

The default model

with Session(engine) as session:
    session.add(user)
    session.commit()

Maps roughly to:

BEGIN;
INSERT INTO users ...;
COMMIT;

The session autobegins on first statement, autoflushes before queries, and COMMITs on session.commit().

Explicit begin

with Session(engine) as session:
    with session.begin():
        session.add(user)
    # commits at end of inner block (or rolls back on exception)

The inner context manager makes the commit/rollback explicit and removes the need for try/except around the commit. This is the recommended pattern.

For Core-style:

with engine.begin() as conn:
    conn.execute(insert(users).values(name="alice"))
# auto-commits on success, rolls back on exception

Savepoints — `begin_nested()`

A savepoint is a “subtransaction” that can be rolled back without aborting the outer transaction.

with session.begin():
    session.add(user1)

    try:
        with session.begin_nested():       # SAVEPOINT sp1
            session.add(user2)
            do_risky_thing()                # might raise
        # RELEASE SAVEPOINT sp1
    except Exception:
        pass
    # if it raised: ROLLBACK TO SAVEPOINT sp1
    # user1 is still in the outer transaction

    session.add(user3)
# outer COMMIT (user1 + user3)

SQL produced:

BEGIN;
INSERT user1;
SAVEPOINT sp1;
INSERT user2;
-- on exception: ROLLBACK TO SAVEPOINT sp1
-- on success: RELEASE SAVEPOINT sp1
INSERT user3;
COMMIT;

Use cases:

Try an optional operation; on failure continue.
Per-row error handling in a batch.
Tests that need rollback to a known state without losing test setup.

Tests + transactional rollback

@pytest.fixture
def db_session(engine):
    connection = engine.connect()
    trans = connection.begin()
    session = Session(bind=connection)

    yield session

    session.close()
    trans.rollback()        # discard everything done in the test
    connection.close()

Pattern:

Open a connection + outer transaction.
Run the test inside; commits in the test become savepoints (SQLAlchemy uses begin_nested for the session inside).
Rollback the outer transaction at teardown.

For modern SQLAlchemy 2.0 with savepoints, the more robust pattern wraps the session in a SAVEPOINT-restarting event listener (the “join_transaction_mode=‘create_savepoint’” pattern). See 13_testing_patterns.md.

Isolation level

# Engine-level (applies to all new connections)
engine = create_engine(
    "postgresql+psycopg://...",
    isolation_level="REPEATABLE READ",
)

# Per-connection
with engine.connect().execution_options(isolation_level="SERIALIZABLE") as conn:
    ...

# Per-session
with Session(engine, info={...}) as session:
    session.connection(execution_options={"isolation_level": "SERIALIZABLE"})
    ...

Levels (DB-dependent):

Level	Phenomena prevented
READ UNCOMMITTED	none (dirty reads possible)
READ COMMITTED	dirty reads
REPEATABLE READ	dirty reads, non-repeatable reads
SERIALIZABLE	dirty reads, non-repeatable reads, phantom reads

Defaults:

Postgres: READ COMMITTED
MySQL/InnoDB: REPEATABLE READ
SQL Server: READ COMMITTED
Oracle: READ COMMITTED

For SERIALIZABLE workloads, be prepared for serialization failures (Postgres 40001) — wrap operations in retry logic.

Optimistic locking with version counters

class Document(Base):
    __tablename__ = "documents"
    id: Mapped[int] = mapped_column(primary_key=True)
    title: Mapped[str]
    version_id: Mapped[int] = mapped_column(default=1)

    __mapper_args__ = {
        "version_id_col": version_id,
    }

On UPDATE, SQLAlchemy issues:

UPDATE documents
SET title = ..., version_id = version_id + 1
WHERE id = ? AND version_id = ?     -- the version you loaded

If another transaction updated the row between your load and your commit, the WHERE matches 0 rows and SQLAlchemy raises StaleDataError. Handle by retrying:

for attempt in range(3):
    try:
        doc = session.get(Document, doc_id)
        doc.title = "new"
        session.commit()
        break
    except StaleDataError:
        session.rollback()
        continue

Optimistic locking is right when conflicts are rare. For high-conflict scenarios, use SELECT FOR UPDATE.

Pessimistic locking — `SELECT ... FOR UPDATE`

stmt = select(Account).where(Account.id == 42).with_for_update()
account = session.scalars(stmt).one()
account.balance -= 100
session.commit()

with_for_update() adds FOR UPDATE to the SELECT, holding a row-level lock until commit/rollback. Other transactions trying to update the same row wait.

Options:

.with_for_update(nowait=True)             # error immediately if locked
.with_for_update(skip_locked=True)        # skip locked rows (work queue pattern)
.with_for_update(of=Account)               # lock only specific table in a join
.with_for_update(read=True)                # FOR SHARE — read lock, weaker

skip_locked=True is the canonical pattern for “atomic job dequeue”:

stmt = (
    select(Job)
    .where(Job.status == "pending")
    .order_by(Job.id)
    .limit(1)
    .with_for_update(skip_locked=True)
)
job = session.scalars(stmt).first()
if job:
    job.status = "processing"
    session.commit()

Multiple workers running this concurrently each pick a different row.

Two-phase commit (XA)

For distributed transactions spanning multiple databases:

session = Session(engine, twophase=True)

Most apps don’t need this — coordinated commits across DBs are operationally painful. Prefer event-driven / saga patterns for cross-service consistency.

Common error: serialization failure

Postgres SERIALIZABLE may abort transactions that would violate serializability:

psycopg.errors.SerializationFailure: could not serialize access due to concurrent update

In SQLAlchemy this surfaces as OperationalError. The right response is retry the whole transaction:

from sqlalchemy.exc import OperationalError

for attempt in range(3):
    try:
        with Session(engine) as session, session.begin():
            do_work(session)
        break
    except OperationalError as e:
        if "could not serialize" not in str(e):
            raise
        time.sleep(0.1 * (2 ** attempt))     # exponential backoff
else:
    raise RuntimeError("retries exhausted")

Use a library (tenacity) instead of hand-rolled retries in production.

Deadlocks

Two transactions waiting on each other’s locks. The DB picks one to abort:

DeadlockDetected: deadlock detected

SQLAlchemy doesn’t auto-retry. Same retry pattern as serialization failures.

Prevent deadlocks:

Acquire locks in consistent order across transactions.
Keep transactions short.
Avoid long lock-holding (e.g. SELECT FOR UPDATE followed by network I/O).

autocommit mode (legacy)

# Legacy 1.x — discouraged
engine = create_engine("...", isolation_level="AUTOCOMMIT")

Every statement is its own transaction. Useful for VACUUM, DDL on Postgres, or any operation that can’t run inside a transaction. For application code, stick with the regular transaction model.

Common pitfalls

Multiple commits in one logical operation — if you commit halfway through and the second half fails, you’ve partially applied the change. Wrap the whole thing in one transaction.
Long-running transactions — hold locks, bloat the WAL, block other writers. Keep transactions short; commit after each unit of work.
SELECT FOR UPDATE without an index on the WHERE column — locks every row scanned, not just matched.
Forgetting session.rollback() after exception — session enters PendingRollbackError state, next op fails until rolled back. Use with session.begin() so it auto-rolls.
Catching IntegrityError and continuing in the same transaction — Postgres aborts the transaction; subsequent statements fail until rollback.

Common interview confusions

“session.commit() finishes the transaction.” — yes, in SQLAlchemy. But on the DB side, every transaction starts implicitly on the next statement (autocommit off), so the session begins a new one transparently.
“Isolation level is set per query.” — typically per connection or per session. Some DBs allow setting per-transaction (SET TRANSACTION ISOLATION LEVEL ...).
“begin_nested runs a separate transaction.” — it runs a SAVEPOINT inside the same transaction. Rollback affects only the savepoint, not the outer transaction.

Interview angle

“How do savepoints work in SQLAlchemy?” — session.begin_nested() issues SAVEPOINT name; exit of block does RELEASE or ROLLBACK TO. Lets nested ops fail without aborting the outer transaction.
“How do you set isolation level?” — engine = create_engine(..., isolation_level="SERIALIZABLE") for the engine; or conn.execution_options(isolation_level="...") per connection.
“Optimistic vs pessimistic locking — which does SQLAlchemy support?” — both. Optimistic via __mapper_args__["version_id_col"] (UPDATE checks version, raises StaleDataError on conflict). Pessimistic via select(...).with_for_update() (DB-level row lock).
“How do you implement a work queue with SELECT FOR UPDATE SKIP LOCKED?” — query: select(Job).where(status=='pending').order_by(id).limit(1).with_for_update(skip_locked=True). Multiple workers each grab a different row atomically.
“What’s a serialization failure?” — Postgres SERIALIZABLE detects that committing would violate serializability and aborts. SQLAlchemy raises OperationalError. The fix is retry with backoff — don’t try to handle in-place.
“How would you ensure a long-running batch script doesn’t hold one giant transaction?” — chunk into N-row batches; commit per batch. Each commit closes the transaction, releases locks, lets concurrent writers in.

Transactions, Savepoints, Isolation

Transactions, Savepoints, Isolation

The default model

Explicit begin

Savepoints — begin_nested()

Tests + transactional rollback

Isolation level

Optimistic locking with version counters

Pessimistic locking — SELECT ... FOR UPDATE

Two-phase commit (XA)

Common error: serialization failure

Deadlocks

autocommit mode (legacy)

Common pitfalls

Common interview confusions

Interview angle

Savepoints — `begin_nested()`

Pessimistic locking — `SELECT ... FOR UPDATE`