Sharing One Async Session
The following async handler crashes under load with
InvalidRequestError: This session is provisioning a new connection; concurrent operations are not permitted:
a, b, c = await asyncio.gather(
get_top_subjects(db),
get_top_questions(db),
get_categories(db),
)
Explain the root cause in terms of concurrency, and give two different correct fixes.
Root cause (a concurrency bug, not a parallelism bug):
A SQLAlchemy AsyncSession wraps a single logical database connection and
is not safe for concurrent use. asyncio.gather schedules all three
coroutines before any of them finishes. While the first coroutine is
suspended mid-await (acquiring the connection), the event loop runs the
second, which calls .execute() on the same, half-initialised session.
The session detects an operation while it is still "provisioning a new
connection" and raises. Nothing ran on two cores — interleaving alone exposed
the in-between state.
Fix 1 — run sequentially:
a = await get_top_subjects(db)
b = await get_top_questions(db)
c = await get_categories(db)
Each await completes before the next starts, so the session is only ever
used by one operation at a time. The event loop still serves other requests
(each with its own session) while these await I/O.
Fix 2 — one session per coroutine:
async with async_session_factory() as db1, async_session_factory() as db2:
a, b = await asyncio.gather(query(db1), query(db2))
Two independent sessions use two connections from the pool, so the coroutines can safely overlap.
Rule: one AsyncSession = one connection = strictly sequential use.