skip to content

A SQLAlchemy test binds Session(bind=connection) to an outer transaction without join_transaction_mode; after the code rolls back and retries, its committed row survives teardown. Why?

level: seniorimportance: nice to knowfreq 15%

answer

  1. the default mode is conditional
  2. no SAVEPOINT on the connection yet
  3. rollback reaches the outer transaction
  4. transaction already deassociated from connection

basics

~10 s

The default join_transaction_mode, "conditional_savepoint", behaves as "rollback_only" when the connection holds no SAVEPOINT: Session.rollback() rolls back the outer transaction itself, and the retry's commit runs in a fresh transaction that really commits.

solid answer

~30 s

With no `join_transaction_mode`, a Session gets `"conditional_savepoint"`. On a connection inside `begin()` but not `begin_nested()`, that resolves to `"rollback_only"`: commits are not propagated, but `session.rollback()` rolls back the test's outer transaction. After that the connection has no transaction, so the retry makes the Session call `connection.begin()` itself and own the new transaction; its `commit()` is real. Teardown's `trans.rollback()` then warns `transaction already deassociated from connection` and removes nothing. The fix is `join_transaction_mode="create_savepoint"`, or opening `connection.begin_nested()` after `begin()` so the default resolves to savepoints. `"control_fully"` is no fix: there even the first commit is real.

code

python · 46 lines
python
import warnings

from sqlalchemy import String, create_engine, select
from sqlalchemy.orm import DeclarativeBase, Mapped, Session, mapped_column
from sqlalchemy.pool import StaticPool

warnings.simplefilter("ignore")  # hides the teardown SAWarning for brevity


class Base(DeclarativeBase):
    pass


class Customer(Base):
    __tablename__ = "customer"
    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String(50))


def run(**session_kw):
    engine = create_engine(
        "sqlite://",
        poolclass=StaticPool,
        connect_args={"check_same_thread": False, "autocommit": False},
    )
    Base.metadata.create_all(engine)
    connection = engine.connect()
    trans = connection.begin()
    session = Session(bind=connection, **session_kw)

    # code under test: first attempt fails and is rolled back, the retry commits
    session.add(Customer(name="first"))
    session.flush()
    session.rollback()
    session.add(Customer(name="retry"))
    session.commit()

    session.close()
    trans.rollback()  # teardown
    connection.close()
    with engine.connect() as conn:
        return conn.scalars(select(Customer.name)).all()


print("default:         ", run())
print("create_savepoint:", run(join_transaction_mode="create_savepoint"))

go deeper

for a junior

Remember that join_transaction_mode exists and that test harnesses should set it to create_savepoint rather than relying on the default.

for a middle

Name the four modes and say what commit, rollback and close each do to the outer transaction under each one.

for a senior

Trace a leak from the teardown warning to the rollback that ended the outer transaction, and harden the harness so the next leak fails loudly in the test that caused it.

for a principal

Treat the harness as shared infrastructure: one reviewed fixture, the mode named explicitly, warnings as errors, and a regression test for the rollback path.

