PostgreSQL deadlock detected: read the lock graph

The symptoms

• •Application receives the error message 'deadlock detected' with SQLSTATE 40P01 and the offending transaction is forcibly aborted.
• •PostgreSQL server log records the aborted statement, often with a DETAIL or HINT block describing the relations involved in the cycle.
• •One peer transaction completes successfully while the other rolls back; retries sometimes succeed and sometimes fail.
• •Failures appear intermittently under concurrent load rather than consistently, often clustered around peak traffic.
• •Application stack traces point to a JDBC, psycopg, or pgx call site that was inside a DML or DDL statement when the error surfaced.

Likely causes

• •Two transactions acquire locks on overlapping rows or tables in opposite order, forming a wait-for cycle that the deadlock detector breaks.
• •Concurrent INSERTs or UPDATEs against rows referenced by a foreign key whose referencing column lacks an index, forcing broader locks on the referenced table.
• •Lock-mode escalation, where a transaction that holds many row-level locks later needs a relation-level lock (or vice versa) and a peer holds the inverse.
• •Conflicting pg_advisory_lock or pg_try_advisory_lock calls across processes that do not follow a shared global acquisition order.
• •Long-running SELECT ... FOR UPDATE combined with concurrent UPDATE or DELETE on the same rows.
• •A serialization failure (SQLSTATE 40001) misread as a deadlock; both abort the transaction but they have different causes and remedies.

First ten minutes

1. 01Capture the PostgreSQL server log lines around the failure; the deadlock detector emits a DETAIL block listing the relations involved.
2. 02Record the backend PID of the aborted transaction from the application error message or server log.
3. 03Query pg_stat_activity for the aborted PID and any PIDs it references to see current session state, wait_event, and query text.
4. 04Query pg_locks for the same PIDs to enumerate granted and waiting locks, including locktype, mode, granted, and waitstart.
5. 05Compare statement order and isolation level across the two sessions to form a working hypothesis about the lock cycle.
6. 06Check whether the application already has retry logic; a clean retry may be sufficient if the cycle is timing-dependent.
7. 07Decide whether the next move is an application code change, a schema or index change, or a workload reshape.

Evidence to collect

• •The exact SQLSTATE 40P01 error text and any DETAIL or HINT lines emitted by the server, including relation names referenced in the cycle.
• •pg_stat_activity rows for the involved backend PIDs: state, wait_event_type, wait_event, query, xact_start, and query_start.
• •pg_locks rows for the same PIDs showing locktype, mode, granted, waitstart, and the joined relation identifier.
• •The application's view of the aborted transaction: isolation level, statements executed prior to the failure, and any explicit LOCK TABLE, SELECT ... FOR UPDATE, or advisory lock calls.
• •Application stack trace and the connection-to-PID mapping resolved against pg_stat_activity.
• •Current value of deadlock_timeout (and lock_timeout / statement_timeout) to confirm detection latency is consistent with observed time-to-failure.

Where to look

• •PostgreSQL server log (logging collector, stderr, or syslog) for the DETAIL block emitted by the deadlock detector.
• •pg_locks joined to pg_class and pg_stat_activity for live sessions and their relations.
• •pg_blocking_pids(backend_pid) for any backend still attached to a blocked transaction.
• •Application logs for the connection identifier, backend PID, retry behavior, and the statement that failed.
• •Information schema and pg_constraint for foreign keys and the indexes (or missing indexes) on the referencing columns.
• •Application source code for any explicit LOCK TABLE, SELECT FOR UPDATE, or pg_advisory_lock call sites.

Diagnostic steps

1. 01From the application error or server log, record the backend PID of the aborted transaction; the surviving blocker is named in the same log line or in pg_stat_activity.
2. 02Run pg_stat_activity for the suspected PIDs to read state, wait_event, query, and transaction timestamps; replace <pid> with the values gathered from logs.
3. 03Enumerate locks for the same PIDs against pg_locks, capturing locktype, mode, granted, waitstart, and the relation cast to regclass for human-readable table names.
4. 04For currently blocked backends, call pg_blocking_pids(<pid>) to obtain the immediate blocker set as a quick cycle indicator.
5. 05Reconstruct the cycle: starting from the aborted backend, follow granted locks to its blockers and their waiters back toward the aborted backend; the smallest such loop is the deadlock the detector broke.
6. 06Group pg_locks rows by locktype (tuple, relation, transactionid, advisory) to identify which mechanism produced each edge of the cycle.
7. 07Inspect foreign keys on tables touched by the failing statements via pg_constraint and verify that each FK column used in concurrent writes is covered by an index.
8. 08If the application issues explicit locks, review statement order in source; a shared global acquisition order is the usual remedy and must be verified under replay.

