Postgres "deadlock detected" or "serialization failure"? Retry the transaction, not the statement

Under concurrency, Postgres throws two errors that look scary but are routine: ERROR: deadlock detected (SQLSTATE 40P01) and could not serialize access (40001). Both are transient and retryable — the database is telling you to try again. The catch is that most retry code does it wrong, and doing it wrong corrupts data or loops forever.

Why they happen

• Deadlock (40P01): two transactions each hold a lock the other needs. Postgres detects the cycle, picks a victim, and aborts it — so one of them fails so the other can proceed.
• Serialization failure (40001): under SERIALIZABLE (or REPEATABLE READ), two concurrent transactions can't both commit without violating serializability, so one is aborted.

Both are the database doing its job. Neither is a bug in your query — they're a signal that two things collided, and one should retry.

The mistake almost everyone makes

The correct retry policy

for attempt in range(max_retries):
    try:
        with conn.transaction():
            # ALL the logic that decides which SQL to run — re-run it whole
            run_transaction()
        break
    except (SerializationFailure, DeadlockDetected):   # 40001 / 40P01
        conn.rollback()
        time.sleep(backoff * 2**attempt + random.uniform(0, jitter))
    # any OTHER error aborts immediately — do not retry

1. Only retry 40001 and 40P01 (and sometimes 23505 unique_violation). Every other error aborts immediately — retrying a logic error just repeats it.
2. Make the transaction idempotent. The victim may have fired triggers or written to external systems before the abort; the retry may run more than once.
3. Roll back (or exit the transaction context) BEFORE sleeping. Sleeping while the transaction is still active holds locks and keeps the connection checked out doing nothing.
4. Cap the retries + use jitter. Log each retry — a transaction that deadlocks repeatedly is a lock-ordering problem to fix, not to retry harder.

Prevent them (and the pool-exhaustion cousin)

• Consistent lock ordering. Access tables/rows in the same order across all transactions (e.g. alphabetical) so cycles can't form.
• Short transactions. Keep transactions focused and quick to reduce the window for contention.
• Pool timeouts are a different failure. A connection-pool timeout (or PgBouncer pool exhausted) is a leaked-connection or under-sized-pool problem, not a deadlock — don't retry it as one; fix the leak or size the pool.

For agents touching a database

An agent that retries only the failed statement, or retries a non-retryable error, corrupts state or loops. diagnose_infra_error returns the database family and the correct handling per SQLSTATE — which codes to retry whole, which to never retry, and when a pool timeout is the real issue:

diagnose_infra_error({error: "deadlock detected 40P01"})
// → { family: "database",
//      fix: "retry the WHOLE transaction (idempotent) with backoff+jitter; fix lock ordering; don't retry the statement alone" }