PostgreSQL error: deadlock detected

SQLSTATE 40P01 (deadlock_detected) · Updated July 9, 2026

ERROR:  deadlock detected
DETAIL:  Process 18461 waits for ShareLock on transaction 1064; blocked by process 18463.
Process 18463 waits for ShareLock on transaction 1063; blocked by process 18461.
HINT:  See server log for query details.
CONTEXT:  while updating tuple (0,3) in relation "accounts"

Two (or more) transactions are each waiting for a lock the other one holds. Neither can ever proceed, so PostgreSQL detects the cycle, picks one transaction as the victim and aborts it with this error. The other transaction continues normally.

What this error means

Row-level locks in PostgreSQL are held until the transaction commits or rolls back. If transaction A locks row 1 and then wants row 2, while transaction B holds row 2 and wants row 1, they form a cycle: a deadlock. When a backend has been waiting on a lock for longer than deadlock_timeout (default: 1 second), PostgreSQL runs a deadlock check; if it finds a cycle, it aborts one of the participants with SQLSTATE 40001's sibling 40P01.

Important framing: the database has already resolved the situation by the time you see the error. Nothing is corrupted and the surviving transaction completed its lock wait. What's left is an application problem — the aborted transaction was rolled back and its work is gone, so your application must be prepared to retry it.

Common causes

• Updating the same rows in different orders. The classic: job A updates account 1 then account 2, job B updates account 2 then account 1. Any two multi-row writers without an agreed ordering can deadlock.
• Touching tables in different orders across code paths — e.g. one path writes orders then inventory, another writes inventory then orders.
• Foreign keys. Inserting or updating a child row takes a share lock on the referenced parent row. Mixed with concurrent updates of the parent, this produces deadlocks that look mysterious because no query mentions both tables.
• Lock upgrades: reading a row with SELECT ... FOR SHARE and later updating it lets two transactions acquire the share lock and then block each other on the upgrade.
• Long transactions don't cause deadlocks by themselves, but they hold locks longer and widen the window for every pattern above.

How to diagnose it

The error itself carries most of what you need:

• DETAIL lists the process IDs and which transaction each one was waiting for.
• CONTEXT often names the tuple and relation being written.
• The server log at that timestamp records the queries involved — that's what the HINT points to.

Set log_lock_waits = on to also log any lock wait longer than deadlock_timeout, which shows you the near-misses, not just the collisions. For live investigation of who blocks whom:

SELECT waiting.pid  AS waiting_pid,  waiting.query  AS waiting_query,
       blocking.pid AS blocking_pid, blocking.query AS blocking_query
FROM pg_stat_activity waiting
JOIN pg_stat_activity blocking
  ON blocking.pid = ANY (pg_blocking_pids(waiting.pid));

How to fix it

• Agree on a global lock order. When a transaction writes multiple rows, acquire the locks in a deterministic order first:

  SELECT id FROM accounts
  WHERE id IN (7, 3, 42)
  ORDER BY id
  FOR UPDATE;   -- poi gli UPDATE, in qualsiasi ordine

• Touch tables in the same order everywhere (document the order; it doesn't matter which one, only that it's consistent).
• Keep transactions short. Never wait on user input, HTTP calls or queues while holding row locks.
• Retry on SQLSTATE 40P01 — the whole transaction, not just the failed statement, with a small backoff and a bounded number of attempts.
• Where possible, collapse read-modify-write into a single statement (UPDATE ... SET x = x - 1 WHERE ...): one statement acquires its locks in one pass and is much harder to deadlock.