Gnosnay's little patch

SQLAlchemy + MySQL: What Does commit() Actually Commit? A Clear Pass on connection / session / txn / savepoint

Recently I was reading a piece of testing infrastructure code that roughly does this: get a connection from async_engine.connect(), open a big outer transaction with begin(), open a savepoint with begin_nested(), then bind that same connection directly into an AsyncSession. Inside tests, you’re allowed to call session.commit() as much as you want, and in teardown the fixture just does trans.rollback() once to wipe everything.

Intuitively it looks awesome: commit all you like, it doesn’t matter, we rollback at the end anyway. But here’s the catch: once you stack these concepts and SQLAlchemy’s abstraction layers together (Session, SessionTransaction, Transaction, Connection, DBAPIConnection), plus MySQL’s “strongly serialized” protocol behavior, my brain starts knotting up—especially when you look at SessionTransaction.commit()’s implementation: it clearly calls trans.commit(), so why does it look like “it didn’t commit the whole transaction”? And if the pool only has one connection, what changes if you switch to bind=engine? And what even is join_transaction_mode?

This post organizes the issues we hit in our discussion in my own mental model. Treat it as an “engineer’s bench notes” article: not guaranteed to cover every edge case, but after reading you should be able to explain: what commit commits, why commit ≠ committing the outer transaction, why pool=1 blocks other sessions, and what join_transaction_mode is actually designed to control.

Disclaimer: This is written from an engineering perspective and aims to be reproducible, but details vary across databases, drivers, and SQLAlchemy versions. Read critically.

0. The single most important premise: the MySQL wire protocol is strongly serialized

MySQL’s classic/native protocol is a strict request → response sequence: on the same connection, once you send a command, you must fully read its response before the connection’s state machine can proceed to the next command. This “strong serialization” is at the protocol level—it’s not optional.

So the practical engineering conclusions are:

  • On the same connection, there can be only one in-flight request at a time
  • You can do concurrency with asyncio, but the unit of concurrency is multiple connections, not “multiple coroutines concurrently sending packets on one connection”
  • If you truly share a single connection across coroutines, you must serialize it yourself (lock/queue), otherwise you’ll hit the classic “packet read/write corruption” and “result mix-up” disasters

We’ll reuse this premise repeatedly later.

1. connection / session / transaction: put the vocabulary in the right layers

I often see people mix these three terms together, especially treating SQLAlchemy’s “Session” as MySQL’s “Session”. They are not the same thing.

1.1 In MySQL terms (database layer)

  • Connection: a TCP/Unix socket connection
  • Session: the server-side session context maintained for that connection (basically 1:1)
  • Transaction: the “current transaction” attached to that session (only one at a time; a savepoint is a checkpoint inside the transaction, not a second transaction)

The key point: transactions are bound to the connection/session. If you only have one connection, there is no such thing as “running two independent transactions at the same time.”

1.2 In SQLAlchemy terms (framework layer)

This is where it gets mind-bending, because the same words are split into multiple objects:

  • DBAPIConnection: the real connection provided by the driver (e.g. aiomysql/pymysql/mysqlclient), responsible for protocol IO
  • Connection (SQLAlchemy Core): a proxy/facade over DBAPIConnection that adds pooling, execution, transaction management, etc.
  • Transaction (SQLAlchemy Core): the transaction object returned by conn.begin() / conn.begin_nested(), corresponding to DB BEGIN / SAVEPOINT
  • Session (ORM): the Unit of Work + identity map; manages object state, flush ordering, cascades, etc.
  • SessionTransaction (ORM): an internal object representing the Session’s current transactional state; it drives the underlying Core Connection/Transaction

One-sentence memory hook:

The ORM Session is not the database session; it’s more like an “object state manager”. The real database transaction lives down at the Connection/Transaction layer.

2. Why so many “similar-looking” concepts? Isn’t this over-designed?

It’s totally normal to find this weird—because it looks like SQLAlchemy rebuilt “connection/transaction” multiple times. But motivation-wise, the layering solves three real engineering pain points:

  1. The ORM’s job is not protocol IO and connection reuse The Session needs object consistency, flush, identity map. It shouldn’t implement pooling, and it definitely shouldn’t care about MySQL packet headers.

  2. Core and ORM must be usable independently Many projects use only Core to run SQL, and many mix ORM + Core. Extracting Connection/Transaction enables composition.

  3. Pooling needs to turn “close” into “return to pool” This is where many people first get bitten: in SQLAlchemy, Connection.close() often means “I’m done” rather than “physically disconnect”. Physical connections are expensive—reuse is the point.

