skip to content

Flush, Identity & Expiry

The identity map keeps one object per row, autoflush writes pending changes before a query and expired attributes reload on access. Interviewers ask when merge() or refresh() is needed.

on this pageshow

explore

questions

6

In SQLAlchemy 2.1, when does a Session autoflush, and why can an IntegrityError surface on a statement that only reads?

level: middleimportance: must knowfreq 50%

answer

  1. pending writes go out early
  2. flush is not commit
  3. before every execution in 2.1
  4. the no_autoflush block
  5. rollback after a failed flush

basics

~20 s

With autoflush on, the default, a SQLAlchemy 2.1 Session flushes its pending INSERTs, UPDATEs and DELETEs before every statement it executes, text() included. A constraint those writes break therefore fails on the read that triggered the flush.

solid answer

~40 s

A **flush** sends the Session's pending changes as INSERT, UPDATE and DELETE statements inside the current transaction; it does not commit. With `autoflush=True`, the default for `Session` and `sessionmaker`, the Session flushes before each `execute()`, `scalars()` or `scalar()` call, before a lazy load or `refresh()`, and before `merge()` looks up a row, while `commit()` and `begin_nested()` flush whatever the setting. In 2.1 that covers every execution, Core `text()` included; 2.0 flushed only before ORM-enabled statements. So a duplicate `sku` added earlier raises `IntegrityError` on a later SELECT, with the note "raised as a result of Query-invoked autoflush". After a failed flush the Session must be rolled back with `rollback()`, or the next call raises `PendingRollbackError`. Wrap code that assembles not-yet-valid objects in `with session.no_autoflush:`.

code

python · 35 lines
python
from sqlalchemy import String, create_engine, func, select, text
from sqlalchemy.exc import IntegrityError
from sqlalchemy.orm import DeclarativeBase, Mapped, Session, mapped_column


class Base(DeclarativeBase):
    pass


class Product(Base):
    __tablename__ = "product"
    id: Mapped[int] = mapped_column(primary_key=True)
    sku: Mapped[str] = mapped_column(String(20), unique=True)
    stock: Mapped[int]


engine = create_engine("sqlite://")
Base.metadata.create_all(engine)
with Session(engine) as session, session.begin():
    session.add(Product(id=5, sku="A-5", stock=10))

with Session(engine) as session:
    session.add(Product(id=7, sku="B-7", stock=4))
    # 2.1: autoflush runs before text() too, so the pending row is counted
    print(session.execute(text("SELECT sum(stock) FROM product")).scalar_one())  # 14

    session.add(Product(id=8, sku="A-5", stock=1))   # duplicate sku, still pending
    with session.no_autoflush:
        print(session.scalar(select(func.count()).select_from(Product)))      # 2

    try:
        session.scalar(select(func.count()).select_from(Product))  # autoflush INSERT fails
    except IntegrityError as err:
        print(type(err).__name__)                                  # IntegrityError
        session.rollback()   # required before this Session can be used again

go deeper

for a junior

Recall that flush() sends pending changes inside the transaction while commit() makes them permanent, and that autoflush is on by default.

for a middle

Explain which calls trigger autoflush in 2.1, including text(), why an IntegrityError appears on an unrelated read, and how no_autoflush and an explicit flush() change where it appears.

for a senior

Read the autoflush note in a traceback, find the pending write that caused it, roll the Session back, and decide where explicit flush points belong in a service.

for a principal

Weigh whether a team should ever disable autoflush globally, and plan the 2.1 upgrade review of raw text() reports that now flush before they run.

