POSTGRESQL ERROR 40P01: DEADLOCK DETECTED ------------------------------------------------------------------------ Two transactions each hold a lock the other wants, so PostgreSQL aborts one to break the cycle. Which one it kills is not predictable, so the fix is never in error handling alone — it is acquiring locks in a consistent order, plus retrying the victim. CONFIDENCE : high VERIFIED : 2026-08-08 (fresh, 0d old) URL : https://knowbase.sh/k/postgres-40p01-deadlock-detected ERROR ------------------------------------------------------------------------ ERROR: deadlock detected CODES: 40P01, deadlock_detected PROBLEM ------------------------------------------------------------------------ A transaction fails with deadlock detected under concurrent load and succeeds when retried or when run alone. It appears at a rate proportional to traffic, so it is invisible in development and routine in production. The statement named in the error is often not the one at fault — it is simply the one PostgreSQL chose to abort. ROOT CAUSE ------------------------------------------------------------------------ 1. [primary] Two code paths lock the same rows in opposite orders One transaction updates A then B, another updates B then A. Each holds what the other needs. This is the textbook case and needs no explicit LOCK statement — ordinary row-level UPDATEs are enough. how to tell: The DETAIL line names two processes each waiting on the other, and the two statements touch the same tables in reverse order 2. [primary] A batch operation updates rows in unpredictable order An UPDATE or DELETE over a set of rows locks them in whatever order the plan produces. Two concurrent batches over overlapping sets can interleave and deadlock even though the application code looks sequential. how to tell: Both statements in the DETAIL are the same bulk statement, differing only in parameters or in the rows matched 3. [common] A foreign key forces a lock on the parent row Inserting a child row takes a lock on the referenced parent to keep the reference valid. Two transactions inserting children of each other's parents deadlock without either touching the other's table directly. how to tell: The DETAIL mentions a table the statement does not name, and that table is the target of a foreign key 4. [common] Lock upgrade from a read to a write A transaction that SELECTs a row and later updates it holds a weaker lock first and needs a stronger one after. Two transactions doing this on the same row block each other symmetrically. how to tell: Both transactions read the same row before writing it, and adding FOR UPDATE to the initial SELECT removes the deadlock 5. [edge] Long-running transactions widen the window Deadlock probability scales with how long locks are held. A transaction kept open across an external API call or user input turns a rare interleaving into a frequent one — the cause is duration, not order. how to tell: Deadlock rate falls sharply when transaction duration is reduced, without any change to lock ordering SOLUTION ------------------------------------------------------------------------ 1. Read the DETAIL and CONTEXT lines in the server log, not just the error. They name both processes, both statements and both locks — the error message alone names only the victim. $ grep -A6 'deadlock detected' /var/log/postgresql/postgresql-*.log | tail -40 note: Set log_lock_waits on so waits that do not yet deadlock are also recorded; they are the early warning for the same lock ordering problem. 2. Establish a single canonical lock order and apply it everywhere. Sorting the keys before touching them is the cheapest way to guarantee it. -- 🔴 order depends on which transfer arrives first UPDATE accounts SET balance = balance - 100 WHERE id = :from; UPDATE accounts SET balance = balance + 100 WHERE id = :to; -- ✅ always lock the lower id first, so two transfers cannot interleave SELECT id FROM accounts WHERE id IN (:from, :to) ORDER BY id FOR UPDATE; 3. For bulk statements, impose an order explicitly rather than trusting the plan. UPDATE items SET status = 'done' WHERE id IN (SELECT id FROM items WHERE status = 'queued' ORDER BY id FOR UPDATE); 4. Retry the aborted transaction. Deadlock is a transient, expected condition under concurrency, so 40P01 should be retried rather than surfaced to the user — with backoff and jitter, and a cap. // Retry only 40P01 and 40001 — never a constraint violation const RETRYABLE = new Set(["40P01", "40001"]); for (let attempt = 0; attempt < 3; attempt++) { try { return await db.tx(work); } catch (err) { if (!RETRYABLE.has(err.code) || attempt === 2) throw err; await sleep(50 * 2 ** attempt * (0.5 + Math.random())); } } note: The retry must re-run the whole transaction. Resuming mid-transaction is not possible — the abort rolled everything back. 5. Shorten transactions. Never hold one open across a network call, a queue publish, or anything waiting on a human. 6. Leave deadlock_timeout alone unless you have measured a reason. It is one second by default and controls how long a waiter sits before checking for a cycle — raising it hides real deadlocks for longer rather than preventing them. VERIFY: Under the concurrency that previously produced them, the PostgreSQL log records no new deadlock detected entries over a full traffic cycle, and any that remain are retried transparently rather than reaching the caller. FALLBACK: Where a consistent order genuinely cannot be imposed — independent services touching shared tables — serialise the conflicting operation behind an advisory lock so contention becomes a queue rather than a cycle. APPLIES TO ------------------------------------------------------------------------ PostgreSQL: 9.0 and later (Detection and automatic abort are server behaviour; deadlock_timeout defaults to 1s.) platforms: self-hosted, managed PostgreSQL NOT APPLICABLE TO ------------------------------------------------------------------------ - SQLSTATE 40001 serialization_failure, which is an isolation-level conflict rather than a lock cycle - Lock waits that eventually succeed, which are contention rather than deadlock and never abort - MySQL error 1205 lock wait timeout, a timeout on a single lock rather than a detected cycle - Application-level deadlocks between processes, which the database cannot see or resolve EVIDENCE ------------------------------------------------------------------------ 1. PostgreSQL — Explicit Locking, Deadlocks https://www.postgresql.org/docs/current/explicit-locking.html The PostgreSQL Global Development Group | official-docs | read 2026-08-08 supports: That PostgreSQL detects the cycle and aborts one transaction unpredictably, that row-level locks alone are enough to deadlock without explicit LOCK statements, and that consistent lock ordering plus retrying the victim is the prescribed defence. 2. PostgreSQL — Lock Management (deadlock_timeout) https://www.postgresql.org/docs/current/runtime-config-locks.html The PostgreSQL Global Development Group | official-docs | read 2026-08-08 supports: That deadlock detection is deferred by deadlock_timeout, one second by default, and that raising it delays reporting of real deadlocks rather than avoiding them. 3. PostgreSQL Error Codes (Appendix A) https://www.postgresql.org/docs/current/errcodes-appendix.html The PostgreSQL Global Development Group | official-docs | read 2026-08-08 supports: That 40P01 is deadlock_detected in class 40, Transaction Rollback — the class whose members are by definition safe to retry. CONFIDENCE ------------------------------------------------------------------------ high — The detection mechanism, the unpredictability of which transaction is aborted, the lock-ordering defence and the deadlock_timeout default are all quoted from PostgreSQL's own documentation. The foreign-key and lock-upgrade cases follow from documented locking behaviour rather than from sentences about deadlocks specifically, and the retry code is illustrative. ------------------------------------------------------------------------ knowbase 0.1.0 — CC-BY-4.0