Diagnosing and preventing Postgres deadlocks (40P01)

Symptoms

• A query or transaction fails with `ERROR: deadlock detected`
• The server log shows a DETAIL line naming two process IDs waiting on each other
• It happens only under concurrent writes and each individual transaction looks correct on its own

The diagnostic path

9 steps, exactly as this revision published them.

1. Step 1 · StartYou're seeing SQLSTATE 40P01

   A deadlock always self-resolves: Postgres's background deadlock detector wakes after deadlock_timeout (1 second by default) of waiting, finds the wait cycle, and aborts one of the two transactions so the other can proceed. By the time you're reading the error, it's already over — there is nothing 'stuck' left to kill. What's left to do is find out why the two transactions waited on each other and stop it recurring.

   What happens next

   • passed → step 2, Read the DETAIL line Postgres already logged

2. Step 2 · TestRead the DETAIL line Postgres already logged

   Deadlock errors are always written to the server log with DETAIL, CONTEXT and STATEMENT lines, regardless of client-visible log settings, because it's an ERROR-severity message. Find the entry for this occurrence — it names the two blocked process IDs, what lock each was waiting for, and (in CONTEXT) the statement each was running. If log_line_prefix doesn't include enough to correlate application requests to PIDs, note that for later.

   What happens next

   • passed → step 3, Do the two statements touch the same rows/tables in a different order?
   • failed → step 4, Turn on full lock-wait logging for the next occurrence
   • unknown → step 4, Turn on full lock-wait logging for the next occurrence

3. Step 3 · TestDo the two statements touch the same rows/tables in a different order?

   Compare the two STATEMENT lines (and the code paths that issued them) against each other. The classic deadlock is two transactions that both update, say, table A then table B, but one code path does it A-then-B while another does it B-then-A — under concurrency each can end up holding the first lock and waiting for the second, which the other already holds.

   What happens next

   • passed → step 5, Classic lock-ordering deadlock
   • failed → step 6, Not a simple two-table ordering mismatch
   • unknown → step 6, Not a simple two-table ordering mismatch

4. Step 4 · FixTurn on full lock-wait logging for the next occurrence

   If the existing log entry didn't have enough detail to pin down which two code paths were involved — most often because log_line_prefix is missing the PID, or the application doesn't log which request issued which query — enable logging of lock waits generally so the next deadlock (or even a near-miss that doesn't escalate to one) is fully captured with context you can correlate.

5. Step 5 · Root causeClassic lock-ordering deadlock

   Two code paths acquire locks on the same set of rows/tables in different orders. Under enough concurrency, two transactions can each grab the first lock in their own order and then block waiting for the second — a genuine cycle, which Postgres detects and breaks.

   What happens next

   • passed → step 7, Enforce a single, consistent lock acquisition order

6. Step 6 · Root causeNot a simple two-table ordering mismatch

   If the two statements touch the same rows/tables in the same nominal order, the conflict is more likely coming from an implicit lock — a foreign key check taking a share lock on the referenced row, a unique index insert, or a SELECT ... FOR UPDATE scanning more rows than expected because it isn't using the index you assumed. Tracing that down is outside what this playbook covers; the DETAIL/CONTEXT lines from the log are the starting point.

7. Step 7 · FixEnforce a single, consistent lock acquisition order

   Make every code path that touches more than one row from the same contended set acquire its locks in the same order — for example, always by ascending primary key. Where possible, do it in one statement rather than a loop of separate statements from application code, since a single UPDATE/SELECT processes its own row locks in a determinate order that application-level loops don't guarantee.

   Changes statesql

   Copy

   Changes system or service state. Review before running.

   Takes row-level locks on the selected accounts, in ascending id order, for the remainder of the transaction. No rows are modified by this statement itself; it only changes lock acquisition order to prevent a future cycle.

   SELECT id FROM accounts WHERE id = ANY($1::int[]) ORDER BY id FOR UPDATE;

   What happens next

   • passed → step 8, Confirm deadlocks have stopped recurring

8. Step 8 · Verify the fixConfirm deadlocks have stopped recurring

   pgstatdatabase keeps a running deadlock counter per database since the last stats reset.

   Read-onlysql

   Copy

   SELECT datname, deadlocks
   FROM pg_stat_database
   WHERE datname = current_database();

   Expected result

    datname | deadlocks 
   ---------+-----------
    appdb   |         0
   (1 row)

   What happens next

   • passed → step 9, Resolved
   • failed → step 6, Not a simple two-table ordering mismatch
   • unknown → step 6, Not a simple two-table ordering mismatch

9. Step 9 · EndResolved

   The deadlock counter is no longer climbing under the same workload that used to trigger it.

Sources

• 13.3. Explicit Locking— PostgreSQL
• 20.8. Error Reporting and Logging— PostgreSQL
• Chapter 28. Monitoring Database Activity— PostgreSQL