skip to content

Implicit IO Errors

Under asyncio, an attribute that would lazy load or refresh on access raises instead of blocking. Interviewers ask about MissingGreenlet and why expire_on_commit=False is the usual setting.

on this pageshow

explore

questions

6

With SQLAlchemy's AsyncSession, which attribute accesses on a mapped object raise MissingGreenlet, and why do they fail instead of loading?

level: middleimportance: must knowfreq 45%

answer

  1. attribute access cannot await
  2. internal await_ needs a greenlet
  3. lazy, expired, deferred, unfetched
  4. the refused SELECT is in the message

basics

~20 s

Under AsyncSession, any attribute access that would emit a SELECT raises MissingGreenlet: an unloaded lazy relationship, an attribute expired by commit, a deferred column, or an unfetched server default. Plain attribute access cannot await, so SQLAlchemy refuses the IO.

solid answer

~40 s

`AsyncSession` runs the ordinary synchronous ORM inside a greenlet started by `greenlet_spawn()`; when the driver must wait, an internal `await_()` hands its coroutine back to the event loop. A bare `order.items` read happens outside any awaited call, so `await_()` finds no such greenlet and raises `MissingGreenlet`: "greenlet_spawn has not been called; can't call await_() here. Was IO attempted in an unexpected place?" The usual reads that need IO are an unloaded lazy relationship, any attribute expired by `commit()` while `expire_on_commit` is `True`, a deferred column, a collection never assigned on a new object that has been flushed, and a server-generated value the flush did not fetch. A value already loaded, or a many-to-one the identity map can answer, reads without IO and works.

code

python · 45 lines
python
import asyncio

from sqlalchemy import ForeignKey, String
from sqlalchemy.ext.asyncio import async_sessionmaker, create_async_engine
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, relationship


class Base(DeclarativeBase):
    pass


class Order(Base):
    __tablename__ = "orders"
    id: Mapped[int] = mapped_column(primary_key=True)
    status: Mapped[str] = mapped_column(String(20))
    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 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)

    async with Session() as session:
        session.add(Order(status="new", items=[OrderItem(sku="A1")]))
        await session.commit()

    async with Session() as session:
        order = await session.get(Order, 1)
        try:
            order.items  # first access of a lazy relationship, no await
        except Exception as exc:
            print(str(exc).splitlines()[0])
    await engine.dispose()


asyncio.run(main())

go deeper

for a junior

Recall that under AsyncSession reading a relationship that was never loaded raises MissingGreenlet, and that eager loading in the query is the normal way to avoid it.

for a middle

Explain the greenlet bridge in a sentence and list every kind of access that needs IO: lazy relationships, expired and deferred columns, uninitialised collections and unfetched server values.

for a senior

Show you can read the refused SELECT in the message, trace the access to a serializer or repr far from the query, and explain why a many-to-one sometimes works from the identity map.

for a principal

Treat MissingGreenlet as a design signal: data needs should be declared at the query, and a team needs conventions that make a missing load fail in tests rather than intermittently in production.

