Why PostgreSQL detects deadlocks only after a timeout—and how blind retries backfire

When two or more transactions each hold a lock that the other needs, a circular wait forms—unless an outside arbiter intervenes. PostgreSQL plays that role by building a wait-for graph from current lock states, spotting cycles, and aborting one transaction to break the deadlock. Developers don’t usually write “wrong” code to cause this; the problem appears when concurrent code paths touch the same resources in different orders under high load. In a single-user dev environment the race never surfaces, but on a busy production system users suddenly receive ERROR: deadlock detected (SQLSTATE 40P01), one transaction rolls back, and the endpoint returns a 500 error. The most damaging consequence isn’t the deadlock itself—it’s retry logic that keeps replaying the same flawed lock order, recreating the cycle with every attempt. Under rising traffic the deadlock counter climbs linearly, PostgreSQL logs fill up, and CPU cycles are burned running cycle-detection algorithms instead of serving real queries.

How PostgreSQL’s deadlock detector works

PostgreSQL doesn’t scan for deadlocks after every lock wait. Instead, when a backend must wait for a lock it registers with the lock manager and goes to sleep; the backend wakes up only after the wait exceeds `deadlock_timeout` (one second by default, settable via deadlock_timeout in runtime-config-locks). This design is laid out in the official PostgreSQL documentation on Lock Management. In normal workloads most lock waits finish in milliseconds—quick enough that running a full graph algorithm on every lock acquisition would waste CPU. The trade-off is latency: if a real cycle exists, every transaction in that cycle sleeps at least deadlock_timeout before it can be released.

When the timer fires, the backend constructs the wait-for graph by asking the lock manager for every process currently waiting. Each edge represents “process A is waiting for a lock held by process B.” PostgreSQL’s algorithm in deadlock.c first tries to rearrange soft-conflicts—cases where two processes are waiting but neither strictly holds the contested lock—into an order that would let both proceed without killing either. Only when no reordering is possible does it declare a true deadlock. Once a cycle is confirmed, the backend running the detection is the one that gets aborted, throwing ERROR: deadlock detected (SQLSTATE 40P01), rolling back the transaction, releasing the locks, and waking the other processes in the cycle so they can continue.

A typical PostgreSQL log line when log_lock_waits = on looks like this:

LOG: process 12345 still waiting for ShareLock on transaction 9876 after 1000.123 ms
DETAIL: Process holding the lock: 12399. Wait queue: 12345.
CONTEXT: while updating tuple (5,12) in relation "accounts"
STATEMENT: UPDATE accounts SET balance = balance + 100 WHERE id = 99
ERROR: deadlock detected
DETAIL: Process 12345 waits for ShareLock on transaction 9876; blocked by process 12399.
        Process 12399 waits for ShareLock on transaction 9877; blocked by process 12345.
HINT: See server log for query details.

The two lines starting with Process N waits for …; blocked by process M describe the two-node cycle. Longer cycles add more lines. In pg_stat_database, the deadlocks counter increments each time a cycle is detected; PostgreSQL offers no view to list historical deadlocks—if you need a record you must scrape the logs.

Common production pitfalls

Pitfall #1: infinite or unguided retries that don’t fix the root cause. Teams that encounter 40P01 for the first time often wrap suspect code in a simple retry:

# Naïve retry without fixing lock order
def transfer(src_id, dst_id, amount):
    for attempt in range(10):
        try:
            with conn.transaction():
                conn.execute(
                    "UPDATE accounts SET balance = balance - %s WHERE id = %s",
                    (amount, src_id)
                )
                conn.execute(
                    "UPDATE accounts SET balance = balance + %s WHERE id = %s",
                    (amount, dst_id)
                )
            return
        except DeadlockDetected:
            time.sleep(0.01)  # back-off too short, cycle remains
    raise

Under load the retry just replays the same deterministic lock order (UPDATE from_id, then UPDATE to_id), recreating the cycle on every attempt. Each retry adds roughly one second of latency, turning a millisecond operation into seconds. Worse, if the retry queue lacks exponential backoff and limits, hundreds of concurrent retries can swamp the database with graph-scan work even as they keep failing.

The fix is to normalize lock acquisition order across all code paths. Any routine touching multiple rows or tables should sort keys first—by id, table name, or another stable rule—so that every path acquires locks in the same sequence:

# Lock rows by id in ascending order, retry only for residual races
def transfer(src_id, dst_id, amount):
    lo, hi = sorted([src_id, dst_id])
    for attempt in range(3):
        try:
            with conn.transaction():
                # Acquire both rows in id order first
                conn.execute(
                    "SELECT id FROM accounts WHERE id IN (%s,%s) ORDER BY id FOR UPDATE",
                    (lo, hi),
                )
                conn.execute(
                    "UPDATE accounts SET balance = balance - %s WHERE id = %s",
                    (amount, src_id)
                )
                conn.execute(
                    "UPDATE accounts SET balance = balance + %s WHERE id = %s",
                    (amount, dst_id)
                )
            return
        except DeadlockDetected:
            time.sleep(0.05 * (2 ** attempt))  # exponential backoff
    raise

Retries are still necessary because some deadlocks (foreign-key locks, multi-table workflows) cannot be eliminated by sorting, but they must be bounded, use exponential backoff, and trigger alerts when thresholds are exceeded so engineers know the underlying flaw remains.

Pitfall #2: deadlocks from implicit foreign-key locks. When an INSERT or UPDATE adds a row that references a parent via foreign key, PostgreSQL automatically takes a FOR KEY SHARE lock on the parent row to prevent it from being deleted or key-changed while the child exists. Two transactions inserting children that point to the same parent do not deadlock because both hold the same KEY SHARE mode. The problem arises when one transaction first inserts a child (acquiring KEY SHARE on the parent) and then later updates the parent (needing a stronger lock), while another transaction does the reverse—inserts first then updates the parent. The two paths now escalate to conflicting lock modes on the same parent row and a cycle appears.

Normalizing lock order won’t help if the business logic itself forces escalation mid-transaction, so teams should either refactor the workflow to acquire parent locks earlier or introduce application-level advisory locks (pg_advisory_lock) to serialize the conflicting operations before they reach PostgreSQL.

Moving forward

Deadlocks aren’t bugs in PostgreSQL—they’re symptoms of non-deterministic lock acquisition order under concurrency. The database’s timeout-based detection keeps overhead low during normal operation, but it can’t compensate for flawed application patterns. Standardizing lock order, bounding retries with exponential backoff, and alerting on repeated failures are the surest ways to keep cycles from forming—and keep your database focused on real work instead of graph algorithms.