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?
answer
- the default mode is conditional
- no SAVEPOINT on the connection yet
- rollback reaches the outer transaction
- transaction already deassociated from connection
basics
~10 sThe 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 sWith 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 linesimport 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
Remember that join_transaction_mode exists and that test harnesses should set it to create_savepoint rather than relying on the default.
Name the four modes and say what commit, rollback and close each do to the outer transaction under each one.
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.
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