ERROR: deadlock detected (SQLSTATE 40P01) means two transactions each held a row lock the other one needed, and PostgreSQL cancelled one of them to break the cycle. The other transaction carried on and committed. Make every transaction lock rows in the same order, and retry the cancelled one.

The cancelled transaction is rolled back as a whole, so nothing it did is kept. Retrying it is safe; changing the lock order stops it from happening again.

The error

UPDATE accounts SET balance = balance + 50 WHERE id = 2;
psql:/tmp/p6a2.sql:4: ERROR:  deadlock detected
DETAIL:  Process 207 waits for ShareLock on transaction 791; blocked by process 215.
Process 215 waits for ShareLock on transaction 790; blocked by process 207.
HINT:  See server log for query details.
CONTEXT:  while updating tuple (0,9) in relation "accounts"
COMMIT;
ROLLBACK

Why it happens

Session A updated account 1 and then wanted account 2. Session B, at the same moment, updated account 2 and then wanted account 1. Each UPDATE holds its row lock until the transaction ends, so A waited for B and B waited for A. Neither could ever finish. After waiting for deadlock_timeout (1s by default, and 1s in this container) PostgreSQL checks for such a cycle, finds it, and raises the error in one of the sessions.

The DETAIL names processes and transaction ids, not queries; the HINT sends you to the server log, which holds both statements: 

2026-11-06 07:15:06.146 UTC [151] ERROR:  deadlock detected
2026-11-06 07:15:06.146 UTC [151] DETAIL:  Process 151 waits for ShareLock on transaction 784; blocked by process 159.
	Process 159 waits for ShareLock on transaction 783; blocked by process 151.
	Process 151: UPDATE accounts SET balance = balance + 50 WHERE id = 2;
	Process 159: UPDATE accounts SET balance = balance + 20 WHERE id = 1;

That log entry is from the first of two identical runs, hence the different process numbers. Note the COMMIT after the error answering ROLLBACK: once a statement fails, the whole transaction is aborted.

The fix

Session B rewritten to touch account 1 before account 2, the same order as session A:

BEGIN;
BEGIN
UPDATE accounts SET balance = balance + 20 WHERE id = 1;
UPDATE 1
SELECT pg_sleep(2);
 pg_sleep
----------

(1 row)

UPDATE accounts SET balance = balance - 20 WHERE id = 2;
UPDATE 1
COMMIT;
COMMIT

With both sessions locking in id order, B simply waited for A to commit and then ran; both transactions committed and the balances ended at 490.00 and 310.00. In application code, sort the ids before updating several rows, keep transactions short, and catch 40P01 to retry the whole transaction. From .NET, Npgsql raises a PostgresException with SqlState 40P01.

How it was reproduced

A postgres:18 container (PostgreSQL 18.4, Debian build 18.4-1.pgdg13+1) on Docker 29.9.2, with accounts (id, owner, balance) and two fictional owners. Two real psql sessions ran at the same time through docker exec, the second started half a second after the first; each updated one row, slept two seconds with pg_sleep(2), and updated the other row. Session A received the error and session B committed. A run with \set VERBOSITY verbose printed ERROR: 40P01: deadlock detected.

Frequently asked

What does 'deadlock detected' mean in PostgreSQL?
Two transactions waited for each other's row locks. PostgreSQL detected the cycle and cancelled one transaction with SQLSTATE 40P01 so the other could finish.
How do I see which queries caused a PostgreSQL deadlock?
Look in the server log. The client message names only process and transaction ids; the log entry for the same error lists the statement each process was running.
Should I retry a transaction after a 40P01 deadlock?
Yes. The cancelled transaction was rolled back completely, so retrying the whole transaction is safe. Fix the lock order as well, or the deadlock will keep returning under load.

More decoded errors in the Fixes category. The same situation on SQL Server is error 1205, chosen as the deadlock victim.