skip to content

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%

answer

  1. stale after SQL the ORM did not track
  2. mark now, load on access
  3. SELECT straight away
  4. unflushed edits are discarded
  5. a whole query that overwrites

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.

solid answer

~40 s

Within a transaction, a query does not overwrite attributes the Session has already loaded, so a `Product` goes stale when SQL the ORM did not track changes its row: a `text()` or Core `update()` passed to `session.execute()`, a database trigger, or another transaction whose commit your isolation level lets you see. `session.expire(product)` or `session.expire(product, ["stock"])` removes the values without SQL; the next access sends one SELECT for the expired columns. `session.refresh(product, attribute_names=None, with_for_update=None)` expires and SELECTs immediately, optionally with `FOR UPDATE`. `session.expire_all()` expires every persistent object, which `rollback()` and, by default, `commit()` already do. `select(Product).execution_options(populate_existing=True)` refreshes every object a query returns. All of them throw away unflushed changes on the objects they touch.

code

python · 31 lines
python
from sqlalchemy import String, create_engine, text
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:
    product = session.get(Product, 5)
    session.execute(text("UPDATE product SET stock = stock - 3 WHERE id = 5"))
    print(product.stock)                  # 10: the ORM does not know about the UPDATE

    session.expire(product, ["stock"])    # no SQL yet
    print(product.stock)                  # 7: SELECT on access

    product.stock = 100                   # unflushed edit
    session.refresh(product)              # discards the edit, SELECT now
    print(product.stock)                  # 7

go deeper

for a junior

Recall that expire() marks attributes to reload on next access, refresh() reloads immediately, and commit() expires everything by default.

for a middle

Explain why a loaded object goes stale after untracked SQL, which tool fits which case, and that each one discards unflushed edits on the objects it touches.

for a senior

Pick the cheapest reload for a production path, account for isolation level, and replace refresh() loops with one populate_existing query.

for a principal

Argue for short transactions and ORM-tracked writes as the default, so manual expiry stays the exception a reviewer questions.

## Why a loaded object goes stale A SQLAlchemy `Session` loads a row into an object once and then trusts it for the rest of the transaction: a later query that returns the same row is matched to the existing object, and attributes that are already loaded are **not** overwritten. That protects unflushed edits and avoids pointless work, and it is correct as long as every change to the row goes through the ORM. It goes wrong when something else changes the row: - a `text()` statement or a Core `update()` sent with `session.execute()` in the same transaction; - a database trigger or other server-side logic that rewrites columns after a flush; - another transaction that has committed, when your isolation level would let you see its change. **Expiring** is SQLAlchemy's word for erasing an attribute's loaded value so that the next access fetches it again. ## The tools, side by side | Call | When SQL runs | Scope | |---|---|---| | `session.expire(product)` | on the next attribute access | all attributes of one object | | `session.expire(product, ["stock"])` | on the next access | named attributes only | | `session.refresh(product)` | immediately | one object; optional `attribute_names` and `with_for_update` | | `session.expire_all()` | on access, object by object | every persistent object in the Session | | `select(Product).execution_options(populate_existing=True)` | when the query runs | every object the query returns | | `session.get(Product, 5, populate_existing=True)` | immediately | one object, by primary key | Every row has one thing in common: **unflushed changes on the affected objects are discarded**. If you set `product.stock = 100` and then call `expire()` or `refresh()` before a flush, the 100 is gone. ## What actually loads - When any expired **column** attribute is accessed, one SELECT loads all of that object's expired column attributes together. - An expired **relationship** attribute reloads on its own access, through its normal lazy loader. - `refresh()` reloads relationships that were eagerly loaded, and lazy ones only when they are named in `attribute_names`. - Deferred columns stay deferred: they load when accessed directly, not as part of a refresh. - `refresh()` autoflushes before its SELECT, so pending changes on *other* objects are written first. ## When you actually need one 1. **After untracked SQL in the same transaction.** A `text("UPDATE product SET stock = stock - 3 WHERE id = 5")` leaves the loaded `Product` showing the old stock. `session.expire(product, ["stock"])` is the cheapest fix. 2. **After a server-side change you must read now**, such as a column a trigger maintains. `session.refresh(product)` reloads it at once. 3. **To lock and reload in one step**: `session.refresh(product, with_for_update=True)` reloads the row with `SELECT ... FOR UPDATE` on databases that support it. 4. **To refresh many objects at once**: a single `select()` with `populate_existing=True` costs one query, where calling `refresh()` in a loop costs one query per object. You rarely need them after `commit()` or `rollback()`. Both end the transaction and expire every object (commit does so under the default `expire_on_commit=True`), so the next access reads fresh data from a new transaction. ## Reading the example Take one Session and one `Product` row with stock 10: 1. `get()` loads stock 10, and a `text()` UPDATE then lowers it to 7 without the ORM noticing; the attribute still reads 10. 2. `expire(product, ["stock"])` sends nothing; the next read of `stock` sends `SELECT product.stock ...` and returns 7. 3. Setting `stock = 100` and calling `refresh()` throws the edit away and reloads 7 immediately. ## Mistakes to avoid - **Expecting a reload to cross isolation.** `refresh()` reads inside the current transaction. Under REPEATABLE READ or SERIALIZABLE you will see the same values you saw before, however many times you refresh. Commit or roll back first, then read. - **Expiring before flushing.** An edit that has not been flushed is lost; flush first if it must survive. - **Refreshing an object that is not in this Session.** `refresh()` and `expire()` on a transient, pending or detached object raise `InvalidRequestError` ("Instance ... is not persistent within this Session"). - **Refreshing a row that is gone.** `refresh()` raises `InvalidRequestError` ("Could not refresh instance ..."); accessing an expired attribute whose row was deleted raises `ObjectDeletedError`. - **Expiring everything in a loop.** `expire_all()` inside a loop over hundreds of objects makes the next read of each object send its own SELECT, again on every pass.

  • Why might SQLAlchemy's session.refresh(product) still show the old stock after another connection committed a change?
    `refresh()` reads inside the Session's current transaction. Under REPEATABLE READ or SERIALIZABLE isolation, or a snapshot started before the other commit, the database returns the values this transaction already saw. End the transaction with `commit()` or `rollback()` and read again in a new one.
  • What is the cost difference between calling Session.refresh() on 500 Products and running one select() with populate_existing?
    `refresh()` in a loop sends 500 SELECTs, one per object, immediately. A single `select(Product).where(...).execution_options(populate_existing=True)` reloads all matching objects in one query, and can combine with loader options to refresh related collections at the same time.

  • Re-running the same select() refreshes the objects the Session already holds.
  • Session.expire() sends a SELECT immediately.
  • Expiring an object keeps its unflushed edits and applies them over the reloaded row.
  • refresh() can always see another transaction's commit, whatever the isolation level.
  • After commit() you must call expire_all() yourself to see new data.