EC
40P01
PostgreSQLERRORNotableTransaction RollbackHIGH confidence

deadlock detected

What this means

Postgres's deadlock detector found a cycle in the lock wait graph: transaction A is waiting for a lock held by transaction B, and transaction B is waiting for a lock held by transaction A (or a longer chain). One transaction is chosen as victim and rolled back to break the cycle.

Why it happens
  1. 1Two transactions acquire locks on the same rows or tables in opposite order
  2. 2Long transactions holding locks while performing external I/O or application logic
  3. 3Implicit lock ordering differences between application code paths that share the same tables
  4. 4Bulk operations (DELETE, UPDATE) locking many rows competing with targeted operations on the same rows
How to reproduce

Two transactions lock rows in opposite order, creating a wait cycle.

trigger — this will error
trigger — this will error
CREATE TABLE accounts (id INT PRIMARY KEY, balance NUMERIC);
INSERT INTO accounts VALUES (1, 100), (2, 200);

-- Session 1:
BEGIN;
UPDATE accounts SET balance = balance - 10 WHERE id = 1;
-- pause here while Session 2 runs

-- Session 2:
BEGIN;
UPDATE accounts SET balance = balance - 10 WHERE id = 2;
UPDATE accounts SET balance = balance - 10 WHERE id = 1; -- waits for Session 1

-- Session 1 continues:
UPDATE accounts SET balance = balance - 10 WHERE id = 2; -- deadlock triggers 40P01

expected output

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

Fix 1

Enforce a consistent lock acquisition order

WHEN When multiple code paths lock the same set of rows — always lock in the same canonical order (e.g., ascending id).

Enforce a consistent lock acquisition order
BEGIN;
UPDATE accounts SET balance = balance - 10 WHERE id = 1; -- always id=1 first
UPDATE accounts SET balance = balance - 10 WHERE id = 2; -- then id=2
COMMIT;

Why this works

Deadlocks only occur when lock acquisition order forms a cycle. By enforcing a global ordering (e.g., always lock lower id before higher id), all transactions acquire locks in the same sequence, making a wait cycle impossible.

Fix 2

Pre-lock all rows at transaction start with SELECT FOR UPDATE ORDER BY

WHEN When the set of rows to be updated is determined by a query and ordering can be imposed.

Pre-lock all rows at transaction start with SELECT FOR UPDATE ORDER BY
BEGIN;
SELECT id FROM accounts
WHERE id IN (1, 2)
ORDER BY id
FOR UPDATE;

UPDATE accounts SET balance = balance - 10 WHERE id = 1;
UPDATE accounts SET balance = balance - 10 WHERE id = 2;
COMMIT;

Why this works

Pre-acquiring all locks at the start of the transaction in a defined order prevents the interleaved lock acquisition pattern that causes cycles. The FOR UPDATE with ORDER BY ensures the lock order is deterministic regardless of which session executes first.

What not to do

✕ Catch 40P01 and immediately retry without rolling back first

The transaction is already aborted; any further commands return 25P02. You must ROLLBACK and start a new transaction before retrying.

Sources
Official documentation ↗

src/backend/storage/lmgr/deadlock.c — DeadLockCheck()

Deadlocks ↗

Content generated with AI assistance and reviewed for accuracy. Found an error? hello@errcodes.dev

← All PostgreSQL errors