Deadlocks

A deadlock is a cycle: A waits for something B holds, B waits for something A holds, and neither can proceed. Unlike every other lock problem in this part, PostgreSQL resolves it for you — by aborting one of the participants.

That makes deadlocks the least dangerous lock problem and the most informative one, because they reveal an ordering defect in application code that will keep producing them.

Producing one

Two transactions taking the same two rows in opposite orders.

-- session A                          -- session B
BEGIN;                                BEGIN;
UPDATE lockdemo SET v=v+1 WHERE id=1; UPDATE lockdemo SET v=v+1 WHERE id=2;
-- both succeed, both hold one row
UPDATE lockdemo SET v=v+1 WHERE id=2; UPDATE lockdemo SET v=v+1 WHERE id=1;
-- A waits for B                      -- B waits for A

$ psql -U postgres -f deadlock-a.sql

UPDATE 1
ERROR:  deadlock detected
DETAIL:  Process 1652 waits for ShareLock on transaction 804143; blocked by process 1651.
HINT:  See server log for query details.
CONTEXT:  while updating tuple (0,2) in relation "lockdemo"
ROLLBACK

The other session is unaffected:

$ psql -U postgres -f deadlock-b.sql

UPDATE 1
UPDATE 1
COMMIT

One victim, one survivor. That is always the shape: PostgreSQL breaks the cycle by aborting exactly enough transactions to break it.

The log carries what the client is not told

$ docker logs rbpg-stor 2>&1 | grep -A6 "deadlock detected"

2026-08-27 20:00:05.795 UTC [1652] ERROR:  deadlock detected
2026-08-27 20:00:05.795 UTC [1652] DETAIL:  Process 1652 waits for ShareLock on transaction 804143; blocked by process 1651.
Process 1651 waits for ShareLock on transaction 804144; blocked by process 1652.
Process 1652: UPDATE lockdemo SET v=v+1 WHERE id=2;
Process 1651: UPDATE lockdemo SET v=v+1 WHERE id=1;
2026-08-27 20:00:05.795 UTC [1652] HINT:  See server log for query details.
2026-08-27 20:00:05.795 UTC [1652] CONTEXT:  while updating tuple (0,2) in relation "lockdemo"
2026-08-27 20:00:05.795 UTC [1652] STATEMENT:  UPDATE lockdemo SET v=v+1 WHERE id=2;

Compare the two. The client got one DETAIL line describing its own wait. The log has both lines, and both statements.

HINT: See server log for query details is therefore literal instruction rather than boilerplate. A deadlock cannot be diagnosed from the application’s exception, because the application’s exception contains only half of the cycle.

Fixes, in order of preference

Order your locks consistently. If every code path takes rows in ascending primary key order and tables in a fixed order, a cycle is impossible. This is the real fix and it is a code change.

-- both sessions do this, so both take id=1 before id=2
SELECT id FROM lockdemo WHERE id IN (1,2) ORDER BY id FOR UPDATE;

Shorten transactions. A deadlock requires two transactions to overlap while each holds something. Transactions that hold locks for milliseconds rarely find each other.

Take all locks up front. If a transaction knows what it will need, acquiring it all at the start — in the agreed order — removes the window where it holds some and wants more.

Retry. Necessary regardless, because no ordering discipline survives every code path. 40P01 is deadlock_detected and belongs in the same retry path as 40001 from lesson VII-04: catch SQLSTATE class 40, retry the whole transaction from BEGIN, back off, cap the attempts.

SELECT … FOR UPDATE NOWAIT when the right behaviour is to fail immediately rather than participate in a potential cycle.

Detection, and what deadlock_timeout really controls

PostgreSQL does not check for deadlocks continuously — maintaining the wait graph on every lock acquisition would cost more than the problem is worth.

Instead, when a session begins waiting for a lock, it sets a timer for deadlock_timeout (default 1 second). If the lock is granted before the timer fires, nothing happens and no check is made. If the timer fires, that session runs the deadlock detector: it builds the wait graph and looks for a cycle containing itself.

Three consequences follow.

A deadlock persists for roughly deadlock_timeout before being broken. With the default, a deadlocked pair is stuck for about a second.

The detector runs in the waiting backend, not in a background process. Cost is proportional to how often sessions wait longer than the timeout, which on a healthy system is rare.

deadlock_timeout is also the log_lock_waits threshold. Lowering it detects deadlocks faster and logs more lock waits. Raising it does the opposite of what people intend.

What to take from this

• A deadlock is a cycle; PostgreSQL breaks it by aborting one participant with SQLSTATE 40P01.
• The client sees half the story. The server log names both processes and both statements — read it.
• The cause is nearly always inconsistent ordering of rows or tables. Fix the ordering; retry regardless.
• Raising deadlock_timeout makes deadlocks last longer, not occur less often.
• The victim is whoever’s timer fired first, not the newest or cheapest transaction.

Cross-course references

• Observability for Production Sysadmins — Part XXXI (Logging foundations) covers shipping the deadlock report, which is the only complete record of what happened, and Part LIX (Database observability) covers alerting on the deadlock counter as a rate.
• Git, CI/CD & GitOps — Part CVI (Change management) covers routing a lock-ordering fix to the application team, since the database cannot fix it.

Quiz