PostgreSQL — ERROR: deadlock detected

What this error actually means

PostgreSQL found a cycle in the lock wait graph and broke it by cancelling one transaction. The victim gets this error and is rolled back; the other transaction proceeds. This is the database protecting itself, and unlike an application-level deadlock it resolves automatically after `deadlock_timeout` (one second by default) — so the symptom is periodic failed transactions rather than a permanent hang.

Read the `DETAIL` as a cycle: each process is waiting for a transaction held by the other. The `CONTEXT` line is the most useful part, because it names the relation and the specific tuple being updated when the cycle closed. Two processes updating the same two rows in opposite orders is the whole story in the vast majority of cases.

The subtlety that makes this hard to spot in review: you rarely write the ordering explicitly. `UPDATE accounts SET ... WHERE id IN (7, 3)` locks rows in whatever order the plan produces them, and a concurrent statement with the ids reversed can produce the mirrored order. Nothing in either statement looks wrong.

How to diagnose it

ALTER SYSTEM SET log_lock_waits = on;
ALTER SYSTEM SET deadlock_timeout = '1s';
SELECT pg_reload_conf();

SELECT deadlocks, xact_commit, xact_rollback FROM pg_stat_database WHERE datname = current_database();

The fix

Impose a deterministic lock order and apply it everywhere. Sort the ids before locking and take the rows with `SELECT ... FOR UPDATE` in that sorted order at the start of the transaction. Once every transaction agrees on one total order, a cycle is impossible by construction — this is the fix that removes the failure rather than tolerating it.

Lock everything up front rather than escalating mid-transaction. Read-then-update leaves a window for interleaving; acquiring all the row locks you will need, in order, as the first act of the transaction closes it.

Shrink the transaction so fewer rows are held at once. Batch loaders that update 10,000 rows in one transaction deadlock far more than ones that commit in ordered chunks of a few hundred — smaller batches mean smaller windows and fewer overlapping sets.

Keep a bounded retry with jitter, because deadlocks are a legitimate transient outcome under concurrency and the victim is always safe to replay. But treat retry as the safety net after ordering is fixed: a rising deadlock rate masked by retries is latency and lost throughput you are choosing not to see.

For hot single-row counters, consider removing the contention instead of ordering it — an append-only ledger with periodic aggregation has no cycle to create.

-- Lock order depends on plan order: two concurrent calls can mirror
UPDATE accounts SET balance = balance + $2 WHERE id = ANY($1);

-- Deterministic order makes a cycle impossible
BEGIN;
SELECT id FROM accounts
 WHERE id = ANY($1)
 ORDER BY id
 FOR UPDATE;          -- all locks taken up front, in one global order
UPDATE accounts SET balance = balance + $2 WHERE id = $3;
UPDATE accounts SET balance = balance - $2 WHERE id = $4;
COMMIT;

How to stop it coming back

• Establish one canonical row-lock order (usually primary key ascending) and enforce it in review for every multi-row transaction
• Always `ORDER BY` in `SELECT ... FOR UPDATE`; an unordered locking read is an ordering bug waiting for traffic
• Track `pg_stat_database.deadlocks` as a metric and alarm on rate, so retries cannot hide a regression
• Test concurrency with mirrored arguments — the only shape that catches this class of bug
• Prefer append-only writes over in-place updates of hot shared rows

FAQ

Related

Other errors engineers hit next to this one

• Exactly-once claim fails at an external side effect
• Dead-letter queue growing without an alert
• Idempotency key reused with a different request body
• Read-after-write returned stale data from a replica
• Replication lag: replica served stale data after a write
• FATAL ERROR: Reached heap limit Allocation failed
• Unhandled promise rejection crashes the process
• ECONNRESET: socket hang up on a reused connection