## What MissingGreenlet is SQLAlchemy's asyncio extension does not contain a second, asynchronous ORM. An `AsyncSession` wraps an ordinary synchronous `Session` and runs each of its operations inside a **greenlet** started by the internal `greenlet_spawn()` function. Deep inside, when the async driver has to wait for the database, SQLAlchemy calls its internal `await_()`, which switches out of that greenlet and hands the driver's coroutine to the event loop. This only works because the outer call (`await session.execute(...)`, `await session.get(...)`, `await session.commit()`) is itself awaited. A plain attribute read such as `order.items` is not awaited. If the ORM decides it must run a SELECT to produce the value, `await_()` finds no SQLAlchemy greenlet around it and raises **`MissingGreenlet`**, a subclass of `InvalidRequestError` with error code `xd2s`: > greenlet_spawn has not been called; can't call await_() here. Was IO attempted in an unexpected place? The refusal is deliberate. An asyncio program is expected to show every point of IO with an `await`; a SELECT hidden behind attribute access would either stall the event loop or break that rule, so SQLAlchemy raises instead of blocking. ## How it surfaces Depending on where in statement execution the driver first awaits, SQLAlchemy's error handling can wrap the exception in `sqlalchemy.exc.StatementError`. With the aiosqlite driver, a refused lazy load arrives that way: the message starts with `(sqlalchemy.exc.MissingGreenlet)`, then prints the SELECT the ORM tried to run, and the original exception sits on `.orig`. That printed SELECT is the quickest clue to which attribute triggered it, because its FROM and WHERE clauses name the table and the key. ## Where implicit IO hides 1. **An unloaded lazy relationship.** The default `lazy="select"` strategy loads `order.items` or `order.customer` on first access. Under asyncio that first access raises unless a loader option such as `selectinload()` already filled it. 2. **An expired attribute.** `commit()` expires every attribute of every object in the session while `expire_on_commit` is `True`, and `async_sessionmaker` leaves it at `True` unless told otherwise. The next read of even a plain column such as `order.status` needs a SELECT. `AsyncSession.expire()` does the same for the attributes it names. 3. **A deferred column.** A column mapped with `mapped_column(deferred=True)`, or deferred by a `defer()` option, loads on first access. 4. **A collection never set on a new object.** If you add `Order(status="new")` without `items=[]` and flush, `order.items` is unloaded and its first read queries the database. 5. **A server-generated value the flush did not fetch.** A `server_default` or `server_onupdate` column that the INSERT or UPDATE did not bring back through RETURNING stays expired until read. | Access | Why it needs IO | Usual remedy | |---|---|---| | unloaded relationship | the lazy loader runs a SELECT | an eager loader option in the query | | attribute after `commit()` | expired by `expire_on_commit` | `expire_on_commit=False` | | deferred column | deferred until access | `undefer()`, or no deferral | | new object's collection | never initialised | assign an empty collection | | server-generated column | not fetched at flush | `eager_defaults`, or `refresh()` | ## What does not raise - Any attribute already present in the object's state, loaded by the query or set by your own code. - A **many-to-one** whose target's primary key is already in the session's identity map: the lazy loader returns that object without SQL. This makes the bug intermittent, passing when an earlier query happened to load the customer and failing when none did. - Anything awaited explicitly: `await order.awaitable_attrs.items` from the `AsyncAttrs` mixin, or `await session.refresh(order, ["items"])`. Writing `await order.items` does not help. Python evaluates `order.items` first, and that access raises before there is anything to await. ## Diagnosing one - Read the SQL in the message: it shows which load was refused. - Find the access in the traceback. A template, a serializer, logging code or a `__repr__` that touches a relationship is a common culprit, because it runs far from the query that should have loaded the data. - Decide whether the value belonged in the original query (add a loader option) or is genuinely occasional (await it explicitly). Catching the exception and retrying is never the fix: the access has to move inside an awaited call, or happen on data that is already loaded.

  • Why does the error sometimes arrive as StatementError rather than MissingGreenlet?
    When the refused await happens while SQLAlchemy is executing a statement, its error handling can wrap the non-driver exception in `sqlalchemy.exc.StatementError`. The message then begins with `(sqlalchemy.exc.MissingGreenlet)`, prints the SQL, and `.orig` holds the original exception. With aiosqlite a lazy load surfaces this way. Matching on the class alone can therefore miss it, but the real answer is to fix the access, not to catch it.
  • Does a many-to-one lazy load always raise under AsyncSession?
    No. If the related object's primary key is already in the session's identity map, the lazy loader returns it without emitting SQL, so `order.customer` works. That makes the failure depend on what an earlier query loaded, which is why it can pass in one request and fail in the next. Declaring the relationship with `lazy="raise"` turns it into a deterministic `InvalidRequestError`.

It is like a cashier who may only use the till while the manager's key is turned. Inside an awaited call the key is turned and the ORM can query; a bare attribute read happens with the key out, so the till refuses instead of waiting.

  • MissingGreenlet means the greenlet package is not installed.
  • Writing await order.items turns the lazy load into something awaitable.
  • Only relationships can raise MissingGreenlet; plain column attributes never do.
  • Catching MissingGreenlet and retrying the access is an acceptable fix.
  • Under asyncio SQLAlchemy runs the lazy load in a thread so it does not block.
open as a page

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?

level: seniorimportance: must knowfreq 48%

basics

~20 s

By 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.

open as a page

Under SQLAlchemy's asyncio extension, how do AsyncAttrs.awaitable_attrs and AsyncSession.refresh() with attribute names load an attribute on demand, and how do they differ?

level: middleimportance: should knowfreq 32%

basics

~20 s

await obj.awaitable_attrs.items runs the attribute's normal loader inside an awaitable, so it loads only what is missing. await session.refresh(obj, ["items"]) expires the named attributes and re-selects them, loading a lazy relationship only when it is named.

open as a page

As lead of a service built on SQLAlchemy's asyncio extension, which loading conventions would you standardise to keep MissingGreenlet out of production, and what does each cost?

level: principalimportance: should knowfreq 25%

basics

~20 s

Typical conventions: expire_on_commit=False, lazy="raise" on relationships, loader options declared per query, WriteOnlyMapped for large collections, and AsyncAttrs or refresh() as audited escape hatches. Each trades convenience or freshness for loads that fail early and visibly.

open as a page

Why do SQLAlchemy's asyncio docs recommend WriteOnlyMapped collections, and how do you read and add to one through an AsyncSession?

level: middleimportance: nice to knowfreq 20%

basics

~10 s

A WriteOnlyMapped collection never loads itself, so it can never cause implicit IO. Read it with await session.scalars(order.status_log.select()); add with order.status_log.add(...), which is queued and inserted at the next flush.

open as a page

With SQLAlchemy's AsyncSession, why can reading a server-generated column right after a flush raise MissingGreenlet, and how does the mapper's eager_defaults setting prevent it?

level: seniorimportance: nice to knowfreq 18%

basics

~20 s

A server-generated value is unknown to Python after INSERT or UPDATE, so the ORM leaves it expired unless the flush fetched it. Under AsyncSession that later read raises. eager_defaults fetches it during the flush, through RETURNING or an extra SELECT.

open as a page