So those objects exist primarily for separation of concerns. It’s annoying, but it enables advanced patterns (testing fixtures, framework-managed transactions, mixing Core/ORM, savepoints, pool reset, etc.).

3. The key question: why does session.commit() sometimes not commit the whole transaction?

You referenced the core logic of SessionTransaction.commit(). In plain English:

session.commit() iterates over the Session’s recorded _connections, and for those with should_commit=True, it calls trans.commit().

So the real question isn’t “does commit trigger a commit”, but:

  • Which trans objects are stored in _connections? (outer Transaction or NestedTransaction?)
  • For which of those does should_commit become True? (does the Session actually own the right to commit them?)

That’s exactly what explains the counterintuitive behavior in your test fixture.

4. Your fixture: why commit only commits the nested/savepoint boundary

Your structure (simplified) is:

  1. connection = engine.connect()
  2. trans = connection.begin() — outer transaction (OUTER)
  3. nested = connection.begin_nested() — savepoint (NESTED)
  4. session = AsyncSession(bind=connection)

The key fact is just one sentence:

The outer transaction trans was not started by the Session—it was started outside the Session.

So the Session faces a re-entrancy problem: I’m bound to a connection that is already in a transaction. Should my commit/rollback/close affect the outer transaction?

If the Session directly committed the outer transaction, external code controlling the transaction boundary would break (e.g. your test teardown wants to rollback, but you already committed). That’s why SQLAlchemy introduces a “re-entrancy strategy” parameter: join_transaction_mode.

With conservative strategies (like rollback_only / create_savepoint), Session.commit() often only affects the transaction boundary it “owns”—typically the savepoint:

  • session.commit() → RELEASE SAVEPOINT (commit the nested boundary)
  • the outer trans is still alive
  • teardown trans.rollback() wipes everything

That’s why you observed “commit happened but didn’t commit the whole transaction”: you committed an inner boundary, not the outer dam gate.

Engineering-wise, this isn’t a bug—it’s a classic test-isolation pattern: allow commits inside the test, but never allow durable writes.

5. What is join_transaction_mode? It’s about “transaction ownership”, not concurrency

Your question is sharp: since MySQL only allows one command at a time on a connection, does binding a Session onto a connection already in a transaction break that assumption?

No. Because join_transaction_mode is about nested/re-entrant transaction boundaries, not concurrent use of the same connection.

A savepoint is not concurrency. It’s still serialized commands:

BEGIN;
SAVEPOINT sp;
... DML ...
RELEASE SAVEPOINT sp;
... DML ...
COMMIT;

join_transaction_mode decides: when an outer transaction already exists, should the Session’s commit/rollback:

  • fully take over the outer transaction (control_fully)
  • allow rollback propagation but prevent commit propagation (rollback_only)
  • always create a savepoint so the inner boundary can commit without affecting the outer (create_savepoint)
  • or use a default compromise (conditional_savepoint)

You can think of it like a re-entrant lock strategy: if an outer layer already holds the lock, what should an inner with lock: do—increment a counter, take a separate sub-lock, or forbid releasing the outer lock?

6. What if I don’t bind a connection, and instead bind the engine (pool=1)?

This is a classic “looks similar but is totally different” trap.

6.1 bind=conn: you force the Session to use that exact connection

Outer transaction, savepoint, event listeners that recreate savepoints—everything revolves around one connection, so semantics are very controllable.

6.2 bind=engine: the Session checks out connections from the pool

Even with pool_size=1, that only means “at most one Session can hold a connection at a time”. It does not mean your external connection.begin() is automatically wrapping what the Session does.

So you get:

  • Session A checks out the connection and leaves it in use (or in a transaction)
  • Session B tries to run SQL → blocks during checkout
  • the block happens because “no connection is available”, not because of join_transaction_mode