## The symptom A suite uses the external-transaction recipe but builds its Session as **`Session(bind=connection)`**, leaving out the mode. Most tests stay isolated. A few leak rows: those whose code under test calls **`session.rollback()`** (an error path, a retry, a conflict handler) and then commits. Teardown logs: ```plaintext SAWarning: transaction already deassociated from connection ``` and the next test finds rows it never wrote. ## The four modes **`join_transaction_mode`** only matters when the Session's bind is a `Connection` that is already in a transaction, and only once the Session actually uses it. | Value | What the Session does with the outer transaction | |---|---| | `"conditional_savepoint"` (default) | acts as `"create_savepoint"` if the connection is inside a SAVEPOINT, otherwise as `"rollback_only"` | | `"create_savepoint"` | always opens its own SAVEPOINT with `begin_nested()`; the outer transaction is left as it was | | `"rollback_only"` | `commit()` is not propagated, `close()` has no effect, but `rollback()` rolls back the outer transaction | | `"control_fully"` | takes the transaction as its own: `commit()` commits it, `rollback()` and `close()` roll it back | Any other value raises `ArgumentError` with `invalid selection for join_transaction_mode`. ## Tracing the leak 1. The test runs `connection.begin()`; there is no SAVEPOINT, so the default resolves to **`"rollback_only"`**. 2. The code adds a row, flushes, hits an error and calls `session.rollback()`. In `rollback_only` mode that **rolls back the outer transaction**, which ends it. 3. The retry adds a row. The connection is no longer in a transaction, so the Session begins a fresh one with `connection.begin()`, and **owns** it. 4. `session.commit()` commits that transaction for real. 5. Teardown calls `trans.rollback()` on a transaction that is already gone, gets the `SAWarning`, and removes nothing. Without step 2 nothing leaks, which is why the defect shows only in tests that exercise failure paths. The rollback in step 2 need not be an explicit call. Any rollback the Session performs counts: - the rollback `with session.begin():` performs when its block raises; - a rollback inside an error handler or a retry decorator in the code under test; - a rollback the test itself issues to reset state between steps. Each one ends the outer transaction under `rollback_only`, and whatever the Session does afterwards runs in a transaction the test no longer owns. ## What the log shows With `echo=True`, the failing test's statements read like this (parameter lines removed): ```plaintext BEGIN (implicit) INSERT INTO customer (name) VALUES (?) ROLLBACK BEGIN (implicit) INSERT INTO customer (name) VALUES (?) COMMIT ``` Two markers give it away. There is **no `SAVEPOINT`** anywhere, so the Session never isolated itself, and there is a **`COMMIT`**, which a test isolated by this recipe never produces. Searching a suite's SQL log for `COMMIT` lines outside fixture setup is a fast way to find tests that leak this way. ## Fixes - **Name the mode**: `Session(bind=connection, join_transaction_mode="create_savepoint")`. Commits release savepoints, rollbacks roll back to them, and the outer transaction survives both. - **Or give the default a savepoint to find**: call `connection.begin_nested()` right after `connection.begin()`. The default then resolves to `"create_savepoint"`. - **Do not reach for `"control_fully"`**: it hands the outer transaction to the Session, so even the first `commit()` is real. - **Make the warning fatal** in the suite (for example by turning `SAWarning` into an error in the runner's warning filters) so a leak fails the test that caused it instead of a later one. ## Why the default is what it is SQLAlchemy's documentation explains the choice: the default uses savepoints only if one is already in progress, because it cannot assume every backend supports SAVEPOINT. It also keeps approximate compatibility with how 1.4 treated a connection handed to a Session, and the documentation recommends setting one of the explicit modes rather than relying on it. That history is also why the bug is easy to miss: harness code copied from older examples binds the Session to the connection and stops there, and it can pass for months until a test exercises a rollback. ## Review checklist for a test harness - Every Session bound to a test connection names `join_transaction_mode`. - At least one test drives a rollback-then-commit path and asserts the table is empty in the next test. - `SAWarning` is an error in the test run. - No test helper builds a second Session from the engine.

  • When does the default conditional_savepoint mode pick SAVEPOINTs on its own?
    When the connection it is given is already inside a SAVEPOINT, meaning `Connection.in_nested_transaction()` is true. Calling `connection.begin_nested()` after `connection.begin()` in the harness is enough; otherwise it falls back to `rollback_only`.
  • Why is control_fully the wrong mode for a test harness?
    It makes the Session the owner of the transaction it was handed: `commit()` commits it, `rollback()` and `close()` roll it back. The first commit in the code under test therefore persists data, and teardown has nothing left to roll back.

  • Assuming the default join_transaction_mode always uses SAVEPOINTs
  • Believing a Session bound to a Connection can never end the outer transaction
  • Choosing control_fully for a test harness to make commits behave normally
  • Ignoring the transaction already deassociated from connection warning at teardown
  • Testing only happy paths, so the rollback-then-commit leak never runs