Common mistakes

• •Treating the deadlock as a single-statement bug and adding a retry without addressing the underlying ordering, so the cycle reappears under load.
• •Lowering lock_timeout or statement_timeout aggressively to mask the deadlock rather than diagnosing it, trading one error class for another without proof.
• •Ignoring locktype values such as 'advisory' or 'relation' in pg_locks and reconstructing an incomplete cycle that misses the actual edge.
• •Conflating SQLSTATE 40001 (serialization failure) with 40P01 (deadlock); both abort the transaction but the detection mechanism and remedy differ.
• •Adding an index on a different column than the foreign key and assuming the FK-related row lock has gone away; verify with EXPLAIN that the new index is used.
• •Re-running the failing workload without confirming whether the peer transaction committed or rolled back, producing an apparent fix that is actually coincidence.

Safe fixes

• •Enforce a consistent lock acquisition order across all transactions that touch the same set of tables or rows; document it and review changes against it.
• •Add an index on the foreign key column in the referencing table so concurrent INSERT or UPDATE does not acquire broad locks on the referenced table.
• •Replace SELECT ... FOR UPDATE with SELECT ... FOR UPDATE NOWAIT or SKIP LOCKED where the workload tolerates skipping or immediate failure, removing the cycle entirely.
• •Wrap the failing transaction in application-level retry logic with exponential backoff and a small jitter, scoped to the statement or whole transaction as appropriate.
• •When using pg_advisory_lock, establish and follow a global ordering across processes and never mix advisory locks with row-level locks in the same transaction unless the ordering is consistent for both.
• •Reduce transaction length so the window in which two transactions can interleave into a cycle shrinks; verify with a replay of the same workload.

Prove the fix

1. 01Replay the workload that produced the deadlock and confirm there is no SQLSTATE 40P01 in the server log during the test window.
2. 02During a stress run, sample pg_locks and verify there is no granted/waiting tuple-or-relation lock pair forming a cycle between two PIDs.
3. 03Capture application logs showing the retry logic completing successfully on a follow-up attempt without manual intervention.
4. 04For FK-related fixes, confirm the new index is chosen by running EXPLAIN (ANALYZE, BUFFERS) on the INSERT or UPDATE statement and observing an index scan, not a sequential scan, on the referenced table.
5. 05For advisory-lock fixes, audit pg_locks for advisory entries during a load run and verify acquisition follows the documented order across sessions.
6. 06Record deadlock_timeout and the observed time-to-failure to confirm detection latency still aligns with application retry budgets after the change.

Prevention and next steps

• •Adopt and document a global lock acquisition order for any multi-table transactions and enforce it during code review.
• •Require indexes on every foreign key column that participates in concurrent write paths and verify with EXPLAIN.
• •Prefer SKIP LOCKED or NOWAIT patterns in queue-style workloads so workers do not wait and cannot form a cycle.
• •Standardize transaction retry with bounded backoff for SQLSTATE 40P01 and 40001 as separate, distinguishable error classes.
• •Monitor pg_stat_activity wait_event counts and pg_locks granted-versus-waiting ratios so contention trends are visible before they escalate into deadlocks.

Safe commands and checks

SELECT pid, usename, application_name, state, wait_event_type, wait_event, query, xact_start, query_start FROM pg_stat_activity WHERE pid = ANY(ARRAY[<pid>]) ORDER BY xact_start;
SELECT l.pid, l.locktype, l.mode, l.granted, l.waitstart, l.relation::regclass AS relation, l.page, l.tuple FROM pg_locks l WHERE l.pid = ANY(ARRAY[<pid>]) ORDER BY l.granted, l.waitstart NULLS LAST;
SELECT pg_blocking_pids(<pid>);
SELECT pid, locktype, mode, granted, waitstart FROM pg_locks WHERE NOT granted ORDER BY waitstart NULLS LAST;
SELECT conname, conrelid::regclass AS from_table, confrelid::regclass AS to_table, conkey FROM pg_constraint WHERE contype = 'f' AND conrelid::regclass::text IN ('<table_a>','<table_b>');
SELECT name, setting, unit FROM pg_settings WHERE name IN ('deadlock_timeout','lock_timeout','statement_timeout');
SELECT l.pid, l.locktype, l.mode, l.granted, a.query FROM pg_locks l JOIN pg_stat_activity a ON a.pid = l.pid WHERE l.locktype = 'advisory';