skip to content

A SQLAlchemy pricing job runs update(Product).where(...).values(...) with synchronize_session=False, and a Product loaded earlier still shows its old price. Why, and what should it use?

level: seniorimportance: should knowfreq 28%

answer

  1. the identity map was not told
  2. auto: evaluate first, then fetch
  3. WHERE matched in Python
  4. RETURNING the primary keys
  5. refresh or expire to recover

basics

~20 s

Loaded objects change only when synchronize_session tells the Session which ones an UPDATE hit; False skips that, so the Product keeps its old price until expired or refreshed. The default 'auto' evaluates the WHERE in Python, using 'fetch' when it cannot.

solid answer

~40 s

An ORM-enabled UPDATE changes rows in the database; the `Product` already in the identity map is a separate Python object that nothing reloads automatically. `synchronize_session` controls that step. `'evaluate'` runs the WHERE clause in Python against loaded objects and applies the SET values, costing no SQL but raising `InvalidRequestError` when the criteria cannot be evaluated, such as with a SQL function. `'fetch'` gets the matched primary keys with RETURNING, or with a SELECT first on MySQL, and updates those objects. The default `'auto'` tries `'evaluate'` and falls back to `'fetch'`. `False` does nothing, which is why the price is stale. Remove the option, or recover with `session.refresh(product)`, `session.expire_all()` or a commit with the default `expire_on_commit=True`.

code

python · 39 lines
python
from decimal import Decimal

from sqlalchemy import Numeric, create_engine, func, insert, update
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(unique=True)
    price: Mapped[Decimal] = mapped_column(Numeric(10, 2))


engine = create_engine("sqlite://")
Base.metadata.create_all(engine)
with Session(engine) as session, session.begin():
    session.execute(insert(Product), [{"sku": "ab-1", "price": Decimal("10.00")}])

with Session(engine) as session, session.begin():
    product = session.get(Product, 1)

    session.execute(
        update(Product)
        .where(Product.sku == "ab-1")
        .values(price=Decimal("12.00"))
        .execution_options(synchronize_session=False)
    )
    print(product.price)  # 10.00 -- the identity map was not told

    session.execute(
        update(Product)
        .where(func.upper(Product.sku) == "AB-1")
        .values(price=Decimal("13.00"))
    )  # default 'auto': cannot evaluate upper() in Python, so it uses 'fetch'
    print(product.price)  # 13.00

go deeper

for a junior

Recall that objects already loaded in a Session do not change by themselves when an UPDATE statement runs.

for a middle

Explain the four synchronize_session values, what each does to loaded objects, and what auto resolves to.

for a senior

Show you diagnose the stale read from the option in use, pick refresh, expire or a strategy change, and know where evaluate raises.

for a principal

Set a team rule on when False is allowed, for example only with a fresh Session per batch, and how reviews check it.

