Deadlocks in PostgreSQL: causes, reading the log, fixes Intermediate

Symptoms

A money-transfer application suddenly gets errors from PostgreSQL, SQLSTATE 40P01 (deadlock_detected). Each transaction succeeds when run alone; run concurrently, they take locks in opposite orders and form a wait cycle.

Distinguish two things:

• Lock wait: A holds a row, B waits for A. When A commits, B proceeds.
• Deadlock: A waits for something B holds while B waits for something A holds. Neither can make progress on its own, so PostgreSQL has to cancel one transaction.

Root cause

Deadlock: two sessions lock each other crosswise

◀ Prev▶ PlayNext ▶↺ RestartStep 1/5

A updates row 1 and holds the lock until the transaction ends.

Time	Session A	Session B	Can proceed?
1	Holds row 1		Yes
2		Holds row 2	Yes
3	Wants row 2, waits for B		B can still run
4	Waits for B	Wants row 1, waits for A	No: the cycle is closed

Once a lock wait lasts deadlock_timeout (default 1s), PostgreSQL checks for deadlock. This is a wait threshold before checking, not a periodic scan job. If it finds a cycle, it cancels one transaction.

The docs state that which transaction gets canceled is “difficult to predict and should not be relied upon”, so the code must handle both roles.

Reproducing and reading the error

Run examples/docker-compose.yml, then examples/src/postgres-locks/deadlock-demo.ts. The script opens two connections and has them update two rows in opposite orders:

// Session A
await sessionA.query('UPDATE accounts SET balance = balance - 100 WHERE id = 1'); // A holds row 1
// Session B
await sessionB.query('UPDATE accounts SET balance = balance - 50 WHERE id = 2');  // B holds row 2

// A tries to update row 2 (held by B) → A waits
const a2 = sessionA.query('UPDATE accounts SET balance = balance + 100 WHERE id = 2');
// B tries to update row 1 (held by A) → B waits → the cycle closes
const b2 = sessionB.query('UPDATE accounts SET balance = balance + 50 WHERE id = 1');

Real output from a run:

--- Result ---
Session A: ERROR — deadlock detected
  code: 40P01
  detail: Process 528 waits for ShareLock on transaction 760; blocked by process 527.
Process 527 waits for ShareLock on transaction 759; blocked by process 528.
  hint: See server log for query details.
Session B: succeeded

The two DETAIL lines together form the cycle: process 528 waits for 527 and 527 waits for 528. PIDs and transaction IDs change every run; only read the “who waits for whom” relationship. Classify the error by the code 40P01, not by matching the string deadlock detected.

Four common real-world deadlock patterns

Deadlocks are rarely two hand-written UPDATEs like above. More common:

Pattern	Why it deadlocks	Fix
Batched UPDATE/INSERT ... ON CONFLICT from two workers, each iterating rows in a different order	Two batches lock the same row set in different orders	Sort by key before sending, or ORDER BY id in the statement
Two tables, opposite order: flow 1 updates orders then inventory, flow 2 updates inventory then orders	Cycle across two tables	A codebase-wide table order convention
Foreign keys: one side DELETEs or SELECT FOR UPDATEs the parent row, the other inserts a child row (taking FOR KEY SHARE on the parent) and then modifies that parent	FOR UPDATE conflicts with FOR KEY SHARE (see row-level locks)	Lock the parent row first in every flow; keep transactions short
SELECT ... FOR UPDATE on a join	No control over which table gets locked first	FOR UPDATE OF <table> and lock in the agreed order

Detection

• Look for 40P01 in the app and ERROR: deadlock detected in the server log; read DETAIL to connect the processes.
• Enable log_lock_waits: PostgreSQL logs when a lock wait exceeds deadlock_timeout. It is for investigating lock waits, not a prerequisite for deadlock detection to work.
• Watch the counter:

SELECT datname, deadlocks FROM pg_stat_database WHERE datname = current_database();

The counter accumulates since the last statistics reset, so alert on its rate of increase and on the app’s retry rate, not on the absolute value.

