Handling ConnectionDoesNotExistError and server disconnects

SQLAlchemy already discards and invalidates connections it recognises as disconnected, so the work left to you is retrying units of work — a callable that opens its own session per attempt — with pool_pre_ping=True and a pool_recycle below the shortest idle timeout on the network path. This guide belongs to dialect-specific gotchas and driver quirks.

Quick Answer

A retry that reuses the session that just failed cannot work: its transaction is rolled back and its connection is gone.

Before — retrying a statement on the broken session:

from sqlalchemy import select
from sqlalchemy.exc import OperationalError

from shop.models import Order


async def load_order(session, order_id: int) -> Order | None:
    for _ in range(3):
        try:
            return await session.scalar(select(Order).where(Order.id == order_id))
        except OperationalError:
            continue
# sqlalchemy.exc.PendingRollbackError: This Session's transaction has been rolled back
# due to a previous exception during flush.

After — retrying a unit of work that opens its own session:

import asyncio
import logging
from collections.abc import Awaitable, Callable
from typing import TypeVar

from sqlalchemy import select
from sqlalchemy.exc import DBAPIError, OperationalError

from shop.db import Session
from shop.models import Order

log = logging.getLogger("db.retry")
T = TypeVar("T")


def _is_disconnect(exc: DBAPIError) -> bool:
    return bool(getattr(exc, "connection_invalidated", False))


async def retrying(unit: Callable[[], Awaitable[T]], attempts: int = 3) -> T:
    for attempt in range(1, attempts + 1):
        try:
            return await unit()
        except OperationalError as exc:
            if not _is_disconnect(exc) or attempt == attempts:
                raise
            delay = 0.2 * (2 ** (attempt - 1))
            log.warning("reconnecting after a disconnect (attempt %d)", attempt)
            await asyncio.sleep(delay)
    raise AssertionError("unreachable")


async def load_order(order_id: int) -> Order | None:
    async def unit() -> Order | None:
        async with Session() as session:              # a fresh session per attempt
            return await session.scalar(select(Order).where(Order.id == order_id))

    return await retrying(unit)

connection_invalidated is the flag SQLAlchemy sets when it recognised the error as a disconnect and discarded the connection — which is exactly the case where a retry has a fresh connection to work with.

Execution Context & Async Workflow Integration

Connections die for ordinary reasons: a load balancer times out an idle TCP session, a NAT gateway forgets a mapping, PostgreSQL is restarted, a failover promotes a replica, or idle_in_transaction_session_timeout terminates a backend. asyncpg reports them as a small family of errors, and the one that names the situation most precisely is ConnectionDoesNotExistError — "connection was closed in the middle of operation", meaning a statement was in flight when the socket went away.

SQLAlchemy handles the pool side of this automatically. The dialect classifies each error, and for a disconnect the pool discards that connection and invalidates every connection created before that moment. The next checkout therefore opens a new connection; nothing has to be configured for that to happen, and engine.dispose() in an error handler is unnecessary and harmful — it closes connections other tasks are using.

What SQLAlchemy cannot decide is whether to retry, because that depends on what the work does. Three cases:

A read has no effect, so retrying is always safe.

A write that failed before the commit was sent never happened: the transaction was rolled back by the server when the connection died. Retrying is safe.

A write interrupted between sending the commit and receiving its acknowledgement has an unknown outcome. The transaction may have committed. Retrying can therefore duplicate the effect — a second charge, a second shipment — and the only safe versions are an idempotency key the operation checks first, or a check-then-act that looks for the effect before repeating it.

That third case is rare and real, and it is why blanket retry decorators around anything that writes are a bad idea. Make the retry explicit at the call site, where the caller knows whether the operation is idempotent, as handling serialization failures with retry logic argues for the analogous serialisation case.

Under async there is one further consideration: task cancellation looks similar and is not. A task cancelled mid-query raises CancelledError, and SQLAlchemy invalidates the connection because it was interrupted mid-protocol. That is not a disconnect and must not be retried — the caller asked to stop. Catching OperationalError rather than a bare Exception keeps the two apart.

Resolving Warnings, Errors & Common Mistakes

Start-up is the case worth building explicitly, because the alternative is a crash loop. A service restarted at the same moment as its database should wait rather than exit:

import asyncio
import logging

from sqlalchemy import text
from sqlalchemy.exc import DBAPIError, OperationalError

log = logging.getLogger("startup")


async def wait_for_database(engine, timeout: float = 60.0, interval: float = 1.0) -> None:
    deadline = asyncio.get_running_loop().time() + timeout
    attempt = 0
    while True:
        attempt += 1
        try:
            async with engine.connect() as conn:
                await conn.execute(text("SELECT 1"))
            log.info("database reachable after %d attempt(s)", attempt)
            return
        except (OSError, OperationalError, DBAPIError) as exc:
            if asyncio.get_running_loop().time() >= deadline:
                raise RuntimeError(f"database unreachable after {timeout:.0f}s") from exc
            await asyncio.sleep(interval)

