- SQLSTATE
- 40P01
- Condition name
deadlock_detected- Product
- PostgreSQL
- Versions
- PostgreSQL 13 to 18
- Category
- Transactions & Concurrency
- Last reviewed
- 24 October 2026
On this page
What it means
SQLSTATE 40P01 means two or more transactions were each waiting for a lock the other held, so PostgreSQL aborted one of them to let the others finish.
A deadlock happens when transaction one holds a lock that transaction two needs, while transaction two holds a lock that transaction one needs. Neither can continue, so waiting would last for ever. When a lock wait has lasted deadlock_timeout (one second by default), PostgreSQL checks for such a cycle; if it finds one, it cancels one of the transactions with 40P01 (deadlock_detected) and the others carry on. Which transaction is chosen is, in the words of the documentation, difficult to predict and should not be relied upon.
The DETAIL line lists each process in the cycle, the lock mode it waits for and what it waits on: ShareLock on transaction ... means it waits for another transaction to finish because that transaction has locked a row it wants to change. The HINT points to the server log, which repeats the DETAIL and adds the query each process was running. The aborted transaction is rolled back as a whole; until you ROLLBACK, later statements in it fail with 25P02. The PostgreSQL documentation lists 40P01 among the errors it may be advisable to retry.
Deadlocks need at least two sessions at the same time, so they appear under concurrent load, often only in production. See transactions, locks and deadlocks for the background.
Common causes
- Two transactions update the same rows in a different order, for example a transfer from account A to B running at the same time as one from B to A.
- Batch jobs that update many rows without an ORDER BY, so two batches lock overlapping rows in different orders.
- A transaction takes a weak table lock (for example LOCK TABLE ... IN SHARE MODE) and later needs a stronger one, while another transaction does the same.
- SELECT ... FOR UPDATE on rows in one order and UPDATE in another, or triggers that change other tables in an order the application does not expect.
- Long transactions that hold row locks while the application does other work, which widens the window for a crossing lock order.
Diagnose it
Read-only These queries only read the system catalogs (pg_catalog, information_schema) and statistics views such as pg_stat_activity and pg_locks. They do not change data, roles or settings, so they are safe to run first.
Read the server log entry for the deadlock
The server log has the same DETAIL as the client plus one line per process with the query it was running (Process 12345: UPDATE ...). Those queries show which statements cross each other. Where the log is depends on log_destination and logging_collector; on managed services it is in the provider's log viewer.
Which sessions are blocked right now, and by whom?
Run this while the problem is happening. pg_blocking_pids returns the processes holding the locks each session waits for.
SELECT pid,
pg_blocking_pids(pid) AS blocked_by,
wait_event_type,
wait_event,
now() - xact_start AS xact_age,
left(query, 60) AS query
FROM pg_stat_activity
WHERE cardinality(pg_blocking_pids(pid)) > 0;No rows when nothing is waiting. A wait that resolves on its own is ordinary blocking; a deadlock never shows here for long, because PostgreSQL breaks it after deadlock_timeout.
How often does it happen?
SELECT datname, deadlocks
FROM pg_stat_database
WHERE datname = current_database();The number of deadlocks detected in this database since the statistics were last reset. Note the value and compare it later to see whether a change helped.
Check the deadlock and lock wait settings
SELECT name, setting, unit
FROM pg_settings
WHERE name IN ('deadlock_timeout', 'log_lock_waits');deadlock_timeout is how long a lock wait lasts before the deadlock check runs (default 1s). With log_lock_waits on, long waits are also written to the log, which helps find the locks that cross.
How to fix it
Each fix is labelled with what it changes. Try read-only checks and the least invasive fix first, and test changes outside production.
Retry the whole transaction
Client or network sideThe aborted transaction did nothing, so it is safe to run it again from the start. Catch SQLSTATE 40P01 in the application, roll back, wait a short random time and retry the complete transaction (not just the failed statement), with a limit on the number of attempts. Retrying fixes occasional deadlocks; frequent ones need the lock order fixed.
Lock rows in one consistent order
Changes dataThe documentation's main advice: make every transaction acquire locks on several objects in the same order. Locking the rows up front, sorted by key, means two transfers between the same accounts queue behind each other instead of crossing.
BEGIN;
SELECT acctnum
FROM accounts
WHERE acctnum IN (11111, 22222)
ORDER BY acctnum
FOR UPDATE;
UPDATE accounts SET balance = balance - 100.00 WHERE acctnum = 22222;
UPDATE accounts SET balance = balance + 100.00 WHERE acctnum = 11111;
COMMIT;Take the strongest table lock first
Changes dataIf a transaction locks a table and then changes it, take a mode that already covers the change. The LOCK documentation recommends SHARE ROW EXCLUSIVE instead of SHARE for this pattern, so only one such transaction runs at a time instead of two deadlocking on the upgrade.
BEGIN;
LOCK TABLE films IN SHARE ROW EXCLUSIVE MODE;
UPDATE films SET kind = 'Drama' WHERE code = 'UA502';
COMMIT;Keep transactions short
Client or network sideDo not hold a transaction open while waiting for user input or calling other services. The fewer locks a transaction holds and the shorter it holds them, the smaller the chance of a crossing lock order. Split large batch updates into smaller transactions that each process keys in sorted order.
Examples
Two transfers that update the same rows in opposite order
The statements are the deadlock example from the PostgreSQL documentation (Explicit Locking, Deadlocks). The documentation describes the outcome but does not print the message, so the text is quoted from the PostgreSQL 18.6 source code; the process numbers, transaction ids and tuple position are illustrative. Which session receives the error can vary. This example needs a running PostgreSQL server (two concurrent sessions), which PGlite does not provide, so it was not reproduced here.
-- Session 1
BEGIN;
UPDATE accounts SET balance = balance + 100.00 WHERE acctnum = 11111;
-- Session 2
BEGIN;
UPDATE accounts SET balance = balance + 100.00 WHERE acctnum = 22222;
UPDATE accounts SET balance = balance - 100.00 WHERE acctnum = 11111; -- waits for session 1
-- Session 1
UPDATE accounts SET balance = balance - 100.00 WHERE acctnum = 22222; -- deadlockERROR: deadlock detected DETAIL: Process 12345 waits for ShareLock on transaction 1002; blocked by process 12346. Process 12346 waits for ShareLock on transaction 1001; blocked by process 12345. HINT: See server log for query details. CONTEXT: while updating tuple (0,2) in relation "accounts"
Two transactions upgrading a SHARE table lock
The LOCK documentation describes this case: both transactions hold SHARE mode and neither can then get ROW EXCLUSIVE mode for its UPDATE. The message text is quoted from the PostgreSQL 18.6 source code; the process numbers and OIDs are illustrative. This example needs a running PostgreSQL server (two concurrent sessions), which PGlite does not provide, so it was not reproduced here.
-- Session 1
BEGIN;
LOCK TABLE films IN SHARE MODE;
-- Session 2
BEGIN;
LOCK TABLE films IN SHARE MODE;
UPDATE films SET kind = 'Drama' WHERE code = 'UA502'; -- waits for session 1
-- Session 1
UPDATE films SET kind = 'Comedy' WHERE code = 'UA503'; -- deadlockERROR: deadlock detected DETAIL: Process 12345 waits for RowExclusiveLock on relation 16384 of database 5; blocked by process 12346. Process 12346 waits for RowExclusiveLock on relation 16384 of database 5; blocked by process 12345. HINT: See server log for query details.
How to prevent it
Agree on one lock order for every code path that changes several rows or tables (for example, always by primary key, parent before child) and lock up front with SELECT ... ORDER BY key FOR UPDATE when a transaction will change several rows. Take the strongest table lock you will need first, and keep transactions short.
Treat 40P01 as a retryable error in the data access layer, together with 40001, and log each occurrence with the DETAIL so repeated deadlocks between the same statements are noticed and fixed rather than retried for ever.
Common questions
How do I fix "deadlock detected" in PostgreSQL?
Find the two statements in the server log entry for the deadlock, then change the code so both transactions lock the rows or tables in the same order. Meanwhile, catch SQLSTATE 40P01 and retry the whole transaction, which is safe because the aborted transaction was rolled back.
Which transaction does PostgreSQL abort in a deadlock?
One of the transactions in the cycle, usually the one whose deadlock check found it. The documentation says exactly which one is difficult to predict and should not be relied upon, so every transaction that can take part must be ready to retry.
Should I raise deadlock_timeout?
It does not prevent deadlocks; it only sets how long a lock wait lasts before the check runs. A higher value means fewer checks on a busy server but a later error for real deadlocks. Fix the lock order instead.
What is the difference between a deadlock and a lock wait?
A lock wait ends when the other transaction commits or rolls back. A deadlock can never end on its own because each side waits for the other, so PostgreSQL ends it by aborting one transaction. Long ordinary waits can be limited with lock_timeout, which raises a different error (55P03).
Learn more on CodeWithSQL
Official documentation
- Explicit locking: deadlocks
- Serialization failure handling (retrying 40001 and 40P01)
- Lock management settings (deadlock_timeout)
- LOCK
Last technically reviewed 24 October 2026; examples and diagnostics run on PostgreSQL 18.6 documentation; SQL-level parts in PGlite 0.5.11. SQL-level examples were run in PGlite (PostgreSQL compiled to WebAssembly); examples that need a running server are shown as documented in the PostgreSQL documentation.