Deadlock
What is a deadlock in PostgreSQL and how do you avoid it?
A deadlock happens when two transactions each wait for a lock the other one holds. PostgreSQL detects it and cancels one of them; taking locks in a consistent order prevents it.
A deadlock is a cycle of lock waits: transaction A holds a lock that B needs while B holds a lock that A needs, so neither can continue. PostgreSQL detects such cycles automatically and resolves them by aborting one of the transactions, which receives a deadlock detected error.
The check does not run on every lock wait: a session first waits for deadlock_timeout (one second by default) before looking for a cycle. The best defense is to lock the same objects in the same order in every transaction, for example by locking rows sorted by primary key, and to keep transactions short. Code that can still hit a deadlock should retry the aborted transaction.
Example
The example locks both rows it is about to change in one statement, sorted by id, before updating them. When every transaction that touches these rows does the same, they wait in line instead of deadlocking.
CREATE TABLE ledger (id int PRIMARY KEY, balance int);
INSERT INTO ledger VALUES (1, 100), (2, 100);
SHOW deadlock_timeout;
BEGIN;
SELECT id FROM ledger WHERE id IN (1, 2) ORDER BY id FOR UPDATE;
UPDATE ledger SET balance = balance - 10 WHERE id = 1;
UPDATE ledger SET balance = balance + 10 WHERE id = 2;
COMMIT;Plain SQL for stock PostgreSQL 16 or 17, no extensions needed. Run it in an empty database you can throw away.
Sources
Official PostgreSQL documentation (in English).