Calling that at the start of the lifespan means a deploy during a database restart produces one delayed start rather than dozens of failed pods. The bound matters: waiting forever hides a genuine misconfiguration behind a container that never becomes ready.

The InterfaceError: connection is closed row is the one that is usually an application bug rather than infrastructure. It means code held a session or connection past its lifetime — a background task using a request's session, or a session stored on a long-lived object. Those are the leaks described in fixing garbage collector non-checked-in connection warnings, and no amount of retrying fixes them.

Advanced: Making Interrupted Writes Safe to Retry

The one genuinely hard case is a write whose outcome is unknown. Two designs make it safe, and both are worth having in place before an incident rather than after.

An idempotency key carried by the operation, and enforced by a unique constraint. The retry presents the same key, and the second attempt is rejected or recognised rather than duplicated:

from sqlalchemy import select
from sqlalchemy.dialects.postgresql import insert
from sqlalchemy.exc import IntegrityError

from shop.db import Session
from shop.models import Payment


async def capture_payment(idempotency_key: str, order_id: int, amount_cents: int) -> Payment:
    async def unit() -> Payment:
        async with Session.begin() as session:
            stmt = (
                insert(Payment)
                .values(
                    idempotency_key=idempotency_key,
                    order_id=order_id,
                    amount_cents=amount_cents,
                    status="captured",
                )
                .on_conflict_do_nothing(index_elements=[Payment.idempotency_key])
                .returning(Payment.id)
            )
            inserted = await session.scalar(stmt)
            if inserted is None:
                # A previous attempt already committed this payment.
                return await session.scalar(
                    select(Payment).where(Payment.idempotency_key == idempotency_key)
                )
            return await session.get(Payment, inserted)

    return await retrying(unit)

ON CONFLICT DO NOTHING plus a follow-up read turns "did my first attempt commit?" into a question the database answers, which is the only reliable place to ask it. The upsert mechanics are covered in writing Postgres ON CONFLICT DO UPDATE upserts.

A check-then-act where no key is available: look for the effect before repeating it. This is weaker — the check and the act are not atomic — so it suits operations whose duplication is undesirable rather than catastrophic.

Two things not to do. Do not call engine.dispose() from an error handler: the pool has already invalidated what it needed to, and disposing closes connections that other tasks are using. And do not retry indefinitely: a bounded number of attempts with exponential backoff turns a brief interruption into a slight delay, while an unbounded loop turns a sustained outage into a thundering herd against a database that is trying to recover.

Finally, make retries visible. A counter incremented per retry, labelled by error code, is what distinguishes "one failover last Tuesday" from "this network path drops a connection every few minutes" — and the second is an infrastructure problem that retries are hiding rather than solving. The instrumentation belongs with the rest of the pool metrics in instrumenting and observing async queries.

Preventing Disconnects Rather Than Recovering From Them

Most disconnects in a steady-state system are not the database's doing. They come from something on the network path deciding a quiet connection is a dead one, and that is preventable rather than merely recoverable.

Recycle below the shortest idle timeout on the path. The timeouts that matter are rarely PostgreSQL's: an AWS network load balancer defaults to 350 seconds of idle, a NAT gateway to a few minutes, and a service mesh sidecar to whatever it was configured with. pool_recycle must be lower than the smallest of them, or connections will be dropped by something in the middle and discovered by the next query. Three hundred seconds is a safe general choice; the right answer is "below the smallest idle timeout you can find".

Keep the connection from looking idle. PostgreSQL can send TCP keepalives on its own connections, and the settings are per-session parameters, so they can be configured from the client:

from sqlalchemy.ext.asyncio import create_async_engine

engine = create_async_engine(
    URL,
    pool_recycle=300,
    pool_pre_ping=True,
    connect_args={
        "server_settings": {
            "tcp_keepalives_idle": "60",      # start probing after 60s idle
            "tcp_keepalives_interval": "10",
            "tcp_keepalives_count": "6",
        }
    },
)

Keepalives make the connection visibly alive to intermediaries, which prevents the idle-timeout class of drop entirely rather than detecting it afterwards.

Do not hold connections while idle. A session kept open across an external HTTP call holds its connection for the duration, which is both a pool problem and an exposure to idle timeouts. Short sessions — open, work, close — avoid it, and the practice is the same one that keeps the pool healthy in handling connection leaks and pool exhaustion.

Bound transactions server-side. idle_in_transaction_session_timeout terminates a session that is holding a transaction open without working. That produces a disconnect, deliberately — and it is much better than the alternative, where locks and dead rows accumulate behind a session nobody notices.

With those in place, disconnects become rare enough that each one is worth looking at: a burst means a failover or a restart, and a steady trickle means something on the path is still dropping connections and the recycle setting has not found it yet.

Frequently Asked Questions

Related

• Dialect-Specific Gotchas and Driver Quirks — The parent guide: behaviour that differs by driver and platform.
• Handling Aurora failover and read-only transaction errors — The disconnect that does not look like one.
• Configuring pool_pre_ping to handle stale connections — Catching dead connections at checkout.
• Handling serialization failures with retry logic — The same retry shape for a different error class.