An async SQLAlchemy ingestion worker shares one AsyncSession across tasks started with asyncio.gather and fails with InvalidRequestError; why, and how should it be structured?
answer
- one transaction, one connection
- no internal locking
- the isce error code
- pass the factory, not the session
- scoped registry needs remove()
basics
~20 sAn AsyncSession is one unsynchronised transaction on one connection, so gathered tasks race on its state and SQLAlchemy can raise errors such as "This session is provisioning a new connection; concurrent operations are not permitted". Give each task its own AsyncSession from one async_sessionmaker.
solid answer
~40 s`AsyncSession` is a thin proxy over a `Session`: a mutable object representing one database transaction on one connection per engine, with no synchronisation. When `asyncio.gather()` runs several coroutines against it, one task can start checking out the connection while another starts a statement, and SQLAlchemy's state checks raise `InvalidRequestError` ("This session is provisioning a new connection; concurrent operations are not permitted") or `IllegalStateChangeError`, both carrying the `isce` error code. Concurrent flushes can fail with "Session is already flushing", and the race can also reach the driver as errors on the shared connection. Even when nothing raises, one task's commit commits every other task's pending objects. The fix is to pass the `async_sessionmaker` into each task and open `async with SessionFactory() as session, session.begin():` there. The pool then bounds how many transactions run at once.
code
python · 50 linesimport asyncio
from sqlalchemy import String, func, select
from sqlalchemy.ext.asyncio import (
AsyncSession,
async_sessionmaker,
create_async_engine,
)
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column
class Base(DeclarativeBase):
pass
class Event(Base):
__tablename__ = "event"
id: Mapped[int] = mapped_column(primary_key=True)
source: Mapped[str] = mapped_column(String(50))
engine = create_async_engine("sqlite+aiosqlite:///ingest.db")
SessionFactory = async_sessionmaker(engine, expire_on_commit=False)
# WRONG: every task receives the same AsyncSession
async def write_shared(session: AsyncSession, source: str) -> None:
session.add(Event(source=source))
await session.flush()
# RIGHT: every task opens its own AsyncSession and transaction
async def write_own(source: str) -> None:
async with SessionFactory() as session, session.begin():
session.add(Event(source=source))
async def main() -> None:
async with engine.begin() as conn:
await conn.run_sync(Base.metadata.drop_all)
await conn.run_sync(Base.metadata.create_all)
async with asyncio.TaskGroup() as tg:
for i in range(10):
tg.create_task(write_own(f"sensor-{i}"))
async with SessionFactory() as session:
print(await session.scalar(select(func.count()).select_from(Event)))
await engine.dispose()
asyncio.run(main())go deeper
Remember the rule SQLAlchemy states plainly: one AsyncSession per task, created from an async_sessionmaker, never shared between concurrent coroutines.
Explain why an AsyncSession is one transaction on one connection with no locking, and how session-per-task code passes the factory rather than the session.
Read the isce errors, the already-flushing error and the silent cross-task commit as one diagnosis, then cap the fan-out to what the pool can serve.
Write the worker convention down: factories are injected, sessions never outlive a task, atomic batches are sequential, and the scoped registry is avoided or always removed.
## What an AsyncSession actually is SQLAlchemy's documentation describes both `Session` and `AsyncSession` as **mutable, stateful objects with no built-in synchronisation** that represent a **single, ongoing database transaction**. `AsyncSession` is only a proxy over a `Session`, so every rule of the synchronous object applies. Within a transaction it holds **one connection per bound engine**, and it moves through internal states (beginning, provisioning a connection, flushing, committing, closing) that have to match what that connection is doing. asyncio switches tasks at every `await`. When ten tasks gathered with `asyncio.gather()` share one session, task A can pause halfway through checking out the connection while task B starts a statement on the same session. Nothing in the session serialises them. ## What breaks when tasks share one SQLAlchemy actively detects concurrent calls on one session instead of letting them corrupt its state: | Symptom | When it appears | |---|---| | `InvalidRequestError: This session is provisioning a new connection; concurrent operations are not permitted` | a second task runs a statement while the first is still checking out the connection | | `IllegalStateChangeError: Method 'close()' can't be called here; method '_connection_for_bind()' is already in progress ...` | the session is closed while another task is mid-operation | | `InvalidRequestError: Session is already flushing`, often with an `SAWarning` about `Session.add()` during flush | two tasks flush, or one adds objects while another flushes | | driver-level errors on the connection | the race reaches the driver before SQLAlchemy can detect it | | no error, wrong result | one task's `commit()` flushes and commits the objects every other task has added so far | The first two carry the error code `isce`, whose documentation page names the usual cause: a session shared among concurrent tasks. The last row matters as much as the errors: a shared session also shares one identity map and one transaction boundary, so a rollback in one task discards everyone's work. Whether a given run raises at all depends on timing, which is why the bug often hides in tests against a fast local database and appears under production latency, where every connection checkout and round trip yields to the loop. ## The fix: one AsyncSession per task - Create the engine and an `async_sessionmaker` once, at start-up. - Pass the **factory** to the code that spawns tasks, never a session. - Inside each task, open `async with SessionFactory() as session, session.begin():`, do that task's writes, and let the block commit and close. - Run the tasks under `asyncio.TaskGroup` or `gather()` as before; each now has its own transaction and pooled connection. Each task now commits or rolls back independently. That is the point of running them concurrently: concurrent database work means **concurrent transactions**. A failure in one task rolls back only that task's events, and the caller can decide whether the others should still stand. ## Bounding the fan-out Every running task now holds a pooled connection for as long as its transaction is open. The `AsyncAdaptedQueuePool` defaults to `pool_size=5` and `max_overflow=10`, so with hundreds of gathered tasks most wait for a checkout and, past `pool_timeout`, fail with a pool timeout. Bound the number of tasks in flight to what the pool allows; sizing the pool itself is pool material. ## When one transaction is the requirement If a batch must commit **atomically**, do not fan it out. A single transaction runs on a single connection, whose commands the database processes in order, so concurrency inside it buys nothing. Run the writes sequentially in one session, or send them as one multi-row statement, and commit once. ## async_scoped_session: possible, rarely the answer `async_scoped_session` keeps one `AsyncSession` per scope key, the asyncio counterpart of thread-local `scoped_session`: 1. `scopefunc` is a **required** argument, because asyncio has no thread-local: `async_scoped_session(SessionFactory, scopefunc=current_task)`. 2. `scopefunc` is called every time the proxy is used, so it must be cheap and idempotent. 3. The outermost awaitable must `await Scoped.remove()`; otherwise the task handle and its session stay in the registry, a memory leak. SQLAlchemy's documentation does not recommend the scoped pattern for new code and suggests passing the `AsyncSession` explicitly to the functions that need it. ## Checklist for a concurrent async worker - One engine and one factory per process; one session per task. - No session stored in a module global, a class attribute or a long-lived object shared by tasks. - Tasks in flight capped to the pool's capacity. - A requirement for one atomic transaction means sequential work in one session, not `gather()`.
- When would you use async_scoped_session, and what must you not forget?Only when deep call chains cannot reasonably be given the session as an argument. Build it as `async_scoped_session(SessionFactory, scopefunc=current_task)`; the `scopefunc` is required and runs on every access, so keep it idempotent. The outermost coroutine of each task must `await Scoped.remove()`, or the task handle and its session stay in the registry and leak. For new code, SQLAlchemy recommends passing the `AsyncSession` explicitly.
- Why not protect the shared AsyncSession with an asyncio.Lock instead?A lock serialises every statement, so the tasks gain no database concurrency, and their objects still share one identity map and one transaction: the first commit commits everyone's pending work and a rollback discards all of it. You add synchronisation code to get a slower version of sequential use. A session per task gives real concurrency and independent outcomes for less code.
- The batch must be all-or-nothing; can the worker still use gather()?Not inside one transaction. A transaction lives on one connection, which runs its commands in order, so gathering coroutines over it gains nothing and triggers the concurrency errors. Run the writes sequentially in one `AsyncSession`, or as one multi-row statement, and commit once. Concurrency means separate transactions that succeed or fail independently.
One AsyncSession is like one shopping trolley at a supermarket till. If ten shoppers throw their items into the same trolley, whoever pays first pays for everything in it, and the till refuses two people scanning at once. Each shopper needs a trolley of their own.
- Thinks an AsyncSession is safe to share because asyncio runs one task at a time
- Believes each await session.execute() borrows its own pooled connection
- Treats the isce errors as flaky driver bugs that a retry will fix
- Shares one session across tasks because they only run SELECT statements
- Builds async_scoped_session without a scopefunc, like thread-local scoped_session