No slow SQL, yet the connection pool is exhausted

Related case: Smart ledger and order management. This article draws on local read-session changes. Examples omit business identity fields and external endpoints; local implementation does not establish production recovery.

Waiting while holding a connection

An endpoint may execute a fast identity query, then spend seconds waiting on an external service. If its database transaction remains open, the checked-out connection may be held through that wait.

Async scheduling lets other coroutines run. It does not automatically release connections, transactions or locks owned by the waiting coroutine.

A Session object is not a checked-out connection

Creating a Session typically does not acquire a connection immediately. Database work and transaction scope determine when it is borrowed and returned. See SQLAlchemy connection pooling for pooling and return-time reset behavior.

Observe checkout/checkin, transaction boundaries and external-call intervals. Session counts and SQL duration alone do not reveal the complete lifetime.

Scope independent reads narrowly

The project uses a separate identity-reading session, extracts plain fields, closes that session, then waits for external business data.

async def resolve_resource(
    session_factory, read_identity, fetch_resource
):
    # read_identity must return a snapshot of plain fields.
    async with session_factory() as identity_session:
        identity = await read_identity(identity_session)

    # The independent read session is closed before external waiting.
    return await fetch_resource(identity)

The injected read_identity must return a plain snapshot. Returning an ORM object with unloaded attributes can trigger failures when it is later accessed outside the session.

The independent session can use the same pool. The improvement comes from a shorter, clearer lifetime, not an extra pool.

Do not commit another layer’s transaction

A caller’s session may contain pending order changes. Committing or rolling it back inside an identity helper would silently take ownership of that business decision.

Split only reads that can safely use an independent snapshot. If a later write depends on a condition remaining true, revalidate it in the write transaction. Earlier permission or identity data is not an eternal authorization guarantee.

Also use a separate AsyncSession per concurrent task, following SQLAlchemy’s concurrency guidance.

Test the waiting interval

Pause the external dependency after the identity read and observe whether the connection has already returned. Then exercise success, failure and cancellation. Verify that the helper leaves any caller-owned pending writes untouched.

Mocks can check control flow; actual pool occupancy and transactions require database integration tests.

For scale intuition, 20 requests per second holding connections for two seconds could imply roughly 40 concurrent connections in steady state. This is an illustrative estimate, not a benchmark, and ignores bursts and tails.

Before enlarging a pool, identify whether connections are executing SQL, waiting on locks or simply waiting on external work. This article’s intervention is specifically to move unrelated waiting outside independent read-session scope.