Fix Postgres "Deadlock Detected" Errors

ERROR:  deadlock detected
DETAIL:  Process 4123 waits for ShareLock on transaction 99812; blocked by process 4188.
         Process 4188 waits for ShareLock on transaction 99807; blocked by process 4123.
HINT:  See server log for query details.
CONTEXT:  while updating tuple (12,7) in relation "accounts"

A deadlock is two (or more) transactions each holding a lock the other needs, so neither can ever proceed. Postgres detects the cycle and aborts one of them with this error so the other can finish. Your data is safe; one transaction failed and must be retried. But frequent deadlocks mean your transactions take locks in conflicting orders — and that's fixable.

How a deadlock forms

Two transfers run at the same time:

Time	Transaction A (transfer 1 → 2)	Transaction B (transfer 2 → 1)
t1	`UPDATE accounts ... WHERE id = 1` — locks row 1
t2		`UPDATE accounts ... WHERE id = 2` — locks row 2
t3	`UPDATE ... WHERE id = 2` — waits for B
t4		`UPDATE ... WHERE id = 1` — waits for A → cycle

Neither can continue. After waiting deadlock_timeout (default 1 second), Postgres checks for cycles, finds this one, and cancels one transaction.

Note that a deadlock is different from ordinary lock waiting: one transaction blocking another until it commits is normal. A deadlock is a cycle that can never resolve on its own. (Database transactions explained.)

Reading the server log

The full detail — including the statements of both transactions — is in the server log. Make sure you can see it:

log_lock_waits = on          # log waits longer than deadlock_timeout

The log names each process, the lock it waited for, and the queries involved. Map those back to code paths: which two features ran these statements concurrently?

Common causes and fixes

1. Inconsistent lock ordering (the classic)

Different code paths lock the same rows in different orders, as in the transfer example.

Fix: always lock in a consistent order. For transfers, lock the lower ID first:

SELECT id FROM accounts WHERE id IN ($1, $2) ORDER BY id FOR UPDATE;
-- now update both, in any order

SELECT ... FOR UPDATE with ORDER BY takes the row locks up front, in a deterministic order.

2. Batch updates in arbitrary order

UPDATE inventory SET stock = stock - 1 WHERE product_id = ANY($1);

Two concurrent batches with overlapping products can lock rows in different orders (the order rows are visited isn't guaranteed). Same fix — lock in order first:

WITH locked AS (
  SELECT product_id FROM inventory
  WHERE product_id = ANY($1)
  ORDER BY product_id
  FOR UPDATE
)
UPDATE inventory i SET stock = stock - 1
FROM locked WHERE i.product_id = locked.product_id;

Or sort the IDs in application code and update one at a time in that order.

3. Foreign keys

Inserting or updating a child row takes a lightweight lock (FOR KEY SHARE) on the referenced parent row, to make sure it isn't deleted mid-transaction. Transactions that update parents and insert children in different orders can deadlock on these. Patterns to watch: "insert order item, then update order total" in one path, "update order, then insert item" in another. Use a consistent order — typically lock the parent first. (Primary key vs foreign key.)

4. Upserts and unique constraints

Concurrent INSERT ... ON CONFLICT batches with overlapping keys in different orders can deadlock on the unique index. Sort the rows by the conflict key before inserting.

5. Long transactions

The longer a transaction holds locks, the more chances for a cycle. Keep transactions short: no network calls (sending email, calling APIs) inside a transaction, no waiting on user input. Do the slow work before or after. The transactional outbox pattern helps move side effects out.

6. Explicit table locks and DDL

LOCK TABLE, or migrations running ALTER TABLE during traffic, take heavy locks that interact badly with normal queries. Run migrations with a lock_timeout so they fail fast instead of piling up. (Postgres migrations on large tables.)

Retrying safely

Even well-designed systems see occasional deadlocks. The aborted transaction gets SQLSTATE 40P01 (deadlock_detected). Your app should catch it and retry the whole transaction — not just the failed statement — with a short random backoff:

async function withRetry<T>(fn: () => Promise<T>, attempts = 3): Promise<T> {
  for (let i = 1; ; i++) {
    try {
      return await db.transaction(fn);
    } catch (err: any) {
      const retryable = err.code === "40P01" || err.code === "40001"; // deadlock, serialization failure
      if (!retryable || i >= attempts) throw err;
      await new Promise((r) => setTimeout(r, 20 * i + Math.random() * 50));
    }
  }
}

Only retry if the transaction is safe to run again — no side effects already performed outside the database. (Idempotency keys.)

Advisory locks for application-level ordering

When the "resource" being contended isn't a single row — say, "recalculate this customer's invoices" — a Postgres advisory lock on the customer ID serialises the work cleanly. (Postgres advisory locks.)

Investigating live lock waits

SELECT pid, pg_blocking_pids(pid) AS blocked_by, wait_event_type, state,
       now() - query_start AS waiting_for, left(query, 60)
FROM pg_stat_activity
WHERE cardinality(pg_blocking_pids(pid)) > 0;

shows who is blocked by whom right now.

The summary

A deadlock is a cycle of lock waits; Postgres aborts one transaction after deadlock_timeout.
Main cause: locking the same rows in different orders. Fix with consistent ordering (ORDER BY ... FOR UPDATE).
Watch batch updates, foreign keys, upserts and long transactions.
Retry the whole transaction on 40P01, with backoff, when it's safe to.
Turn on log_lock_waits to see both sides of every deadlock.

EasySpawn gives Claude Code your real PostgreSQL and app on one server, so it can reproduce a deadlock with two concurrent sessions, read the server log, and verify the fix. See how it works or join the waitlist.

Postgres "Deadlock Detected": Why It Happens and How to Prevent It