deadlock detected

What it means

A deadlock is two (or more) transactions each holding a lock the other needs: A waits for B, and B waits for A, so neither can ever finish. When a lock wait lasts longer than deadlock_timeout (one second by default), PostgreSQL checks for such a cycle. If it finds one, it cancels one of the transactions with this error and lets the others carry on. Which one it picks can’t be predicted.

Your transaction is now aborted: everything it did is undone when you roll back, and any further statement in it fails with current transaction is aborted. The other transaction wasn’t harmed. A deadlock is a timing problem, not bad data, so running the whole transaction again normally succeeds.

The message comes with details:

ERROR:  deadlock detected
DETAIL:  Process 279089 waits for ShareLock on transaction 4721; blocked by process 279078.
Process 279078 waits for ShareLock on transaction 4722; blocked by process 279089.
HINT:  See server log for query details.
CONTEXT:  while updating tuple (0,28) in relation "accounts"

• Process numbers are server process IDs (the pid in pg_stat_activity).
• ShareLock on transaction means “waiting for that transaction to finish”: that’s how a session waits for a row another transaction has changed or locked.
• CONTEXT names the table, and the row’s physical position ((0,28) is page 0, item 28).
• The server log has the statement each process was running.

Common causes

1. The same rows updated in different orders. One code path moves money from account 1 to 2, another from 2 to 1, at the same moment.
2. Batches that overlap. Two jobs update overlapping sets of rows; each statement locks rows in the order it happens to visit them.
3. Locks taken weak, then strengthened. Two transactions both read-lock something and then both try to upgrade it.
4. Long transactions. The longer a transaction holds locks, the more chances another one has to cross it.

How to fix it

Retry the transaction

Treat 40P01 like a serialisation failure: roll back, and run the whole transaction again, including the code that decided what to write. A few attempts with a short random pause are enough. See the retry example on could not serialize access.

Take locks in the same order everywhere

Lock every row you’ll change, in a fixed order, at the start of the transaction:

BEGIN;
SELECT id FROM accounts WHERE id IN (1, 2) ORDER BY id FOR UPDATE;
UPDATE accounts SET balance = balance - 10 WHERE id = 2;
UPDATE accounts SET balance = balance + 10 WHERE id = 1;
COMMIT;

With both transactions written this way, the second waits for the first to commit instead of deadlocking. For batches, sort the keys before you update. For tables, lock them in the same order too, and take the strongest lock you’ll need first rather than upgrading later.

Keep transactions short

Do slow work (network calls, user input, file processing) before BEGIN or after COMMIT, not while holding locks.

Find the statements involved

The server log has both queries:

ERROR:  deadlock detected
DETAIL:  Process 279089 waits for ShareLock on transaction 4721; blocked by process 279078.
	Process 279078 waits for ShareLock on transaction 4722; blocked by process 279089.
	Process 279089: UPDATE seo_pgconn.accounts SET balance = balance + 10 WHERE id = 1;
	Process 279078: UPDATE seo_pgconn.accounts SET balance = balance + 10 WHERE id = 2;

Only the statement each process was waiting on is shown, not what it ran earlier in the transaction; the earlier statements took the locks. Setting log_lock_waits = on (off by default) also logs any lock wait longer than deadlock_timeout, which shows the near misses.

Reproduce it

On PostgreSQL 18.6, with two rows in seo_pgconn.accounts, session A runs in the background:

BEGIN;
UPDATE seo_pgconn.accounts SET balance = balance - 10 WHERE id = 1;
SELECT pg_sleep(2);
UPDATE seo_pgconn.accounts SET balance = balance + 10 WHERE id = 2;
COMMIT;

A second later, session B updates the same rows in the opposite order:

BEGIN;
UPDATE seo_pgconn.accounts SET balance = balance - 10 WHERE id = 2;
UPDATE seo_pgconn.accounts SET balance = balance + 10 WHERE id = 1;
COMMIT;

Session B’s second update waits for A, then A’s second update waits for B. A second after that, session B gets:

ERROR:  deadlock detected
DETAIL:  Process 279089 waits for ShareLock on transaction 4721; blocked by process 279078.
Process 279078 waits for ShareLock on transaction 4722; blocked by process 279089.
HINT:  See server log for query details.
CONTEXT:  while updating tuple (0,28) in relation "accounts"

Its COMMIT answers ROLLBACK, because the transaction was already aborted. Session A’s update then goes through and it commits. With \set VERBOSITY verbose, psql shows the code: ERROR: 40P01: deadlock detected. PostgreSQL 14.24 gives the same message.

With both sessions starting with SELECT … ORDER BY id FOR UPDATE, session B waited about a second for A and then committed, with no error.

In Inlet

Inlet’s Activity monitor shows which session blocks which, so you can see a lock wait while it’s happening and cancel or terminate the session holding things up. When a statement fails with this error, Ask Claude (with your own Anthropic API key) can explain it and suggest a fix.