SQLSTATE 40P01 ERROR Class 40: Transaction Rollback

deadlock_detected deadlock detected — 40P01

PostgreSQL error "deadlock detected" (SQLSTATE 40P01): what it means, common causes, and how to fix it.

PG 12, 13, 14, 15, 16, 17, 18 Official docs
Last reviewed May 2025 Grounded in source

Diagnostic Queries

Symptoms

Two or more transactions each hold a lock the other needs, forming a cycle that can never resolve on its own. PostgreSQL’s deadlock detector broke the cycle by aborting one transaction — the victim — so the others can proceed.

  • One transaction is aborted with SQLSTATE 40P01; the rest continue.
  • The DETAIL shows the wait-for cycle between processes.
  • Appears under concurrency when sessions lock the same rows in different orders.

What the server log shows

ERROR:  deadlock detected
DETAIL:  Process 1234 waits for ShareLock on transaction 5678; blocked by process 9012.
        Process 9012 waits for ShareLock on transaction 3456; blocked by process 1234.
HINT:  See server log for query details.
CONTEXT:  while updating tuple (0,3) in relation "accounts"

Why PostgreSQL raises this — what the manual says

Section 13.3.5 Deadlocks:

“PostgreSQL automatically detects deadlock situations and resolves them by aborting one of the transactions involved, allowing the other(s) to complete.”

Each transaction waits on a lock held by another; the wait-for graph contains a cycle. After deadlock_timeout (default 1s) PostgreSQL runs cycle detection, picks a victim, and aborts it with 40P01. The victim should simply retry — the operation is expected to succeed once the winner commits.

Common causes

  • Two transactions updating the same rows in opposite orders.
  • Lock upgrades (read then write the same row from several sessions).
  • Foreign-key locks acquired in differing orders.
  • Long transactions holding locks while doing other work.

Relevant GUC parameters

Parameter Default Effect
deadlock_timeout 1s How long to wait on a lock before running deadlock detection. Lower values detect faster but cost more CPU.
log_lock_waits off When on, logs long lock waits — invaluable for diagnosing the queries involved in a deadlock.

How to fix it

  1. Always acquire locks / update rows in a consistent order across the app.
  2. Catch 40P01 and retry the transaction (it is safe to retry).
  3. Keep transactions short; do not hold locks across user think-time.
  4. Consider SELECT ... FOR UPDATE in a deterministic order to serialize contenders.

Diagnostic query

-- Who is blocking whom right now
SELECT blocked.pid AS blocked_pid, blocking.pid AS blocking_pid,
       blocked.query AS blocked_query
FROM pg_stat_activity blocked
JOIN pg_stat_activity blocking
  ON blocking.pid = ANY(pg_blocking_pids(blocked.pid))
WHERE cardinality(pg_blocking_pids(blocked.pid)) > 0;

Run during contention to see the live blocking chain.

Related & next steps

Reference: PostgreSQL 18 Section 13.3.4 “Deadlocks”.

Was this helpful?