Postgres Error: 40P01 — Deadlock Detected

ERROR:  deadlock detected
DETAIL:  Process 1234 waits for ShareLock on transaction 5678; blocked by process 9012.
         Process 9012 waits for ShareLock on transaction 6789; blocked by process 1234.
         Process 1234: UPDATE accounts SET balance = balance - 50 WHERE id = 2;
         Process 9012: UPDATE accounts SET balance = balance + 50 WHERE id = 1;
HINT:  See server log for query details.
SQLSTATE: 40P01

SQLSTATE 40P01 — deadlock_detected — is the signal that your application has at least two transactions caught in a lock cycle. Postgres’s deadlock detector picks a victim, aborts its transaction, and lets the other proceed. The error is never a Postgres bug; it’s always a concurrency pattern in your code that needs straightening out.

The single most effective fix is consistent lock ordering. If every transaction touches rows in ascending primary-key order, deadlocks become mathematically impossible — there is no cycle to form. Combined with a cheap retry loop (3 attempts with jitter), most production code never sees 40P01 surface to users at all.

Why this happens

• Inconsistent row update order across transactions. Tx A locks row 1 then row 2; Tx B locks row 2 then row 1. Both wait forever. The classic example is bank transfers: Tx A debits acct 1 then credits acct 2 while Tx B does the reverse. Always update in primary-key order to eliminate this.
• Long-running transactions holding locks. Transactions that span web requests, manual user input, or batch loops hold locks for seconds to minutes. Any concurrent transaction touching the same rows is at high risk of deadlock. Keep transactions tight — open, do work, commit fast.
• SELECT FOR UPDATE on a parent before the child. Locking a parent row (e.g., `orders`) and then descending into children (`order_items`) deadlocks against another transaction doing the reverse. Choose one direction and document it; mixed direction across the codebase is the root of most deadlocks.
• Foreign key cascading lock acquisition. INSERTs into a child table acquire a share lock on the parent FK row. If another transaction is updating that parent row exclusively, FK insertion blocks. Two transactions inserting children of two different parents while updating each other's parents form a deadlock.
• Index updates and bulk operations. Bulk INSERT or UPDATE statements lock many index pages. Two such bulk operations targeting overlapping index ranges in opposite orders deadlock. Adding `ORDER BY pk` to your bulk updates fixes most of these.

How to fix it

Fixes are ordered by likelihood. Start with the first one that matches your context.

-- Wrong: order depends on which account is debited first
BEGIN;
UPDATE accounts SET balance = balance - 50 WHERE id = 2;
UPDATE accounts SET balance = balance + 50 WHERE id = 1;
COMMIT;

-- Right: always lock by ascending id
BEGIN;
SELECT id FROM accounts WHERE id IN (1, 2) ORDER BY id FOR UPDATE;
UPDATE accounts SET balance = balance - 50 WHERE id = 2;
UPDATE accounts SET balance = balance + 50 WHERE id = 1;
COMMIT;

import { Pool } from 'pg';
const pool = new Pool();

async function withRetry(fn, maxAttempts = 3) {
  for (let i = 0; i < maxAttempts; i++) {
    const client = await pool.connect();
    try {
      await client.query('BEGIN');
      const result = await fn(client);
      await client.query('COMMIT');
      return result;
    } catch (err) {
      await client.query('ROLLBACK');
      if (err.code !== '40P01' || i === maxAttempts - 1) throw err;
      await new Promise(r => setTimeout(r, 50 * Math.random() * (i + 1)));
    } finally {
      client.release();
    }
  }
}

BEGIN;
SELECT pg_advisory_xact_lock(hashtext('rebalance:user-' || $1));
-- complex multi-row logic safe from deadlock here
COMMIT;

Detection and monitoring in production

Track 40P01 as a separate metric from generic SQL errors. A non-zero baseline (a few per hour) is normal in busy OLTP systems with retry logic. A spike usually correlates with a deploy that changed transaction shape — cross-reference with deploy timestamps. The Postgres log captures both transactions; mine it for the most common deadlocking SQL pair.