Detecting idle-in-transaction sessions from async code

An await on anything other than the database inside a transaction leaves the session idle in transaction — holding row locks, a snapshot and a pooled connection while doing nothing — so keep transactions around the database work, set idle_in_transaction_session_timeout as a backstop, and watch pg_stat_activity for the state. This guide belongs to handling connection leaks and pool exhaustion.

Quick Answer

Under async it is easy to await something slow in the middle of a transaction, and the transaction stays open across it.

Before — an HTTP call inside the transaction:

from sqlalchemy.ext.asyncio import AsyncSession

from shop.models import Order


async def confirm_order(session: AsyncSession, order_id: int) -> None:
    async with session.begin():                 # BEGIN
        order = await session.get(Order, order_id)
        order.status = "confirmed"
        await session.flush()                   # row locks taken here
        await payments.capture(order.id, order.total_cents)   # 200 ms – 5 s
        order.payment_reference = "captured"
    # COMMIT — locks held for the whole external call

After — the transaction brackets only the database work:

from sqlalchemy import update
from sqlalchemy.ext.asyncio import async_sessionmaker

from shop.models import Order


async def confirm_order(Session: async_sessionmaker, order_id: int) -> None:
    async with Session.begin() as session:      # transaction 1: short
        order = await session.get(Order, order_id)
        if order.status != "pending":
            return
        order.status = "confirming"
        total = order.total_cents

    reference = await payments.capture(order_id, total)       # no transaction open

    async with Session.begin() as session:      # transaction 2: short
        await session.execute(
            update(Order)
            .where(Order.id == order_id, Order.status == "confirming")
            .values(status="confirmed", payment_reference=reference)
        )

The intermediate confirming state is what makes the split safe: it records that a capture was started, so a crash between the two transactions leaves a row that a reconciliation job can resolve rather than an order that is silently unpaid.

Execution Context & Async Workflow Integration

PostgreSQL reports a session's state in pg_stat_activity. active means a statement is running. idle means a connection is held with no transaction open — normal for a pooled connection. idle in transaction means a transaction is open and nothing is running, and that state has three costs.

It holds row locks taken by any write in the transaction, so other writers to those rows wait. It holds a snapshot, which prevents VACUUM from removing rows that any transaction could still see — including rows deleted by entirely unrelated transactions, so one long-held snapshot bloats tables across the database. And it holds a pooled connection, so the pool is consumed by sessions doing nothing.

SQLAlchemy opens transactions lazily but unavoidably: the first statement on a session begins one, and it stays open until commit(), rollback() or close(). That applies to reads as well as writes — a read-only transaction still pins a snapshot, which is why a session left open for the length of a request is a problem even if it only selected.

Under async the pattern is easy to write because awaits are cheap-looking. The four things most often awaited inside a transaction are an HTTP call, a message publish, a cache round trip, and asyncio.sleep in a retry loop. All of them turn a millisecond transaction into a multi-second one.

The fix is structural: make the transaction as small as the database work, and let external effects happen outside it. Where the two must appear atomic, they cannot be — an HTTP call has no rollback — so the design needs either an intermediate state and reconciliation, as above, or an outbox table so the effect is recorded transactionally and delivered afterwards, the pattern in routing models to multiple databases with session binds.

Request-scoped sessions deserve a note, because they encourage exactly this shape. A FastAPI dependency that yields a session for the whole request means every await in the handler happens with a session available, and after the first statement, inside a transaction. That is convenient and fine for handlers that only talk to the database; for handlers that also call other services, committing before the external call — or taking a session only where it is needed — is what keeps the transaction short.

Resolving Warnings, Errors & Common Mistakes

The monitoring query is the starting point for any of these, and it is worth having as an endpoint or a dashboard rather than something typed during an incident:

from sqlalchemy import text

IDLE_IN_TRANSACTION = text("""
    SELECT pid,
           application_name,
           state,
           now() - xact_start   AS transaction_age,
           now() - state_change AS in_state_for,
           wait_event_type, wait_event,
           left(query, 200)     AS last_query
    FROM pg_stat_activity
    WHERE datname = current_database()
      AND state IN ('idle in transaction', 'idle in transaction (aborted)')
      AND now() - state_change > interval '1 second'
    ORDER BY in_state_for DESC
""")


async def idle_in_transaction(session) -> list[dict]:
    rows = await session.execute(IDLE_IN_TRANSACTION)
    return [dict(row._mapping) for row in rows]

last_query is the statement that ran most recently, not the operation that is slow — the slow part is the Python awaiting something else — so it points at where in the transaction the code is, which is usually enough to identify the handler. application_name narrows it to a service, which is why configuring connect_args and server_settings for asyncpg recommends setting it per role.

The server-side timeout is the backstop that makes all of this survivable:

