// PostgreSQL error reference

PostgreSQL 40P01: deadlock detected

Two or more transactions were waiting on each other's locks. PostgreSQL rolled one back so the other could finish; retry the one that failed.

SQLSTATE
40P01
Name
deadlock_detected
Category
Transaction Rollback
Error message
deadlock detected
DETAIL:  Process 1234 waits for ShareLock on transaction 5678; blocked by process 4321.
Process 4321 waits for ShareLock on transaction 8765; blocked by process 1234.

What it means

A deadlock is a cycle of lock waits: transaction A holds a lock B needs while waiting for a lock B holds. Neither can ever proceed, so when a lock wait has lasted deadlock_timeout (1 second by default) PostgreSQL checks for a cycle and, if it finds one, aborts one of the transactions with 40P01.

The aborted transaction is rolled back completely. The other one carries on. The DETAIL lines show which processes were involved and the CONTEXT line which statement was waiting.

Common causes

  • Two transactions update the same rows in a different order, for example one transfers money from account 1 to 2 while another goes from 2 to 1.
  • Foreign keys: inserting child rows takes a FOR KEY SHARE lock on the parent row, which can conflict with another transaction updating that parent.
  • Long transactions that touch many rows, which widen the window for an overlapping lock order.
  • Explicit LOCK TABLE or SELECT ... FOR UPDATE taken in different orders in different code paths.

How to fix it

  1. Retry the failed transaction. A deadlock is a normal concurrency outcome, and the application should catch SQLSTATE 40P01 (and 40001) and run the whole transaction again.

  2. Take locks in a consistent order everywhere, for example always lock the lower id first.

    BEGIN;
    SELECT * FROM accounts WHERE id IN (1, 2) ORDER BY id FOR UPDATE;
    UPDATE accounts SET balance = balance - 100 WHERE id = 1;
    UPDATE accounts SET balance = balance + 100 WHERE id = 2;
    COMMIT;
  3. Keep transactions short: do the reads and computation first, then the writes, and commit promptly.

  4. Turn on log_lock_waits so waits longer than deadlock_timeout are logged with the statements involved, then look at the server log.

    ALTER SYSTEM SET log_lock_waits = on;
    SELECT pg_reload_conf();

Reproduce it safely

Run the steps in two separate connections, in order, on a scratch database.

-- Session 1:
CREATE TABLE dl (id int PRIMARY KEY, v int);
INSERT INTO dl VALUES (1, 0), (2, 0);
BEGIN; UPDATE dl SET v = 1 WHERE id = 1;
-- Session 2:
BEGIN; UPDATE dl SET v = 2 WHERE id = 2;
UPDATE dl SET v = 2 WHERE id = 1;   -- waits
-- Session 1:
UPDATE dl SET v = 1 WHERE id = 2;   -- one session gets ERROR: deadlock detected

Questions

Which transaction does PostgreSQL abort in a deadlock?

The one whose deadlock check found the cycle, which is not predictable in general. Write every transaction so that it can be retried.

Related errors

The code and message text are from the PostgreSQL documentation, Appendix A: PostgreSQL Error Codes. Browse the full PostgreSQL error list or search every database error.

// fewer errors, faster fixes

Work on PostgreSQL in SQLly

A native SQL client for macOS, Windows and Linux with schema-aware IntelliSense, guardrails for risky statements and clear error messages, for PostgreSQL and the other databases you use.