Postgres raises ERROR: deadlock detected (SQLSTATE 40P01) when two transactions each hold a lock the other needs. The durable fix is to make every code path lock rows in the same order; a retry on 40P01 is the safety net, not the cure. If you only add the retry, you hide the bug and pay for it in latency.
What does the error actually say?
This is the shape of the message you get in the application and the server log:
ERROR: deadlock detected
DETAIL: Process 8121 waits for ShareLock on transaction 90417; blocked by process 8130.
Process 8130 waits for ShareLock on transaction 90416; blocked by process 8121.
HINT: See server log for query details.
CONTEXT: while updating tuple (0,12) in relation "accounts"
Read it as a cycle. Process 8121 is waiting for a transaction that process 8130 owns, and 8130 is waiting for one that 8121 owns. Postgres notices the cycle after deadlock_timeout (1 second by default), picks one transaction as the victim, and aborts it. The other one proceeds as if nothing happened.
Two things trip people up. The victim is not necessarily the "wrong" transaction, so you cannot fix this by blaming one code path. And the CONTEXT line names only the row the victim was waiting on when it was killed, not the whole story. The server log, not the client error, has the full query text for both sides.
Takeaway: a deadlock is a cycle between two or more transactions, and the error message is only the last edge of that cycle.
Why does it only happen under load?
A deadlock needs two transactions to interleave at exactly the wrong moment, so it passes every test you run alone. The classic version is a transfer between two accounts:
-- Transaction A (transfer 1 -> 2)
BEGIN;
UPDATE accounts SET balance = balance - 10 WHERE id = 1;
UPDATE accounts SET balance = balance + 10 WHERE id = 2;
COMMIT;
-- Transaction B (transfer 2 -> 1), running at the same moment
BEGIN;
UPDATE accounts SET balance = balance - 5 WHERE id = 2;
UPDATE accounts SET balance = balance + 5 WHERE id = 1;
COMMIT;
A locks row 1, B locks row 2, then each reaches for the row the other holds. Neither can finish. Nothing about this is a Postgres quirk; any database with row locks behaves this way.
The less obvious sources, in the order I tend to find them:
-
Batch updates with no stable order.
UPDATE ... WHERE id IN (...)locks rows in whatever order the plan visits them. Two workers updating overlapping sets can lock in opposite orders. Postgres does not guarantee the order from theINlist. -
Foreign keys. Inserting a child row takes a
FOR KEY SHARElock on the parent row. Concurrent inserts of children plus an update of the same parent can form a cycle you never wrote explicitly. -
Upserts and unique indexes. Two transactions inserting overlapping sets of keys with
INSERT ... ON CONFLICTin different orders can deadlock on the index entries. -
Mixed application paths. One endpoint locks
orderstheninventory; a background job locksinventorythenorders.
Takeaway: most production deadlocks come from two code paths touching the same rows in different orders, not from a single bad query.
How do you find the two queries involved?
Turn on lock-wait logging so the server log tells you who waited on whom. These are standard settings; set them in postgresql.conf or through your managed provider's parameter UI:
ALTER SYSTEM SET log_lock_waits = on;
SELECT pg_reload_conf();
With log_lock_waits on, any wait longer than deadlock_timeout is logged, and a deadlock entry includes the statements for each process involved. Pair that with log_line_prefix containing the process id (%p) so you can match Process 8121 to its query. If you ship logs to a hosted service, search for deadlock detected and read the two statements side by side. The ordering mismatch is usually visible in thirty seconds.
Takeaway: turn on log_lock_waits before you need it, because a deadlock you cannot attribute to two specific statements is a deadlock you cannot fix.
How do you fix it: consistent lock ordering
Lock the rows you are about to modify up front, in a deterministic order, then do the writes. For the transfer example:
BEGIN;
SELECT id
FROM accounts
WHERE id = ANY(ARRAY[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;
Both directions of transfer now lock id 1 before id 2. The second transaction waits for the first instead of forming a cycle, and waiting is fine; only circular waiting is fatal.
In application code, sort the keys before you touch them. A Python sketch with psycopg 3:
import psycopg
def transfer(conn: psycopg.Connection, src: int, dst: int, amount: int) -> None:
with conn.transaction():
# Lock both rows in a fixed order, regardless of transfer direction.
conn.execute(
"SELECT id FROM accounts WHERE id = ANY(%s) ORDER BY id FOR UPDATE",
([src, dst],),
)
conn.execute(
"UPDATE accounts SET balance = balance - %s WHERE id = %s",
(amount, src),
)
conn.execute(
"UPDATE accounts SET balance = balance + %s WHERE id = %s",
(amount, dst),
)
For batch jobs, the same idea applies: select the target ids with ORDER BY id FOR UPDATE, or use FOR UPDATE SKIP LOCKED if workers should divide work rather than queue behind each other. Also keep transactions short. A transaction that holds locks while it calls an external API widens the window for every cycle.
Takeaway: if every transaction acquires locks in one global order (for example ascending primary key), a lock cycle cannot form.
What should the retry look like?
Ordering fixes the cycles you know about. Foreign-key and index locks can still produce rare ones, so wrap write transactions in a retry that catches only the deadlock error:
import random
import time
import psycopg
from psycopg import errors
def run_with_deadlock_retry(conn, fn, attempts=4):
for attempt in range(attempts):
try:
return fn(conn)
except errors.DeadlockDetected: # SQLSTATE 40P01
if attempt == attempts - 1:
raise
time.sleep(random.uniform(0, 0.05 * 2 ** attempt))
Retry the whole transaction, not the single statement that failed, because Postgres has already rolled it back. Make sure the function is safe to run twice, which means no external side effects inside it. Jitter matters: without it, the two transactions can collide again in lockstep.
| Approach | Removes the cycle? | Cost | Use when |
|---|---|---|---|
Consistent lock order (ORDER BY id FOR UPDATE) |
Yes | One extra statement per transaction | Always, as the primary fix |
Retry on 40P01
|
No, it recovers | Latency on the rare collision | As a safety net behind ordering |
| Shorter transactions | Narrows the window | Refactoring | Locks held across slow calls |
SKIP LOCKED |
Avoids waiting | Rows may be skipped this pass | Worker pools draining a queue |
Raise deadlock_timeout
|
No | Slower detection | Rarely; it just delays the failure |
Takeaway: ordering prevents deadlocks, retries absorb the leftovers, and raising timeouts does neither.
FAQ
Is a Postgres deadlock a bug in my database?
No. Postgres is behaving correctly by detecting the cycle and aborting one transaction; the bug is in the application's lock ordering.
Should I just retry on deadlock detected?
Retry as a safety net, but fix the ordering first. A retry on its own turns a correctness bug into intermittent latency spikes.
What is SQLSTATE 40P01?
It is the code Postgres returns for deadlock_detected. Catch that code specifically instead of retrying on every error.
Bottom line
If you see deadlock detected, enable log_lock_waits, find the two statements, and make both paths lock rows in the same order, usually ascending primary key via SELECT ... ORDER BY id FOR UPDATE. Then wrap write transactions in a small jittered retry on 40P01 for the cycles you cannot predict. Do not treat a longer deadlock_timeout as a fix. As of late 2026 this behavior is stable across supported Postgres versions, though the exact log wording can differ slightly.
Top comments (0)