ERROR: deadlock detected (PostgreSQL) — how to fix
ERROR: deadlock detected
DETAIL: Process 12345 waits for ShareLock on transaction 67890; blocked by process 12346.
Process 12346 waits for ShareLock on transaction 67889; blocked by process 12345.
HINT: See server log for query details.
CONTEXT: while updating tuple (0,5) in relation "accounts"
Quick fix: Make every transaction lock rows in the same order (e.g. ORDER BY id with SELECT ... FOR UPDATE), keep transactions short, and retry the aborted transaction on SQLSTATE 40P01. Check the server log for both queries involved.
What it means
Transaction A locked row 1 and wants row 2; transaction B locked row 2 and wants row 1. Neither can finish. After deadlock_timeout (default 1s) Postgres checks for such cycles, picks a victim, and aborts it with deadlock detected (SQLSTATE 40P01). The other transaction continues normally.
The victim's whole transaction is rolled back. Nothing is corrupted, but your application must handle the error.
Find the queries involved
The client message hides the statements. The server log has them:
DETAIL: Process 12345: UPDATE accounts SET balance = balance - 10 WHERE id = 1
Process 12346: UPDATE accounts SET balance = balance - 5 WHERE id = 2
Turn on log_lock_waits = on to also log waits longer than deadlock_timeout. Live blocking can be seen with:
SELECT pid, pg_blocking_pids(pid) AS blocked_by, state, query
FROM pg_stat_activity
WHERE cardinality(pg_blocking_pids(pid)) > 0;
Cause 1 / Fix 1: rows locked in different orders
The classic case: a transfer that updates the two accounts in parameter order.
-- tx A: transfer 1 -> 2 -- tx B: transfer 2 -> 1
UPDATE accounts ... WHERE id=1; UPDATE accounts ... WHERE id=2;
UPDATE accounts ... WHERE id=2; UPDATE accounts ... WHERE id=1; -- deadlock
Lock in a consistent order up front:
BEGIN;
SELECT id FROM accounts WHERE id IN (1, 2) ORDER BY id FOR UPDATE;
UPDATE accounts SET balance = balance - 10 WHERE id = 1;
UPDATE accounts SET balance = balance + 10 WHERE id = 2;
COMMIT;
The same applies to batch updates: UPDATE ... WHERE id IN (...) doesn't guarantee order, so lock with SELECT ... ORDER BY id FOR UPDATE first, or sort the batch in the application.
Cause 2 / Fix 2: foreign keys and parent rows
Inserting child rows takes a FOR KEY SHARE lock on the referenced parent row. Two transactions that insert children and then update the same parents can deadlock. Update the parent first, or avoid updating parent rows in the same transaction that inserts children.
Cause 3 / Fix 3: long transactions
The longer a transaction holds locks, the more likely a cycle. Don't do network calls or user interaction inside a transaction, and commit as early as possible.
Cause 4 / Fix 4: explicit table locks and DDL
LOCK TABLE, ALTER TABLE or CREATE INDEX (without CONCURRENTLY) running alongside normal traffic can deadlock with it. Run migrations during low traffic, use CREATE INDEX CONCURRENTLY, and set lock_timeout for DDL:
SET lock_timeout = '5s';
Always: retry on 40P01
Deadlocks can't be ruled out completely under concurrency, so retry the whole transaction:
from psycopg import errors
for attempt in range(3):
try:
with conn.transaction():
transfer(conn, a, b, amount)
break
except errors.DeadlockDetected:
if attempt == 2:
raise
Retry the entire transaction, not just the failed statement — everything before it was rolled back.
Last updated 2026-11-10.