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.
In 10 seconds
- What
- Deadlock detected
- What triggers it
- Session A updates row 1 and keeps the transaction open.
- The 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.
The 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 2Diagnose
See it 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.
The 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
);The 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.
Same two concurrent sessions and the same sleep timing as the incident above, but with lock acquisition order changed: both transactions now touch row 1 before row 2 (previously session A ran 1→2 while session B ran 2→1). That reordering is the only variable changed.
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.
What Pro unlocks here
- The exact prevention SQL — copy-paste ready
- Raw psql output captured from the Docker lab
- A senior-DBA action list to take it further
- Live monitoring queries to catch it in production
- The deeper audit: fix-that-fails counterexample, GUC before/after, server-log evidence
Related & next steps
Follow the thread
Everything this error touches — jump straight to the sibling error, term, runbook, or parameter.
Related errors
GUCs
Verification
- Last verified
- 2026-07-14 (Docker lab, PostgreSQL 16.14)
- Reviewed by
- Verified against PostgreSQL 16.14 in an isolated lab environment
- Audit status
- reviewed