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
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 SHARElock 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 TABLEorSELECT ... FOR UPDATEtaken in different orders in different code paths.
How to fix it
-
Retry the failed transaction. A deadlock is a normal concurrency outcome, and the application should catch SQLSTATE
40P01(and40001) and run the whole transaction again. -
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;
-
Keep transactions short: do the reads and computation first, then the writes, and commit promptly.
-
Turn on
log_lock_waitsso waits longer thandeadlock_timeoutare 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
40001could not serialize access55P03lock not available25P02current transaction is aborted1205SQL Server: Deadlocked and chosen as the victim1213MySQL: Deadlock found when trying to get lockORA-00060Oracle: deadlock detected while waiting for resource
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.
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.