More importantly, your original “outer transaction guarantees final rollback” pattern can be broken. In the engine-bound path, session.commit() is much more likely to send a real COMMIT and make changes durable (unless you restructure boundaries using Session.begin() or engine.begin() around it).

So a corrected mental model for pool=1 is:

pool=1 queues concurrent requests, but it does not automatically implement “outer transaction never commits” test isolation for you.

7. A minimal comparison: single-session vs multi-session behavior

7.1 Single Session, bind=conn (your current tests)

  • outer trans: not owned by the Session
  • Session commit: only commits the savepoint (and can be recreated)
  • teardown: outer rollback clears everything

Great for tests: you want “committable inner boundaries + mandatory final rollback.”

7.2 Multiple Sessions, bind=engine + pool=1

  • Session A checks out the connection; if it doesn’t finish, Session B must block
  • if A really commits, changes may become durable (unless there’s an outer rollback boundary)
  • join_transaction_mode mostly doesn’t matter, because the Session isn’t joining an external transaction—it is the owner

Closing: the abstraction isn’t pretty, but it’s practical

I’ll complain too: stacked together, these concepts really do look like “duplicated abstractions”. But if you read it as “re-entrant transaction strategy management + pooled connection resource management + ORM unit-of-work management”, the weird behaviors become coherent:

  • commit didn’t commit the outer transaction: because the Session doesn’t own the outer boundary
  • pool=1 blocks other sessions: because there’s only one connection resource
  • bind=conn vs bind=engine behave very differently: because with conn you control the connection + outer transaction; with engine you hand control to the pool + Session

I’ll stop here. If you share your current SQLAlchemy version, your sessionmaker params (especially whether join_transaction_mode is explicitly set), and whether your goal is truly “allow commit but never persist”, I can help you simplify your fixture into a more footgun-resistant version (including: when to use create_savepoint vs rollback_only, and how to prevent concurrent use of the same connection in async).

References

Just in case you need it, here’s my configuration. The final effect is:

  1. When tests start, the whole test process has only one DB-API Connection, shared by all test cases
  2. Between test cases, we use savepoints for isolation
# pytest configuration
@pytest.fixture(scope="function", autouse=True) # Must be function scope; otherwise isolation won't work
@classmethod
async def async_db_session(cls) -> AsyncIterator[AsyncSession]:
   connection = await async_engine.connect()
   trans = await connection.begin()
   async_session = TestAsyncSession(bind=connection)
   nested = await connection.begin_nested()

   # Recreate the savepoint whenever a transaction ends
   # Without this listener, every commit would commit the outer transaction
   @event.listens_for(async_session.sync_session, "after_transaction_end")
   def end_savepoint(session: Any, transaction: Any):  # type: ignore
      nonlocal nested

      if not nested.is_active and connection.sync_connection:
            nested = connection.sync_connection.begin_nested()

   yield async_session

   # After each test case, rollback the transaction and close the connection
   await trans.rollback()
   await async_session.close()
   await connection.close()

# fastAPI async db session dependency
async def _mock_db_session() -> AsyncIterator[AsyncSession]:
   try:
         # Before start, clear all Session cache
         # to avoid cached data affecting test results
         async_db_session.expunge_all()
         async_db_session.expire_all()
         yield async_db_session
   finally:
         # After the test case finishes, clear all Session cache
         # to avoid cached data affecting the next test case
         async_db_session.expunge_all()
         async_db_session.expire_all()

# You can inject it into FastAPI
@pytest.fixture(scope="function", autouse=True)
@classmethod
async def test_client(
   cls,
   app: FastAPI,
   async_db_session: AsyncSession,
) -> AsyncIterator[AsyncClient]:
   async def _mock_db_session() -> AsyncIterator[AsyncSession]:
      try:
            # Before start, clear all Session cache
            # to avoid cached data affecting test results
            async_db_session.expunge_all()
            async_db_session.expire_all()
            yield async_db_session
      finally:
            # After the test case finishes, clear all Session cache
            # to avoid cached data affecting the next test case
            async_db_session.expunge_all()
            async_db_session.expire_all()

   app.dependency_overrides[get_db_session] = _mock_db_session

   try:
      async with AsyncClient(
            transport=ASGITransport(app=app),  # type: ignore
            base_url="http://test",
      ) as client:
            yield client
   finally:
      app.dependency_overrides.clear()