Deadlocks: Cause, Detection and Prevention

In one line

A deadlock is a cycle of transactions each waiting for a lock the next one holds. PostgreSQL checks for cycles after deadlock_timeout (1 s) and aborts one victim with SQLSTATE 40P01. Prevent deadlocks by acquiring locks in a consistent order, keeping transactions short, and retrying the victim.

0/5 · 0%

Think of it like this

Two cars at a narrow bridge from opposite ends, each waiting for the other to reverse. Neither can move until someone forces one car back.

Key ideas

01
Classic cause: transaction A updates account 1 then 2; transaction B updates 2 then 1. Each holds one lock and waits for the other.
02
Detection: after waiting deadlock_timeout, PostgreSQL builds the wait-for graph, finds the cycle, and cancels one transaction: ERROR: deadlock detected ... Process 123 waits for ShareLock on transaction 456; blocked by process 789.
03
Prevention: lock rows in a deterministic order (sort IDs; SELECT ... WHERE id IN (1,2) ORDER BY id FOR UPDATE), touch tables in the same order in every code path, keep transactions short, and avoid user interaction inside transactions.
04
Hidden sources: FK checks lock parent rows (KEY SHARE); bulk UPDATEs without ORDER BY lock rows in physical order, which differs between runs; unique-index inserts by concurrent transactions for the same key wait on each other.
05
Diagnose with log_lock_waits = on and the deadlock log message; query pg_locks joined to pg_stat_activity (or pg_blocking_pids()) to see who blocks whom in real time.

Code & diagrams

deadlock.sqlsql

-- Session A                                -- Session B
BEGIN;                                       BEGIN;
UPDATE account SET balance = balance - 10
 WHERE id = 1;                               UPDATE account SET balance = balance - 10
                                              WHERE id = 2;
UPDATE account SET balance = balance + 10
 WHERE id = 2;       -- waits for B          UPDATE account SET balance = balance + 10
                                              WHERE id = 1;   -- waits for A -> cycle
-- after ~1s one session gets:
-- ERROR:  deadlock detected
-- DETAIL: Process 4121 waits for ShareLock on transaction 9012; blocked by process 4133.

-- prevention: lock both rows in id order first
BEGIN;
SELECT id FROM account WHERE id IN (1, 2) ORDER BY id FOR UPDATE;
UPDATE account SET balance = balance - 10 WHERE id = 1;
UPDATE account SET balance = balance + 10 WHERE id = 2;
COMMIT;

-- who is blocking whom right now
SELECT pid, pg_blocking_pids(pid) AS blocked_by, wait_event_type, left(query, 60)
FROM pg_stat_activity WHERE cardinality(pg_blocking_pids(pid)) > 0;

wait-for.mermaiddiagram

Rendering diagram…

Interview problem

The problem

Deadlocks in a transfer service

A payments service logs ~200 deadlocks/hour during peak. Transfers debit the sender and credit the receiver; a nightly batch also updates balances with interest. Diagnose and fix.

When it breaks

Deadlock errors surfaced to users

What you see

Random 500 errors under load; the failed transaction is not retried and the user's action is lost.

Fix & prevent

Treat 40P01 and 40001 as retryable in the data access layer (bounded retries with jitter), and fix lock ordering at the root.

Explain it without notes

How does PostgreSQL detect deadlocks and why not immediately?

Practice

Explain why two concurrent INSERTs of the same unique key can block each other.

Trade-offs

↔
Consistent lock ordering adds discipline and sometimes an extra locking query; retries hide residual deadlocks at the cost of latency.

Done when you can

I can reproduce, read, diagnose and prevent deadlocks.

Command Palette