PostgreSQL: ERROR 40P01 deadlock detected — two transactions waiting on each other
40P01 deadlock detected — circular lock wait
Verified against PostgreSQL docs: Chapter 13 — Deadlocks, PostgreSQL source: src/backend/storage/lmgr/deadlock.c, PostgreSQL wiki: Lock Monitoring · Updated June 2026
> quick_fix
Two transactions each hold a lock the other needs, creating a circular wait. PostgreSQL's deadlock detector killed one transaction (yours) to break the cycle. Fix: ensure all transactions acquire locks in the same order (e.g., always lock rows by ascending ID), keep transactions short, and retry the failed transaction.
-- Bad: Transaction A locks row 1 then 2
-- Transaction B locks row 2 then 1 → deadlock
-- Fix: Always lock in consistent order (ascending ID)
BEGIN;
SELECT * FROM accounts WHERE id IN (1, 2)
ORDER BY id FOR UPDATE;
-- Now update safely
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;What causes this error
A deadlock occurs when two or more transactions each hold locks that the other needs to proceed. Transaction A holds a lock on row 1 and waits for row 2; Transaction B holds a lock on row 2 and waits for row 1. Neither can proceed. PostgreSQL runs a deadlock detector (every deadlock_timeout interval, default 1 second) that checks for circular wait-for dependencies. When it finds one, it terminates the transaction that has done the least work (fewest locks held), rolling it back and returning error 40P01 to the client.
How to fix it
- 01
step 1
Check the deadlock details in the PostgreSQL log
PostgreSQL logs the exact queries and lock types involved in the deadlock. Check the server log for lines containing 'deadlock detected' — they show which processes were involved and which locks they were waiting on.
-- Enable detailed deadlock logging if not already: SHOW log_lock_waits; -- should be 'on' SHOW deadlock_timeout; -- default '1s' - 02
step 2
Ensure consistent lock ordering
The primary fix for deadlocks is to acquire locks in the same order across all transactions. If you're updating multiple rows, sort them by primary key first. This prevents circular waits.
-- Lock rows in consistent order before updating: SELECT * FROM accounts WHERE id = ANY(ARRAY[5, 2, 8]) ORDER BY id FOR UPDATE; - 03
step 3
Keep transactions short
Long transactions hold locks longer, increasing the window for deadlocks. Move non-database work (API calls, file I/O, computation) outside the transaction. Only include the minimum SQL needed inside BEGIN/COMMIT.
- 04
step 4
Add retry logic in your application
Even with consistent ordering, rare deadlocks can still occur under high concurrency. The standard pattern is to catch SQLSTATE 40P01 and retry the entire transaction (not just the last statement).
import psycopg2 from psycopg2 import errors MAX_RETRIES = 3 for attempt in range(MAX_RETRIES): try: with conn: with conn.cursor() as cur: cur.execute("BEGIN") cur.execute("SELECT ... FOR UPDATE") cur.execute("UPDATE ...") cur.execute("COMMIT") break except errors.DeadlockDetected: if attempt == MAX_RETRIES - 1: raise conn.rollback()
How to verify the fix
- The transaction completes without 40P01 errors under normal load.
- Load testing with concurrent transactions shows no deadlocks.
- PostgreSQL logs show no new 'deadlock detected' entries.
Why 40P01 happens at the runtime level
PostgreSQL's deadlock detector runs in a background process that wakes every deadlock_timeout (default 1s). It builds a wait-for graph: each transaction waiting for a lock is a node, and edges point to the transaction holding the requested lock. If the graph contains a cycle, that's a deadlock. The detector in src/backend/storage/lmgr/deadlock.c uses a depth-first search to find cycles. When found, it selects a victim — the transaction in the cycle that has performed the least amount of work (measured by transaction age and lock count) — and sends it a SIGINT-equivalent that triggers an automatic rollback with SQLSTATE 40P01. The surviving transactions then acquire the released locks and proceed.
Common debug mistakes for 40P01
- Retrying only the last SQL statement instead of the entire transaction — after a deadlock, the whole transaction is rolled back. Retrying just the final UPDATE leaves the earlier SELECTs' state inconsistent.
- Adding a global lock (LOCK TABLE ... IN EXCLUSIVE MODE) to prevent deadlocks — this serializes all access to the table, destroying concurrency. Consistent lock ordering is almost always the right fix.
- Setting deadlock_timeout to a very high value thinking it prevents deadlocks — it only delays detection. The deadlock still happens; you just wait longer before PostgreSQL resolves it.
- Blaming the ORM for deadlocks — ORMs generate SQL that acquires locks in the order you wrote the code, not in a consistent order. If your code updates user then order and another path updates order then user, the ORM faithfully reproduces the deadlock. The fix is in your code's lock ordering, not the ORM.
- Not logging SQLSTATE in application error handlers — generic 'database error' messages hide whether the failure was a deadlock (retryable) or a constraint violation (not retryable). Always log the SQLSTATE code.
When 40P01 signals a deeper problem
Persistent deadlocks in a production application usually indicate that the data access layer lacks a consistent locking strategy. When each feature team writes transactions independently without a shared convention for lock ordering, deadlocks are inevitable at scale. The architectural fix is a documented locking convention: 'all transactions that touch multiple tables lock them in alphabetical order; all transactions that touch multiple rows lock them in primary key order.' Codify this in a shared database access module that handles ordering and retry logic, so individual features can't accidentally introduce new deadlock patterns.
Editor's take
The deadlock that taught me the most about PostgreSQL internals was in a payment system that transferred money between accounts. The transfer function locked the source account, checked the balance, locked the destination account, and credited it. Two simultaneous transfers between the same two accounts (A→B and B→A) deadlocked immediately. The fix was trivial: sort the account IDs and always lock the lower ID first. But finding it took hours because the application's error handling swallowed the SQLSTATE and logged a generic 'transaction failed' message.
The mental model that makes deadlocks intuitive is the dining philosophers problem from computer science: five philosophers sit around a table, each needing two forks to eat. If every philosopher picks up their left fork first, they all deadlock — each holding one fork, waiting for the other. The solution is the same in PostgreSQL: agree on a global ordering (always pick up the lower-numbered fork first) and the cycle becomes impossible.
In the same debugging session you'll often find: 'could not serialize access due to concurrent update' (serialization failure under SERIALIZABLE isolation — similar to deadlock but different mechanism), 'lock timeout' if lock_timeout is set and a transaction waits too long for a lock (not a deadlock, just slow), and 'canceling statement due to statement timeout' if statement_timeout kills a query that was waiting for a lock held by a long-running transaction.
By Bikram Nath · Curator · Updated June 2026
Frequently asked questions
Is a deadlock the same as a slow query?
No. A slow query is just taking a long time; a deadlock is two transactions permanently stuck waiting on each other. Slow queries resolve eventually; deadlocks never resolve without intervention (PostgreSQL's detector kills one).
Can SELECT cause deadlocks?
Plain SELECT doesn't acquire row locks, so it can't deadlock. But SELECT ... FOR UPDATE and SELECT ... FOR SHARE do acquire locks and can deadlock. Also, concurrent INSERT/UPDATE/DELETE operations acquire row-level locks implicitly.
What does deadlock_timeout control?
It's the interval between deadlock detection checks (default 1 second). It does NOT prevent deadlocks. Lower values detect deadlocks faster but add overhead from more frequent checks. Don't set it below 100ms in production.