skip to content

In SQLAlchemy, what does the connection pool do to a connection when it is returned, and when is pool_reset_on_return=None appropriate?

level: middleimportance: nice to knowfreq 18%

answer

  1. leftover locks and pending writes
  2. the Connection already rolled back
  3. rollback, commit or None
  4. autocommit engines and custom reset handlers

basics

~20 s

By default the pool calls the DBAPI rollback() on every returned connection, unless the Connection already rolled back, so no transaction, locks or pending writes leak to the next borrower. None fits pure autocommit use or a custom reset handler.

solid answer

~40 s

SQLAlchemy resets each connection on checkin. With the default `pool_reset_on_return="rollback"`, the pool calls the DBAPI `rollback()`, discarding uncommitted work and releasing locks. A Core `Connection` or `Session` closed without commit has already rolled back, so it tells the pool to skip the step; the pool's rollback mainly protects raw DBAPI connections. `"commit"` commits whatever is pending and is the riskier choice. `None` is right when the connection always runs in DBAPI autocommit, when the database has no transactions to reset, or when a `PoolEvents.reset` handler replaces the default. Even then a `Connection` closed without commit emits its own ROLLBACK; in SQLAlchemy 2.1, `skip_autocommit_rollback=True` is the more complete switch for autocommit engines.

code

python · 18 lines
python
import logging

from sqlalchemy import create_engine, text

logging.basicConfig(format="%(levelname)s %(name)s %(message)s")
logging.getLogger("sqlalchemy.engine").setLevel(logging.INFO)
logging.getLogger("sqlalchemy.pool").setLevel(logging.DEBUG)

engine = create_engine("sqlite:///orders.db")

# a Core Connection closed without commit(): the Connection rolls back itself
with engine.connect() as conn:
    conn.execute(text("SELECT 1"))

# a raw DBAPI connection handed back: the pool performs the rollback
raw = engine.raw_connection()
raw.cursor().execute("SELECT 1")
raw.close()

go deeper

for a junior

Recall that SQLAlchemy rolls back a returned connection by default, so uncommitted work never reaches the next request that borrows it.

for a middle

Explain how the Connection and the pool share the reset, what the three pool_reset_on_return values do, and why commit is the dangerous one.

for a senior

Recognise when None is justified, autocommit engines or a custom PoolEvents.reset handler, and what state a plain rollback leaves behind on the connection.

for a principal

Decide whether per-connection state such as temporary tables or session settings is allowed at all, and if so who owns the reset handler that cleans it up.

## Why a returned connection needs a reset A pooled DBAPI connection outlives the code that borrowed it. If it came back with a transaction still open, the next borrower would inherit **uncommitted changes, row and table locks**, and on PostgreSQL a server session sitting "idle in transaction". So SQLAlchemy's pool performs a **reset on return**: by default it calls the DBAPI connection's `rollback()` when the connection is checked in. For most drivers a rollback on a connection with nothing pending is cheap. ## Who actually does the rollback The reset is **shared between the `Connection` and the pool**: - A Core `Connection` (or a `Session`, which uses one) that is closed **without `commit()`** rolls back its own transaction first. It then tells the pool the transaction is already reset, and the pool **skips** its own rollback. With pool debug logging you see "reset, transaction already reset". - A **raw DBAPI connection** from `engine.raw_connection()`, or any connection the `Connection` object did not manage, gets the pool's own rollback. The log says "rollback-on-return". So the pool's rollback is the safety net for whatever the `Connection` did not already clean up. ## The `pool_reset_on_return` values `create_engine(..., pool_reset_on_return=...)` sets the pool's behaviour: | value | what happens on checkin | |---|---| | `"rollback"` (default) | DBAPI `rollback()`: discard pending changes, release locks | | `"commit"` | DBAPI `commit()`: **commits whatever is pending**, unconditionally | | `None` | nothing | `True` and `False` are still accepted for backwards compatibility, as `"rollback"` and `None`. `"commit"` exists for databases that cache query plans when a commit is issued, but SQLAlchemy's own documentation calls it more dangerous than `"rollback"`, because a half-finished unit of work is committed instead of discarded. ## When None is appropriate 1. **The connection is in DBAPI autocommit mode at all times**, for example an engine created with `isolation_level="AUTOCOMMIT"`, so there is never a transaction to undo. 2. **The database has no transactions to reset**, such as tables on MySQL's MyISAM storage engine. 3. **A custom reset handler replaces the default.** A `PoolEvents.reset` listener can run a backend's own reset commands; for PostgreSQL the documentation names `DISCARD`, `RESET`, `DEALLOCATE` and `UNLISTEN`. The documented pattern still calls `rollback()` in the handler so the DBAPI's own transaction tracking stays consistent. Two caveats: - With `None`, a `Connection` that ran a statement still emits its own `ROLLBACK` when closed without commit, because a 2.x `Connection` begins a transaction implicitly. `pool_reset_on_return=None` only removes the **pool's** step. - For the autocommit case, SQLAlchemy 2.1 has a more complete switch: `create_engine(..., skip_autocommit_rollback=True)` (available since 2.0.43) has the dialect skip every DBAPI `rollback()` call while the connection is confirmed to be in autocommit mode. Support depends on the dialect. ## Watching it happen Pool logging shows which side performed the reset. Turn it on with `create_engine(..., echo_pool="debug")` or by setting the `sqlalchemy.pool` logger to `DEBUG`: - "being returned to pool" marks every checkin. - "reset, transaction already reset" means the `Connection` rolled back first and the pool skipped its step. - "rollback-on-return" means the pool issued the rollback itself, typically for a raw DBAPI connection. With `echo=True` on the engine as well, the `ROLLBACK` the `Connection` emits appears in the SQL log just before the checkin, which is how you tell an implicit rollback from an explicit `commit()`. ## When the reset fails If the rollback or commit on return raises, the pool treats the connection as unusable: it attempts a final `close()` and **discards** it, and the next checkout gets a new connection. All such invalidations fire the `PoolEvents.invalidate` event. ## What the reset does not clear A rollback ends the transaction, but it does not necessarily reset other per-connection state: **temporary tables** that outlive a transaction, **prepared statement handles**, server-side statement caches, or session settings changed with `SET`. If the application creates such state, either undo it before returning the connection or install a `PoolEvents.reset` handler that does.

  • What happens in SQLAlchemy if the rollback on return itself raises an error?
    The pool treats the connection as unusable: it makes a final attempt to `close()` it and discards it, firing the `PoolEvents.invalidate` event. The next checkout gets a newly opened connection instead.
  • Does SQLAlchemy's reset on return clear temporary tables and prepared statements?
    Not necessarily. A rollback ends the transaction, but temporary tables that outlive a transaction, prepared statement handles and session settings can survive checkin. A `PoolEvents.reset` listener can run the backend's own reset commands, with `pool_reset_on_return=None` so it replaces the default, while still calling `rollback()` itself.

  • SQLAlchemy returns connections to the pool exactly as the code left them
  • pool_reset_on_return="commit" is the safer choice because nothing is lost
  • With pool_reset_on_return=None no ROLLBACK is ever emitted on that engine
  • The pool always issues its own rollback, even after the Connection rolled back

Assumes SQLAlchemy 2.1; create_engine's skip_autocommit_rollback parameter exists since 2.0.43.