In SQLAlchemy 2.1, when does a Session autoflush, and why can an IntegrityError surface on a statement that only reads?
answer
- pending writes go out early
- flush is not commit
- before every execution in 2.1
- the no_autoflush block
- rollback after a failed flush
basics
~20 sWith 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 sA **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 linesfrom 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 againgo deeper
Recall that flush() sends pending changes inside the transaction while commit() makes them permanent, and that autoflush is on by default.
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.
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.
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().