It does not tell you which query caused the error: group the DETAIL lines by endpoint to find it. A sudden jump in deadlocks after a deploy is almost always caused by the code path that just changed.

Immediate fix

While deadlocks are happening

PostgreSQL has already broken the cycle, but the canceled request still has to be handled: ROLLBACK the session before reusing it, then retry the whole transaction with a bounded number of attempts and jittered backoff.

Check side effects outside the database before retrying: emails or payment calls do not roll back. If the error rate keeps climbing, reduce the concurrency of the contending flow and check the lock order.

Long-term prevention

Lock ordering: every flow locks the same row set in the same order. The crude approach is two SELECT ... FOR UPDATE statements in ascending id in the app. Better is a single statement:

SELECT id FROM accounts
WHERE id IN ($1, $2)
ORDER BY id
FOR UPDATE;

One statement saves a round trip and lets the database lock in sort order. Note the docs warn that under Read Committed, ORDER BY combined with a locking clause can return rows out of order if a row is modified while waiting for the lock.

Also check locks taken indirectly through triggers, foreign keys or other tables. Example 1 → 2 and 2 → 1: if the code locks “sender first, receiver second”, the two transfers take locks in opposite orders.

If both lock the smaller id first, the later transfer waits at id 1 while holding nothing, so no cycle forms.

Extra layers of protection:

Tool	Purpose
lock_timeout	Caps the wait for one lock (default 0, i.e. wait forever); set it for the app
statement_timeout	Caps the duration of the whole statement
deadlock_timeout	Raise it (default 1s) to reduce wasted checks on a very busy server, at the cost of reporting real deadlocks later. Ideally larger than a typical transaction
Bounded retry	Rerun the whole transaction on 40P01; jitter, a few attempts at most, then a clear error

A retry must restart from the first read: data read earlier may be stale. If the transaction triggers external side effects, you need idempotency.

End-of-day stock dispatch deadlocks after adding workers

A warehouse system runs batches that decrement stock for 500 orders per worker. Going from 1 to 4 workers, pg_stat_database.deadlocks jumped from 0 to dozens per hour. Cause: each worker processed orders in the order received, so two workers locked the same product in different orders.

Fix: sort line items by product_id before decrementing stock, set lock_timeout = 5s, retry at most 3 times with jitter, and alert when the retry rate crosses a threshold. The numbers are an illustrative scenario.

Production checklist

1. Every flow that locks several rows or tables follows one agreed order (sorted by key).
2. A retry wrapper for 40P01 (and 40001): rerun the whole transaction, bounded, with jitter.
3. Set lock_timeout and statement_timeout for the app, log_lock_waits = on for the server.
4. Alert on the rate of increase of pg_stat_database.deadlocks and the retry rate after each deploy.
5. Group log DETAIL lines by endpoint to find the code path forming the cycle.
6. Short transactions: no slow network calls while holding locks.
7. Side effects outside the database go through an outbox or idempotency.

Trade-offs

Lock ordering and retry

• Lock ordering eliminates the whole class of lock-order deadlocks, no retry needed.
• Bounded retry covers the rare remaining deadlocks cheaply.
• lock_timeout turns infinite waits into controlled errors.

Costs to manage

• Lock ordering must be maintained across the whole codebase; one out-of-order code path is enough to cause errors.
• Retries repeat work and can repeat side effects without idempotency.
• Raising deadlock_timeout reports real deadlocks later.

Summary

Key takeaways

• A deadlock is a closed wait cycle; PostgreSQL detects it after deadlock_timeout (1 second by default) and cancels one transaction with 40P01.
• Which transaction gets canceled is hard to predict: every flow must handle this error.
• Real deadlocks usually come from unsorted batches, two tables in opposite order and foreign keys, rarely from two manual UPDATEs.
• Prevent with lock ordering; the safety net is lock_timeout plus bounded whole-transaction retry.
• Watch the rate of increase of pg_stat_database.deadlocks and the retry rate.

References

• Deadlocks — PostgreSQL 17
• Lock Management — PostgreSQL 17
• Error Codes (Class 40) — PostgreSQL 17
• Statistics Views — PostgreSQL 17