PostgreSQL — deadlock detected

Diagnose PostgreSQL deadlocks by reading the server log, identifying conflicting lock order, shortening transactions, and adding bounded retries.

advanceddebuggingpostgresqltransactionslocking

Symptoms

ERROR: deadlock detected
DETAIL: Process 123 waits for ShareLock on transaction 456; blocked by process 789
One transaction is aborted while the other succeeds, often under concurrent writes
The error appears in bursts when two code paths update the same rows in different orders

Possible causes

Two transactions lock the same rows in different orders
A transaction stays open while waiting for user input or an external API
A missing index causes a broad scan that locks more rows than expected
Foreign key checks acquire locks that conflict with application updates
A batch job and an interactive request update overlapping rows

Diagnosis steps

Read the deadlock detail in the PostgreSQL log

tail -n 100 /var/log/postgresql/postgresql.log | grep -A8 'deadlock detected'

Interpretation

The detail names the processes, the lock type, and the statements. The two statements show the conflicting access order.

Inspect active transactions and waits

SELECT pid, state, wait_event_type, wait_event, query FROM pg_stat_activity WHERE state <> 'idle' ORDER BY query_start;

Interpretation

Long-running or idle-in-transaction sessions hold locks and increase the conflict window.

Reproduce with two sessions

BEGIN; UPDATE accounts SET balance = balance - 10 WHERE id = 1; UPDATE accounts SET balance = balance + 10 WHERE id = 2; COMMIT;

Interpretation

Running the statements in opposite order in a second session produces a deadlock and confirms the lock order hypothesis.

Root cause

PostgreSQL detects a cycle in the lock wait graph and aborts one transaction. The cycle exists because concurrent transactions acquire the same locks in different orders or hold locks for too long.

Fix

Acquire locks in a deterministic order (for example, sort ids before updating), shorten transactions, and move external calls outside the transaction. Add a bounded retry for SQLSTATE 40P01 with jitter, because a deadlock is a transient conflict.

Risk note

Retrying an entire transaction can duplicate external side effects if the transaction already performed them. Keep transactions free of non-database side effects, or make those effects idempotent.

Verify the fix

Re-run the two-session reproduction and confirm the deadlock disappears when both sessions use the same lock order. Add a concurrency test and monitor deadlock counts in pg_stat_database.

A deadlock is a lock-order bug

PostgreSQL does not prevent deadlocks; it detects a cycle and aborts one transaction. The error is a symptom of two transactions taking locks in opposite orders, or holding locks long enough for a cycle to form. The server log contains the exact statements and processes involved. Start there instead of guessing from the application stack.

ERROR: deadlock detected
DETAIL: Process 120 waits for ShareLock on transaction 121;
        blocked by process 121.

Make the order deterministic

If a transfer updates accounts 1 and 2, another transfer updates 2 and 1. Sorting the ids before locking removes the cycle:

BEGIN;
SELECT id, balance FROM accounts
 WHERE id IN (1, 2)
 ORDER BY id
 FOR UPDATE;
UPDATE accounts SET balance = balance - 10 WHERE id = 1;
UPDATE accounts SET balance = balance + 10 WHERE id = 2;
COMMIT;

The ORDER BY id ... FOR UPDATE acquires row locks in a stable order. For batch jobs, process rows in primary-key order and commit in small batches. Do not hold a transaction open while calling a payment provider or waiting for a human; that turns a millisecond lock into a multi-second conflict window.

Retry with a bound

A deadlock is transient, so a bounded retry is appropriate. Catch SQLSTATE 40P01, roll back, wait with jitter, and retry the whole transaction. Limit attempts and surface the failure rather than looping forever. Ensure the transaction has no external side effects that would duplicate on retry; if it does, move them to an outbox or make them idempotent.

Watch the trend

Track deadlocks per minute and the top statements involved. A sudden increase often follows a new query plan, a missing index, or a changed lock order in one code path. pg_stat_database.deadlocks gives a counter; the server log gives the details. Fix the order, not just the timeout.