An async SQLAlchemy handler loads an Order, commits, then reads order.customer.name and order.items and gets MissingGreenlet; what causes it, and what is the fix?
answer
- two causes stacked
- commit expires everything by default
- relationships were never loaded
- expire_on_commit=False plus loader options
basics
~20 sBy default commit() expires every loaded attribute, and customer and items were never loaded, so both reads need a SELECT that plain attribute access cannot await. Set expire_on_commit=False and load both relationships with loader options in the query.
solid answer
~40 sTwo causes stack up. `async_sessionmaker` leaves `expire_on_commit=True`, so `await session.commit()` expires every attribute of the Order; reading anything afterwards needs a refresh SELECT, and outside an `await` that raises `MissingGreenlet`. And `customer` and `items` were never loaded: with the default lazy strategy, their first access is a SELECT too. The fix has two parts: build the factory with `async_sessionmaker(engine, expire_on_commit=False)`, and load the relationships in the original query, for example `select(Order).options(joinedload(Order.customer), selectinload(Order.items))`. Turning off expiry alone is not enough: `order.customer` still lazy-loads inside the session, and once the `async with` block has closed it raises `DetachedInstanceError` instead. The price of `expire_on_commit=False` is that values stay as read, so changes made by triggers or other transactions are not re-read.
code
python · 73 linesimport asyncio
from sqlalchemy import ForeignKey, String, select
from sqlalchemy.ext.asyncio import async_sessionmaker, create_async_engine
from sqlalchemy.orm import (
DeclarativeBase,
Mapped,
joinedload,
mapped_column,
relationship,
selectinload,
)
class Base(DeclarativeBase):
pass
class Customer(Base):
__tablename__ = "customers"
id: Mapped[int] = mapped_column(primary_key=True)
name: Mapped[str] = mapped_column(String(100))
class Order(Base):
__tablename__ = "orders"
id: Mapped[int] = mapped_column(primary_key=True)
status: Mapped[str] = mapped_column(String(20))
customer_id: Mapped[int] = mapped_column(ForeignKey("customers.id"))
customer: Mapped[Customer] = relationship()
items: Mapped[list["OrderItem"]] = relationship()
class OrderItem(Base):
__tablename__ = "order_items"
id: Mapped[int] = mapped_column(primary_key=True)
order_id: Mapped[int] = mapped_column(ForeignKey("orders.id"))
sku: Mapped[str] = mapped_column(String(20))
async def mark_paid(Session: async_sessionmaker, order_id: int) -> str:
async with Session() as session:
stmt = (
select(Order)
.where(Order.id == order_id)
.options(joinedload(Order.customer), selectinload(Order.items))
)
order = (await session.scalars(stmt)).one()
order.status = "paid"
await session.commit()
# nothing expired, everything needed already loaded
return f"{order.customer.name}: {[i.sku for i in order.items]}"
async def main() -> None:
engine = create_async_engine("sqlite+aiosqlite://")
async with engine.begin() as conn:
await conn.run_sync(Base.metadata.create_all)
Session = async_sessionmaker(engine, expire_on_commit=False)
async with Session() as session:
session.add(
Order(
status="new",
customer=Customer(name="Ada"),
items=[OrderItem(sku="A1"), OrderItem(sku="B2")],
)
)
await session.commit()
print(await mark_paid(Session, 1))
await engine.dispose()
asyncio.run(main())go deeper
Remember that async_sessionmaker expires objects on commit unless you pass expire_on_commit=False, and that relationships must be loaded in the query when using AsyncSession.
Explain the two causes separately: expiry turns every post-commit read into a refresh SELECT, and the default lazy strategy makes every first relationship read a SELECT too.
Diagnose from the table of states: MissingGreenlet inside the session, DetachedInstanceError after it closes, and a many-to-one that works only when the identity map already holds the target.
Weigh the stale-data cost of expire_on_commit=False against expiry that async code cannot use anyway, and decide where fresh reads must be explicit with refresh().
## The failing handler An async order service runs a handler shaped like this: open an `AsyncSession`, `await session.get(Order, order_id)`, change `order.status`, `await session.commit()`, then build a response from `order.customer.name` and `order.items`. The commit succeeds and the response fails with `MissingGreenlet` ("greenlet_spawn has not been called; can't call await_() here"). Two independent causes produce the same error, and fixing only one of them leaves the other in place. ## Why expiry exists at all Expiry on commit is a synchronous-world convenience. Once a transaction ends, other transactions may change the rows this session read, so SQLAlchemy discards its copies and lets the next access fetch whatever the database now holds. In synchronous code that is cheap and invisible. In asyncio code the same lazy refetch is exactly the implicit IO the extension forbids, so the convenience turns into a failure mode. ## Cause one: commit expired the object A SQLAlchemy `Session` expires every attribute of every object it holds when a transaction commits, controlled by `expire_on_commit`, which defaults to `True`. `async_sessionmaker` passes the same default through. Expiry means the next read of any attribute, even a plain column such as `order.status` or the foreign key `order.customer_id`, first runs a SELECT to reload the row. In synchronous code that reload is invisible. Under asyncio it is IO on attribute access, which the extension refuses: there is no `await` at `order.customer`, so SQLAlchemy's internal `await_()` has no greenlet to switch out of and raises. ## Cause two: the relationships were never loaded `session.get(Order, order_id)` loads the Order's columns only. With the default `lazy="select"` strategy, `order.customer` and `order.items` are loaded on first access, by a SELECT the attribute read cannot await. So even with expiry switched off, the very first touch of each relationship raises. One exception muddies diagnosis: if the Customer with that primary key is already in the identity map, the many-to-one is answered without SQL and works, so the bug can look intermittent. | Session configuration | `order.status` after commit | `order.customer` after commit, session open | any unloaded relationship after the block closes | |---|---|---|---| | defaults | `MissingGreenlet` | `MissingGreenlet` | `DetachedInstanceError` | | `expire_on_commit=False` only | value | `MissingGreenlet`, unless in the identity map | `DetachedInstanceError` | | `expire_on_commit=False` and loader options | value | value | value | ## The fix, in order 1. **Stop expiry on commit.** Create the factory as `async_sessionmaker(engine, expire_on_commit=False)`; SQLAlchemy's own asyncio examples configure it this way. Loaded values now survive the commit. 2. **Load what the response needs in the query.** Replace the bare `get()` with `select(Order).where(Order.id == order_id).options(joinedload(Order.customer), selectinload(Order.items))`, run through `await session.scalars(...)`. `joinedload()` suits the many-to-one; `selectinload()` suits the collection because it issues one extra `SELECT ... WHERE order_id IN (...)` instead of multiplying rows. 3. **Make a missed load fail loudly.** Declaring the relationships with `lazy="raise"` turns a forgotten option into an immediate `InvalidRequestError` naming the attribute, instead of an intermittent `MissingGreenlet`. If the handler genuinely needs a fresh value after commit, read it explicitly with `await session.refresh(order, ["status"])` rather than relying on expiry. ## What expire_on_commit=False costs - **Stale values.** After the commit, the object still shows what this transaction read or wrote. A column changed by a trigger, by another transaction, or by a server default the flush did not fetch is not re-read. - **Detached objects keep looking valid.** After the `async with` block closes, the object is detached but its loaded attributes still read fine, so a missing load surfaces later, in a different layer, as `DetachedInstanceError`. - **Expiry was hiding nothing useful in async code.** SQLAlchemy's asyncio guidance treats `expire_on_commit=False` as the normal setting and advises `AsyncSession.refresh()` over `AsyncSession.expire()` when fresh data is wanted. ## Verifying the fix - Run the handler with `echo=True` and confirm that all SELECTs happen before the COMMIT line and none after it. - In tests, `lazy="raise"` on the relationships makes any handler that forgets an option fail the first time it touches the relationship. - Keep the response building inside the code that ran the query, so the data the response reads is visibly the data the query loaded. - Watch for the same pattern in background work: a task that receives an Order from a finished handler is reading a detached object, and only what was loaded before the commit is safe to touch.
- With expire_on_commit=False, why does an unloaded relationship raise DetachedInstanceError after the session block instead of MissingGreenlet?Leaving `async with Session() as session:` closes the session and detaches its objects. A lazy load on a detached object fails the session check first, with `DetachedInstanceError` ("Parent instance ... is not bound to a Session; lazy load operation ... cannot proceed"), before any IO is attempted. Inside the block, the session exists, the loader tries the SELECT, and the missing greenlet is what stops it.
- Why load the collection with selectinload() rather than joinedload() here?`joinedload()` on a collection joins the item rows into the order query, multiplying the parent row per item and requiring `unique()` on the result. `selectinload()` sends one extra `SELECT ... WHERE order_items.order_id IN (...)`, which keeps rows flat and batches well across many orders. For the single many-to-one `customer`, `joinedload()` adds one LEFT OUTER JOIN and no row multiplication.
- Setting expire_on_commit=False makes SQLAlchemy load every relationship at commit time.
- The error means the commit failed and the transaction was rolled back.
- AsyncSession never expires attributes, so the commit cannot be involved.
- Wrapping the response building in a second await session.commit() fixes it.
- expire_on_commit=False keeps values fresh because SQLAlchemy re-reads them lazily.