Incident brief
Deadlock detected
Two or more transactions waited on locks held by each other, forming a cycle. PostgreSQL detected the deadlock and aborted one transaction so the others could continue.
What lands in your log
ERROR: deadlock detected
In 10 seconds
- What triggers it
- Session A updates row 1 and keeps the transaction open.
- Fix
- Roll back and retry the aborted transaction.
- Proof
- Reproduced on PostgreSQL 16.14 → In this lab run, session B's transaction was cancelled by PostgreSQL's deadlock detector and rolled back; session A's transaction committed. Which participant gets aborted is decided internally by PostgreSQL's deadlock detector and should not be relied upon, a different run of the same cycle could cancel the other session instead.
Fix
What to do right now
The immediate, application-level response to this error.
Recovery Act now
- Roll back and retry the aborted transaction.
Prevention Safe
- Enforce a single, consistent lock acquisition order across the codebase.
- Split overly broad transactions into smaller atomic units so lock windows shrink.
-- standardize row access order
BEGIN;
UPDATE inventory SET quantity = quantity - 1 WHERE id = 1;
UPDATE inventory SET quantity = quantity - 1 WHERE id = 2;
COMMIT;
-- all concurrent workers must follow the same order: 1 then 2For this error
See this error live on the server
Run these against the affected instance to confirm the diagnosis before you act.
A deadlock is a wait-for cycle: two backends each hold a lock the other needs. You catch it in the act with pg_blocking_pids() and pg_locks, before the detector fires. In the reproduction below, PostgreSQL's own DETAIL line already prints the cycle ("Process 95 waits for ShareLock on transaction 5094; blocked by process 102. Process 102 waits ... blocked by process 95."). These two queries are how you see that same cycle live.
Blocking graph · who waits on whom
Each blocked backend's blocked_by points at the backend holding the lock it wants. In a deadlock those pointers form a cycle: A is blocked_by B and B is blocked_by A, the unambiguous fingerprint.
SELECT a.pid AS backend_pid,
a.state,
a.wait_event_type,
a.wait_event,
pg_blocking_pids(a.pid) AS blocked_by,
left(a.query, 80) AS running_query
FROM pg_stat_activity a
WHERE cardinality(pg_blocking_pids(a.pid)) > 0
OR a.pid = ANY (
SELECT unnest(pg_blocking_pids(x.pid)) FROM pg_stat_activity x
);Locks behind the cycle · pg_locks
For the same backends, every row lock they already hold shows granted = true, while the transactionid ShareLock each one is waiting for shows granted = false. Two waiters, each behind the other, which is exactly why PostgreSQL has to cancel one.
SELECT l.pid AS backend_pid,
l.locktype,
l.mode,
l.granted,
COALESCE(c.relname, l.transactionid::text) AS object
FROM pg_locks l
LEFT JOIN pg_class c ON c.oid = l.relation
WHERE l.pid IN (
SELECT a.pid FROM pg_stat_activity a
WHERE cardinality(pg_blocking_pids(a.pid)) > 0
)
ORDER BY l.granted, l.pid;Why it happens
What PostgreSQL is telling you
The mechanism behind the error, grounded in the official manual, not paraphrased.
PostgreSQL 16 Documentation, §13.3.4 Deadlocks
The use of explicit locking can increase the likelihood of deadlocks, wherein two (or more) transactions each hold locks that the other wants. [...] PostgreSQL automatically detects deadlock situations and resolves them by aborting one of the transactions involved, allowing the other(s) to complete. (Exactly which transaction will be aborted is difficult to predict and should not be relied upon.) [...] The best defense against deadlocks is generally to avoid them by being certain that all applications using a database acquire locks on multiple objects in a consistent order. [...] If it is not feasible to verify this in advance, then deadlocks can be handled on-the-fly by retrying transactions that abort due to deadlocks.Read the full section on postgresql.org →
Session A
Session A locked row 1 first, slept, then requested row 2. Its transaction committed cleanly because PostgreSQL chose to cancel the other participant in the cycle instead.Session B
Session B locked row 2 first, then requested row 1, the opposite order from session A. Once both sessions were each waiting on a row the other held, PostgreSQL's own message says exactly what happened: "Process 95 waits for ShareLock on transaction 5094; blocked by process 102. Process 102 waits for ShareLock on transaction 5093; blocked by process 95." That is a textbook wait-for cycle. PostgreSQL's deadlock detector picked one transaction (session B, in this run) to cancel so the other could proceed, neither session was ever going to finish on its own.Reproduce & verify
A real, two-session PostgreSQL reproduction
A literal transcript of SQL run against a live PostgreSQL instance in an isolated lab. The commands below are exactly what was executed.
- 1Session A updates row 1 and keeps the transaction open.
- 2Session B updates row 2 and keeps the transaction open.
- 3Session A tries to update row 2.
- 4Session B tries to update row 1, forming a lock cycle.
Setup runs first, then session A begins, then session B begins while session A is still open.
DROP TABLE IF EXISTS inventory;
CREATE TABLE inventory (
id integer primary key,
quantity integer not null
);
INSERT INTO inventory (id, quantity) VALUES (1, 10), (2, 20);BEGIN;
UPDATE inventory SET quantity = quantity - 1 WHERE id = 1;
SELECT pg_sleep(2);
UPDATE inventory SET quantity = quantity - 1 WHERE id = 2;
COMMIT;BEGIN;
UPDATE inventory SET quantity = quantity + 1 WHERE id = 2;
SELECT pg_sleep(2);
UPDATE inventory SET quantity = quantity + 1 WHERE id = 1;
COMMIT;What PostgreSQL actually returned
DROP TABLE
CREATE TABLE
INSERT 0 2BEGIN
UPDATE 1
pg_sleep
----------
(1 row)
UPDATE 1
COMMITBEGIN
UPDATE 1
pg_sleep
----------
(1 row)
ERROR: deadlock detected
DETAIL: Process 95 waits for ShareLock on transaction 5094; blocked by process 102.
Process 102 waits for ShareLock on transaction 5093; blocked by process 95.
HINT: See server log for query details.
CONTEXT: while updating tuple (0,2) in relation "inventory"
ROLLBACKThe recommended prevention, consistent lock ordering, was re-run against the same two-session, opposite-timing workload shape. Under this tested workload, no deadlock occurred after both sessions acquired locks in the same order.
Without this
Above: session B was cancelled, SQLSTATE 40P01, a deadlock cycle.
With this, tested
Below: same concurrent workload and timing, with lock order changed from (1→2 vs 2→1) to (1→2 vs 1→2), both sessions commit, no deadlock.
- A second operational test: exact SQL, raw output, measured result, and engineer notes
- A captured monitoring query with its raw output
- Fix that delays the error but does not prevent the deadlock: exact SQL, output, and verdict
- A measured deadlock_timeout before/after
- Raw PostgreSQL server-log evidence
- A 10-run load-harness proof with an invariant check
- A manual-grounded production interpretation of the lab result
Card required. Cancel before day 7 and you are not charged.
Runbook to fix this
Runbooks for this incident
Full step-by-step fixes for the condition behind this error: the diagnosis, the exact SQL, and output captured in the lab.
- Capture Sev-1 evidence before recovery erases itEvery postmortem ends at 'high load' because the evidence was gone by morning.Free
- Detect and resolve lock contentionQueries hang with no error and no progress, and you need the wait graph now.Free
- Assess multixact wraparound riskA second wraparound counter nobody monitors until the cluster stops accepting writes.Pro
Connected
Everything this error touches
Every page this SQLSTATE connects to: the concept that explains it, the runbooks that fix it, the parameters you tune to prevent it, and the sibling errors it travels with. All real cross-references. Jump straight in, or open the full interactive map.
Understand the concept
Fix it — runbooks
Set sane statement, lock, and idle timeoutsDetect and resolve lock contentionBuild a safe job queue with SKIP LOCKEDCapture Sev-1 evidence before recovery erases itCatch a high transaction rollback rateKnow which lock mode your statement takesResolve row-level lock contention from SELECT FOR UPDATEWatch wait events in pg_stat_activityAssess multixact wraparound riskCoordinate work with advisory locksRead pg_locks: relation waits versus tuple waitsTune to prevent it
Part of these pathways
Related errors
Verification
- Last verified
- 2026-07-14 (Docker lab, PostgreSQL 16.14)
- Verification scope
- Verified against PostgreSQL 16.14 in an isolated lab environment
- Audit status
- reviewed
Went further?
Pro unlocks the second lab proof
Free page stops the bleeding. Pro adds the operational test, SQLSTATE audit, and deeper evidence, same error, more certainty.