Fix: ERROR: deadlock detected

PostgreSQL raises ERROR: deadlock detected when two or more transactions are each holding a lock that another needs, forming a cycle that cannot resolve itself. PostgreSQL breaks the cycle by aborting one transaction, so the immediate fix is to retry, and the real fix is to stop the cycle from forming.

What this error means

A deadlock is a circular wait. Transaction A holds a lock on row 1 and wants row 2. Transaction B holds a lock on row 2 and wants row 1. Neither can proceed, so they would wait forever. PostgreSQL runs a deadlock check (after deadlock_timeout, default one second) and, when it finds a cycle, aborts one transaction with SQLSTATE 40P01. The other transaction then continues normally.

The server log records exactly who was involved. Look for the DETAIL and HINT lines:

ERROR:  deadlock detected
DETAIL:  Process 12345 waits for ShareLock on transaction 6789; blocked by process 22233.
         Process 22233 waits for ShareLock on transaction 6790; blocked by process 12345.
         Process 12345: UPDATE accounts SET balance = balance - 100 WHERE id = 2;
         Process 22233: UPDATE accounts SET balance = balance + 100 WHERE id = 1;
HINT:  See server log for query details.

The two Process ... waits for lines are the cycle. The two queries beneath them are the statements each process was blocked on, which almost always reveals the conflicting lock order.

Common causes

• Two code paths update the same rows in opposite order (A updates row 1 then row 2, B updates row 2 then row 1).
• Long transactions that hold locks while doing slow work (external API calls, large reads, user think time) inside BEGIN ... COMMIT.
• SELECT ... FOR UPDATE statements that lock rows in an order that depends on the result set rather than a fixed key order.
• Foreign key checks taking a ShareLock on the parent row while another transaction updates that parent, colliding with child inserts.
• Bulk updates or upserts whose row order differs between concurrent sessions.
• Application retry loops that reacquire locks in the same bad order, turning one deadlock into a storm.

How to fix it

1. Stabilize: retry the aborted transaction. The losing transaction was rolled back cleanly with no partial writes, so it is safe to run again. If you are mid-incident and errors are spiking, add or confirm a short retry on SQLSTATE 40P01 at the application layer so requests recover instead of failing. Back off briefly between attempts so both sides do not collide again immediately.

2. Diagnose: read the deadlock from the log. Confirm log_lock_waits is on so waits and deadlocks are logged with query text:

SHOW log_lock_waits;      -- want on
SHOW deadlock_timeout;    -- default 1s
ALTER SYSTEM SET log_lock_waits = on;
SELECT pg_reload_conf();

Then read the DETAIL lines from the error above to identify the two statements and the rows or tables they contended for.

3. Diagnose live: inspect current locks and blockers. If the deadlock is recurring right now, see what is waiting on what:

SELECT blocked.pid   AS blocked_pid,
       blocked.query AS blocked_query,
       blocking.pid  AS blocking_pid,
       blocking.query AS blocking_query
FROM pg_stat_activity blocked
JOIN pg_stat_activity blocking
  ON blocking.pid = ANY(pg_blocking_pids(blocked.pid))
WHERE cardinality(pg_blocking_pids(blocked.pid)) > 0;

For raw detail you can also query pg_locks joined to pg_stat_activity on pid. pg_blocking_pids() (PostgreSQL 9.6 and later) is the simplest way to see the wait chain.

4. Fix properly: enforce a consistent lock order. Make every code path acquire locks in the same order, for example always by ascending primary key. When locking multiple rows explicitly, order them:

BEGIN;
SELECT id FROM accounts
 WHERE id IN (1, 2)
 ORDER BY id
 FOR UPDATE;
-- now update in the same fixed order
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

If both sides always lock the lower id first, the cycle cannot form. Apply the same rule to multi-table transactions: pick a fixed table order and keep it everywhere.

How to prevent it

• Establish one canonical lock order (by key, then by table) and enforce it in code review.
• Keep transactions short: do no network calls, file I/O, or user interaction between BEGIN and COMMIT.
• Batch bulk updates with a deterministic ORDER BY so concurrent runs lock rows in the same sequence.
• Add application retry logic on SQLSTATE 40P01 (and 40001 for serialization failures) with a small randomized backoff.
• Reduce lock scope: update only the columns you need, and avoid SELECT FOR UPDATE where a narrower lock or an optimistic version column would do.
• Consider lock_timeout to fail fast on pathological waits rather than blocking a full second before the deadlock check runs.

When to call a senior engineer

If deadlocks keep firing under load after you have added retries and a consistent lock order, or if the log points at foreign key ShareLock contention, upsert ordering, or a hot parent row that is hard to reorder around, the fix usually needs someone who can read the wait graph against the actual query plan and transaction boundaries. That is the point to bring in a senior engineer who can trace the contention end to end, reshape the transactions, and confirm the cycle is gone under real concurrency rather than in a quiet test.