## Why the object is stale A `Session` keeps an **identity map**: one Python object per database row it has loaded. An ORM-enabled `update(Product).where(...).values(...)` bypasses the unit of work and changes rows directly in the database. The loaded `Product` is not reloaded by that statement, so unless the Session is told which objects the UPDATE touched, it keeps showing the values it had. The **`synchronize_session`** execution option is that telling step. Set it on the statement with `.execution_options(synchronize_session=...)` or pass `execution_options={"synchronize_session": ...}` to `Session.execute()`. ## The four strategies | Value | How it finds affected objects | Extra SQL | Fails when | |---|---|---|---| | `'auto'` (default) | Tries `'evaluate'`; falls back to `'fetch'` if the criteria cannot be evaluated | Only on fallback | Only where `'fetch'` cannot run either | | `'evaluate'` | Compiles the WHERE clause into a Python function and tests each loaded object | None | The criteria use something the evaluator cannot run in Python, such as a SQL function: `InvalidRequestError` | | `'fetch'` | Reads the matched primary keys with RETURNING, or with a SELECT before the statement where RETURNING is missing | RETURNING columns or one SELECT | Not available for bulk UPDATE by primary key | | `False` | Does nothing | None | Never fails; leaves objects stale | With an UPDATE, matched objects get the new values written in, or the attributes expired so they reload on next access. With a DELETE, matched objects are marked **deleted** and leave the session. ## What 'auto' actually does `'auto'` prefers `'evaluate'` because it costs no round trip when the Session holds few objects and the UPDATE matches many rows. Only when evaluation is impossible does it switch to `'fetch'`. On SQLite the echo log shows the difference: ```sql -- WHERE product.sku = ? : evaluated in Python UPDATE product SET price=? WHERE product.sku = ? -- WHERE upper(product.sku) = ? : not evaluable, falls back to fetch UPDATE product SET price=? WHERE upper(product.sku) = ? RETURNING id ``` Choosing `'evaluate'` explicitly turns that silent fallback into an error: `Could not evaluate current criteria in Python: "Cannot evaluate Function". Specify 'fetch' or False for the synchronize_session execution option.` On MySQL, which has no UPDATE ... RETURNING, `'fetch'` runs a SELECT of the matching primary keys before the UPDATE, a separate statement. ## Recovering after False - `session.refresh(product)` reloads that object's attributes now. - `session.expire(product)` or `session.expire_all()` marks attributes stale so the next access reloads them. - Committing with the default `expire_on_commit=True` expires everything. - Neither `session.flush()` nor `session.merge(product)` fixes the stale price: flush writes pending changes, and merging an object already in the session returns it unchanged. ## Edge cases worth knowing - An attribute you changed in Python but have not flushed is **expired** rather than overwritten when synchronization applies. Normally autoflush has already written it before the UPDATE runs. - Objects that changed only because of **ON DELETE CASCADE** or **ON UPDATE CASCADE** on related tables are not synchronized; expire them or commit. - `with_loader_criteria()` criteria are included in the statement and in the synchronization step. ## How the bug unfolds in the pricing job 1. The job loads a `Product` with `session.get(Product, 17)` to compute a discount. 2. Someone sets `synchronize_session=False` on the price UPDATE "for speed", so the statement runs and the object is not told. 3. Later in the same transaction the code reads `product.price` to write an audit row, gets the old value, and records a wrong `old_price`/`new_price` pair. 4. Nothing errors, so the defect surfaces only when audit rows disagree with the table. The fix is to drop the option, letting `'auto'` keep the object current, or to read the price from the statement itself with `.returning(Product.id, Product.price)`, which avoids depending on in-memory state. ## Choosing a strategy 1. Leave `'auto'` in almost all code; it is the default for a reason. 2. Use `'fetch'` when you know the criteria cannot be evaluated and want the RETURNING path directly. 3. Use `False` only when the Session holds none of the affected objects, as in a job with a fresh Session per batch, or when you expire or commit immediately afterwards.

  • Why does `synchronize_session='evaluate'` raise for a SQLAlchemy UPDATE whose WHERE uses `func.upper(Product.sku)`?
    The evaluator turns criteria into Python functions, covering comparisons, AND/OR, IN, IS, simple arithmetic and a few string operators on mapped attributes. A SQL function has no Python implementation, so it raises `InvalidRequestError` asking for `'fetch'` or `False`. Under the default `'auto'` the same statement silently falls back to `'fetch'`. Custom operators made with `Operators.op()` can supply `python_impl` to stay evaluable.
  • What does SQLAlchemy's `'fetch'` strategy cost on MySQL compared with PostgreSQL?
    On PostgreSQL and SQLite, `'fetch'` adds the primary key columns to RETURNING, so the UPDATE itself reports the matched rows. MySQL has no UPDATE ... RETURNING, so the ORM first runs a SELECT of the matching keys and then the UPDATE: one more round trip.

It is like editing a shared spreadsheet while a colleague works from a printout: the sheet is right, but their copy stays wrong until someone tells them which rows changed, or they reprint it.

  • An UPDATE statement automatically reloads every matching object in the Session.
  • The default 'auto' always uses 'fetch' wherever RETURNING is available.
  • session.flush() reloads attributes changed by an ORM UPDATE.
  • 'evaluate' falls back to 'fetch' quietly when it cannot evaluate the WHERE.
  • synchronize_session also controls whether relationship cascades run.