40P01ERRORTier 2 — Caution✅ HIGH confidence

deadlock detected

Category: Transaction RollbackVersions: All Postgres versions

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

1Two transactions acquire locks on the same rows or tables in opposite order
2Long transactions holding locks while performing external I/O or application logic
3Implicit lock ordering differences between application code paths that share the same tables
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

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

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 multiple code paths lock the same set of rows — always lock in the same canonical order (e.g., ascending id).

fix

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 the set of rows to be updated is determined by a query and ordering can be imposed.

fix

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

Why it's wrong: The transaction is already aborted; any further commands return 25P02. You must ROLLBACK and start a new transaction before retrying.

Dangerous variant

⚠️ Warning

Postgres automatically rolls back the deadlock victim. If the application does not detect 40P01 and retry, the rolled-back operations are silently lost.

Sources

📚 Official docs: https://www.postgresql.org/docs/current/errcodes-appendix.html

📚 Feature docs: https://www.postgresql.org/docs/current/explicit-locking.html#LOCKING-DEADLOCKS

🔧 Source ref: src/backend/storage/lmgr/deadlock.c — DeadLockCheck()

📖 Further reading: Deadlocks

Confidence assessment

✅ HIGH confidence

The deadlock detection algorithm and error code are stable across all versions. The consistent-ordering fix is universally recommended. Edge case: deadlock detection runs every deadlock_timeout milliseconds (default 1s); brief waits shorter than this threshold complete without error.