## Flush versus commit A SQLAlchemy `Session` collects changes in memory: objects passed to `add()` are **pending**, edited attributes are **dirty**, and objects passed to `delete()` are scheduled for deletion. Two different operations send them to the database: - **`session.flush()`** emits the INSERT, UPDATE and DELETE statements inside the current transaction. Primary keys generated by the database are fetched, pending objects become persistent, and the rows are visible to later statements in the same transaction. Nothing is committed: `rollback()` still undoes all of it, and other connections see none of it until the commit (subject to their isolation level). - **`session.commit()`** flushes whatever is still pending and then commits the transaction. **Autoflush** is the Session calling `flush()` for you before it reads, so that queries see the changes you have already made in Python. ## When the Session flushes on its own | Operation | Flushes first? | |---|---| | `session.execute()`, `scalars()` or `scalar()` with an ORM `select()` | yes, when `autoflush=True` | | the same calls with Core `text()` or a Core statement | yes in 2.1; 2.0 skipped them | | `session.get()` that finds the object in the identity map | no, because no SQL is sent | | `session.get()` that has to query, a lazy load, `session.refresh()` | yes, when `autoflush=True` | | `session.merge()` with the default `load=True` | yes, before it looks the row up | | `session.commit()`, `session.begin_nested()` | always, even with `autoflush=False` | `autoflush` defaults to `True` on both `Session` and `sessionmaker`. 2.1 made the rule unconditional: the Session flushes each time an execution takes place, whether the statement is an ORM statement or not. A raw `text("SELECT sum(stock) FROM product")` now counts a `Product` you added a line earlier, which it did not under 2.0. ## Why the error appears on a read The sequence that confuses people: 1. Code calls `session.add(Product(id=8, sku="A-5", stock=1))`. The `sku` column is unique and `A-5` already exists, but no SQL runs yet, so nothing fails. 2. Later, unrelated code runs a totals query through the same Session. 3. Autoflush sends the pending INSERT first; the database rejects it. 4. The `IntegrityError` is raised from the totals query. SQLAlchemy prefixes the message with "raised as a result of Query-invoked autoflush; consider using a session.no_autoflush block if this flush is occurring prematurely", which is the clue that the statement that failed is the flushed write, not the read your code was running. The real defect is the write, not the read. Calling `session.flush()` right after building the object moves the failure to the line that caused it. ## Controlling it - **`with session.no_autoflush:`** disables autoflush for the block and restores the previous setting afterwards; under the hood it sets the `Session.autoflush` attribute to `False` and puts the old value back in a `finally` clause, so an exception inside the block does not leave autoflush off. Use it while assembling an object whose required columns are not set yet but which needs a query to look up a related row. - **`sessionmaker(autoflush=False)`** turns autoflush off for every Session the factory makes. Queries then do not see your own pending changes, and constraint errors move to `commit()`; that is rarely a better trade. - **`session.flush()`** on purpose, when you need a database-generated primary key inside the transaction or want constraint errors raised at a known point. - **`.execution_options(autoflush=False)`** on one ORM statement skips the flush for that statement only. ## After a failed flush When any statement inside a flush fails, SQLAlchemy rolls back the database transaction and puts the Session into an inactive state. Every later use, including `commit()`, raises `PendingRollbackError`: "This Session's transaction has been rolled back due to a previous exception during flush. To begin a new transaction with this Session, first issue Session.rollback()." Only an explicit `session.rollback()` (or closing the Session) clears it. Correcting the bad attribute and flushing again does not. ## The order statements leave in A flush does not replay your `add()` and `delete()` calls in order. The unit of work sorts them by the dependencies between mappers: a `Product` row is inserted before the `StockMove` rows whose foreign key points at it, child rows are deleted before their parent, and within one table the INSERTs and UPDATEs go out before the DELETEs.

  • If SQLAlchemy's commit() flushes anyway, why call session.flush() explicitly?
    To get a database-generated primary key while the transaction is still open, for example to write it into a related row or a log line, and to make constraint errors fail on the line that caused them rather than on a later read. `flush()` leaves the transaction open, so a later `rollback()` still undoes everything.
  • In what order does one SQLAlchemy flush send statements for a new Product and the StockMove rows that reference it?
    In dependency order, not call order: the `Product` INSERT goes first so its primary key exists for the `StockMove` foreign keys, even if the moves were added first. Deletes run the other way, children before the parent, and within one table the INSERTs and UPDATEs precede the DELETEs.
  • What changes for a SQLAlchemy 2.0 code base upgraded to 2.1 whose text() reports ran between add() calls?
    Those `text()` statements now trigger autoflush, so they count rows that are still pending in Python, and a constraint error from a pending write can surface on them. Where a report must not flush, wrap it in `with session.no_autoflush:`; otherwise flush deliberately before the report.

  • A flush commits, so flushed rows are visible to other connections at once.
  • In 2.1, autoflush runs before ORM select() statements but never before text().
  • Setting autoflush=False stops commit() from writing pending changes.
  • After an autoflush IntegrityError the Session can keep querying without a rollback.
  • A flush sends statements in exactly the order the code called add() and delete().

Assumes SQLAlchemy 2.1, where autoflush runs before every Session execution; 2.0 skipped it for Core statements such as text().

open as a page

A SQLAlchemy Product returned after its Session closed raises DetachedInstanceError when a report reads product.stock; why, and how do you fix it?

level: seniorimportance: must knowfreq 45%

basics

~20 s

A detached object cannot load anything, and commit() expired its attributes by default, so reading product.stock needs a SELECT that has no Session to run in. Load what the report needs before the Session closes, or reattach the object to a new Session.

open as a page

In SQLAlchemy 2.1, what does Session.get(Product, 5) do when called twice in one Session, and how does a select() differ?

level: juniorimportance: should knowfreq 50%

basics

~20 s

Session.get() checks the Session's identity map before sending SQL, so a second get(Product, 5) returns the same object with no SELECT. A select() always queries, but returns that same object and leaves its loaded attributes as they were.

open as a page

In SQLAlchemy 2.1, how do Session.expire(), Session.refresh() and populate_existing differ, and when does a loaded Product need one?

level: middleimportance: should knowfreq 40%

basics

~20 s

Session.expire() erases an object's loaded attributes so the next access reloads them; Session.refresh() does that and sends the SELECT at once; populate_existing makes a query overwrite every object it returns. All three discard unflushed edits on those objects.

open as a page

With a SQLAlchemy Session, how do you read an object's state with inspect(), and which calls move it between the five states?

level: middleimportance: should knowfreq 35%

basics

~20 s

sqlalchemy.inspect(obj) returns the object's InstanceState, whose transient, pending, persistent, deleted and detached flags name its state. add() makes it pending, a flush makes it persistent, delete() plus a flush makes it deleted, and closing the Session or committing the deletion detaches it.

open as a page

A SQLAlchemy consumer calls session.merge() on a Product rebuilt from a queue message, then edits that object; why are the edits never written?

level: seniorimportance: should knowfreq 35%

basics

~20 s

Session.merge() copies the incoming object's state onto the Session's own persistent Product, loaded by primary key, and returns that copy. The object passed in never joins the Session, so later edits to it are not tracked; edit the returned object.

open as a page