skip to content

What does SQLAlchemy's `expire_on_commit=True` default do to a Session's objects after commit, and what does it cost the code that reads them?

level: middleimportance: must knowfreq 62%

answer

  1. state erased when the transaction ends
  2. next attribute read goes back
  3. one SELECT per expired object
  4. sessionmaker(engine, expire_on_commit=False)

basics

~20 s

Session.commit() expires every object it holds, erasing the loaded values. The next attribute read autobegins a transaction and SELECTs that row again, so reads after commit cost extra queries, and objects from a closed Session cannot reload.

solid answer

~40 s

`expire_on_commit` defaults to `True` on both `Session` and `sessionmaker`. After `Session.commit()` every instance in the Session is **expired**: its column values are erased so that the next access loads the current database state, because once the transaction has ended other transactions may have changed those rows. Reading `invoice.total_cents` after commit therefore autobegins a new transaction and emits a `SELECT` by primary key, one per object touched, holding a pooled connection again. If the Session has been closed in between, the expired object is detached and has nothing to reload from, so the attribute read raises an error instead. Setting `expire_on_commit=False`, on the factory or per call with `Session(expire_on_commit=False)`, keeps the loaded values across commit, at the price that they may be stale. `Session.rollback()` expires everything regardless of this flag.

code

python · 36 lines
python
import logging

from sqlalchemy import create_engine, String
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, sessionmaker


class Base(DeclarativeBase):
    pass


class Invoice(Base):
    __tablename__ = "invoice"
    id: Mapped[int] = mapped_column(primary_key=True)
    number: Mapped[str] = mapped_column(String(20))
    total_cents: Mapped[int]


engine = create_engine("sqlite://")
Base.metadata.create_all(engine)
Session = sessionmaker(engine)  # expire_on_commit=True by default

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

with Session() as session:
    invoice = Invoice(number="INV-7", total_cents=4200)
    session.add(invoice)
    session.commit()  # every attribute of invoice is now expired
    print("total:", invoice.total_cents)  # autobegins and SELECTs the row again

KeepLoaded = sessionmaker(engine, expire_on_commit=False)
with KeepLoaded() as session:
    invoice = Invoice(number="INV-8", total_cents=900)
    session.add(invoice)
    session.commit()
print("total:", invoice.total_cents)  # no SQL: the values survived commit and close

go deeper

for a junior

Recall the default: expire_on_commit is True, so after commit the next attribute read reloads the row from the database.

for a middle

Explain the mechanics: commit erases loaded values, the next read autobegins a transaction and emits a SELECT by primary key, and a closed Session cannot perform that reload.

for a senior

Diagnose post-commit query bursts and unreadable returned objects, and choose between reading before commit, keeping the Session open and a factory with expire_on_commit=False.

for a principal

Decide where a team allows expire_on_commit=False: which factories may hand out snapshot objects, and how that is documented so stale values are never mistaken for fresh ones.

## What "expired" means Every ORM-mapped object a **`Session`** holds carries the column values it loaded. **Expiring** an object erases those loaded values while keeping the object, and its primary key identity, in the Session. The next time code reads an expired attribute, the Session loads the row again with a `SELECT` by primary key, and that refresh brings in every column of the row, not only the one asked for. The parameter **`expire_on_commit`** controls whether `Session.commit()` does this to every object it holds. It is a `Session` constructor argument, so it can be set on the factory, `sessionmaker(engine, expire_on_commit=False)`, or for a single Session, `Session(expire_on_commit=False)` when `Session` is the factory. Its default is **`True`**. ## Why the default is `True` A Session represents one transaction. While that transaction is open, its isolation level says what it can see; once it commits, the Session can no longer vouch for anything it loaded. Another process may have updated the same invoice a millisecond later. Expiring everything at commit means the next read reflects the database as it is now, in a new transaction, rather than a snapshot from a transaction that no longer exists. ## What it costs after commit In a billing job the pattern "write, commit, then log what you wrote" is common, and with the default it is not free: - **One extra `SELECT` per object.** Reading `invoice.total_cents` after commit reloads that invoice's row. A loop that commits and then reads 500 invoices emits up to 500 extra statements, unless a later query happens to return those rows and refresh them. - **A new transaction and a pooled connection.** The reload autobegins a new transaction and checks out a connection, which is held until the next commit, rollback or close; if nothing commits, `close()` simply rolls that read-only transaction back. - **Even the primary key is expired.** `invoice.id` after commit triggers the same reload; nothing about the object stays loaded. - **Closed Sessions cannot reload.** If the function that committed also closed its Session and returned the object, the object is now detached and expired. Reading any column raises an error instead of returning a value, because no Session is there to emit the `SELECT`. ## Reading it in the SQL log With `echo=True`, or the `sqlalchemy.engine` logger at `INFO`, the reload is easy to spot. After the `COMMIT` of the write, reading one attribute logs a second `BEGIN (implicit)`, then a `SELECT` of the mapped columns with `WHERE invoice.id = ?`, and finally a `ROLLBACK` when the Session closes. A burst of single-row SELECTs straight after a `COMMIT`, one per object the code touches, is the signature of `expire_on_commit=True` meeting post-commit reads. ## The options | Setting | After commit | Risk | |---|---|---| | `expire_on_commit=True` (default) | values reload on next access | extra queries; detached objects unreadable | | `expire_on_commit=False` | values kept as last loaded or flushed | values may be stale against the database | Ways to live with the default or leave it: 1. **Read before you commit.** Copy the values you need (an id, a total) into local variables, then commit. 2. **Keep the Session open** for the reads that follow, accepting one `SELECT` per object. 3. **Turn it off where staleness is acceptable**: `sessionmaker(engine, expire_on_commit=False)` for a job whose objects are only reported after commit, never compared with fresh data. ## What it does not control - **`Session.rollback()` expires every remaining object regardless of `expire_on_commit`.** The flag governs commit only; after a rollback the in-memory values may describe changes that never happened, so they are always discarded. - **`Session.close()` does not expire anything.** It detaches the objects; whatever they had loaded at that moment stays readable. The problem appears only when the commit had already expired them first. - Expiring a single object or attribute on demand, and refreshing it, are separate Session methods with their own behaviour, outside this setting. ## A rule of thumb Leave the default on for request-style units of work that commit at the end and stop touching the objects. Turn it off deliberately, per factory, for code whose objects must outlive the Session, and document that those objects are snapshots. Turning it off globally to silence an error usually hides a Session that is closed too early.

  • Does `expire_on_commit=False` also stop `Session.rollback()` from expiring objects?
    No. The flag only governs `Session.commit()`. `Session.rollback()` expires every object that stays in the Session, whatever `expire_on_commit` says, because the in-memory values may describe changes the rollback just undid. Objects that were pending in that transaction are expunged rather than expired, keeping their attribute values.
  • When is `expire_on_commit=False` the right setting for a SQLAlchemy sessionmaker?
    When objects must stay readable after their Session commits or closes and slightly stale values are acceptable: a job that writes invoices and then reports their ids and totals, or code that hands objects to a layer that never opens a Session. It is wrong where code later compares those objects with fresh database state, because nothing reloads them.

  • expire_on_commit defaults to False, so objects keep their values after commit.
  • Reading an attribute after commit returns the cached value without any SQL.
  • Expiring an object removes it from the Session.
  • expire_on_commit=False also stops rollback() from expiring objects.
  • Session.close() expires all objects, so closing is what makes them unreadable.