engine = create_async_engine(
    URL,
    connect_args={"server_settings": {
        "idle_in_transaction_session_timeout": "30s",
        "application_name": "orders-api",
    }},
)

Thirty seconds is generous for a request path and aggressive for a batch job, which is an argument for setting it per role. When it fires, the application sees a disconnect — handled by the machinery in handling ConnectionDoesNotExistError and server disconnects — which is a much better outcome than an unbounded hold.

Advanced: Measuring Transaction Duration From the Application

pg_stat_activity shows the current state; it cannot tell you which endpoint produces long transactions or how often. Session events can, and the instrumentation is small enough to leave on.

import logging
import time

from sqlalchemy import event
from sqlalchemy.orm import Session

log = logging.getLogger("db.transactions")
SLOW_TRANSACTION_S = 1.0


@event.listens_for(Session, "after_begin")
def _transaction_started(session, transaction, connection) -> None:
    session.info["txn_started"] = time.perf_counter()
    session.info["txn_statements"] = 0


@event.listens_for(Session, "do_orm_execute")
def _count_statement(orm_execute_state) -> None:
    info = orm_execute_state.session.info
    info["txn_statements"] = info.get("txn_statements", 0) + 1


def _finish(session, outcome: str) -> None:
    started = session.info.pop("txn_started", None)
    statements = session.info.pop("txn_statements", 0)
    if started is None:
        return
    elapsed = time.perf_counter() - started
    if elapsed >= SLOW_TRANSACTION_S:
        log.warning(
            "long transaction",
            extra={"seconds": round(elapsed, 3), "statements": statements,
                   "outcome": outcome},
            stack_info=True,
        )


@event.listens_for(Session, "after_commit")
def _committed(session) -> None:
    _finish(session, "commit")


@event.listens_for(Session, "after_rollback")
def _rolled_back(session) -> None:
    _finish(session, "rollback")

The two numbers together are what make it diagnostic. A transaction lasting two seconds with forty statements is slow database work — an N+1, or a missing index. A transaction lasting two seconds with two statements spent its time awaiting something else, which is the case this guide is about. stack_info=True captures where the commit happened, which is usually enough to find the handler.

Recording it as a histogram rather than only logging outliers gives the distribution, which is what tells you whether the problem is one endpoint or everywhere. Pairing it with the per-request query counter from counting queries per request to catch N+1 regressions covers both failure modes with one dashboard.

For a stronger guard, a test-suite assertion can fail a test whose transaction spans a mocked external call. Because the external calls are mocked, the transaction is fast — so the check has to be structural rather than time-based: assert that no transaction is open while the HTTP client is invoked:

import pytest


@pytest.fixture
def forbid_open_transaction(session):
    def check(*_args, **_kwargs):
        assert not session.in_transaction(), (
            "an external call was made inside a database transaction"
        )
    return check

Wiring that as a side effect on the mocked client turns the rule into something CI enforces, rather than a convention that erodes.

Setting Transaction Boundaries Deliberately

The durable fix is a convention about where transactions begin and end, applied consistently enough that nobody has to think about it per handler.

One transaction per unit of work, opened explicitly. async with Session.begin() makes the boundary visible and commits at the end, which is clearer than a session that autobegins somewhere and is committed later by someone else:

async def place_order(Session, payload: dict) -> int:
    async with Session.begin() as session:      # the boundary is one line
        order = Order(**payload)
        session.add(order)
        await session.flush()
        return order.id

No non-database awaits inside that block. HTTP calls, message publishes, cache writes and sleeps go outside. When ordering matters, an intermediate state or an outbox row carries the intent across the boundary.

Read-only work closes promptly. A handler that reads and then spends time rendering should let the session close first — a function that takes the session, reads, and returns plain data keeps the transaction to the read.

Long jobs commit per batch. A batch loop with a transaction per iteration never holds a snapshot for long, which is the reasoning in processing large tables in batches with partitions.

Streaming is the documented exception. A server-side cursor must stay inside its transaction, so a long export legitimately holds one. That is a reason to run exports on a separate engine with its own settings, as streaming query results as CSV from FastAPI describes, rather than a reason to relax the rule elsewhere.

With those in place, the remaining idle-in-transaction sessions are the interesting ones: a genuine deadlock, an external call nobody realised was there, or a job whose batch size is too large. And because the server-side timeout bounds them all, finding out about them is a log line rather than an outage — which is the difference between a system with this problem and a system that had it once.

Frequently Asked Questions

Related

• Handling Connection Leaks and Pool Exhaustion — The parent guide: leaks, timeouts and pool sizing.
• Debugging QueuePool limit reached timeouts — The symptom idle transactions usually produce first.
• Fixing garbage collector non-checked-in connection warnings — The related failure where nothing closes the session at all.
• Configuring connect_args and server_settings for asyncpg — Where the server-side timeout is configured.