SQLSTATE 40P01 ERROR Class 40: Transaction Rollback

deadlock_detected deadlock detected — 40P01

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

PG 9.6, 10, 11, 12, 13, 14, 15, 16, 17, 18 Official docs
Last reviewed Jun 2026 Grounded in source

Diagnostic Queries

Symptoms

Two or more transactions formed a circular wait for locks. PostgreSQL detected the cycle and aborted one of them with SQLSTATE 40P01 (deadlock_detected).

  • A lock-wait cycle was detected among transactions.
  • PostgreSQL chose a victim and rolled it back.
  • Common with inconsistent lock-acquisition order.

What the server log shows

ERROR:  deadlock detected
DETAIL:  Process 123 waits for ShareLock on transaction 456; blocked by process 789.
HINT:  See server log for query details.

Why PostgreSQL raises this — what the manual says

Section 13.3.5 Deadlocks:

“The use of explicit locking can increase the likelihood of deadlocks, wherein two (or more) transactions each hold locks that the other wants.”

When transactions wait on each other’s locks in a cycle, none can proceed. The deadlock detector finds the cycle and aborts one transaction (the victim) with 40P01 so the others can continue.

Common causes

  • Transactions locking the same rows/tables in different orders.
  • Foreign-key or index lock interactions under concurrency.
  • Long transactions widening the window for cycles.

Relevant GUC parameters

Parameter Default Effect
deadlock_timeout 1s How long to wait before running deadlock detection.

How to fix it

  1. Acquire locks in a consistent order across all code paths.
  2. Keep transactions short to shrink the conflict window.
  3. Retry the aborted transaction; deadlocks are expected to be retried.

Related & next steps

Reference: PostgreSQL 18 Section 13.3 “Explicit Locking”.

Was this helpful?