PostgreSQL Error 40P01: Deadlock detected

Identify: does your log match?

ERROR:  deadlock detected
DETAIL:  Process 48211 waits for ShareLock on transaction 9123412; blocked by process 48207.
Process 48207 waits for ShareLock on transaction 9123410; blocked by process 48211.
HINT:  See server log for query details.
CONTEXT:  while updating tuple (0,7) in relation "accounts"

1. High

   Rows locked in inconsistent order

   Transaction A updates account 1 then 2 while B updates 2 then 1. Each waits on the other forever until the detector kills one.

2. Medium

   Foreign keys take implicit share locks

   Inserting a child row takes FOR KEY SHARE on the parent. Concurrent updates of the parent and inserts of children can cycle.

3. Low

   Long transactions widen the window

   Doing HTTP calls or heavy work inside a transaction keeps locks held for seconds, making collisions likely under load.

Fix

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = $from;
UPDATE accounts SET balance = balance + 100 WHERE id = $to;
COMMIT;

BEGIN;
SELECT id FROM accounts
WHERE id IN ($from, $to)
ORDER BY id
FOR UPDATE;
UPDATE accounts SET balance = balance - 100 WHERE id = $from;
UPDATE accounts SET balance = balance + 100 WHERE id = $to;
COMMIT;

Verify: reproduce it on purpose

$ psql -c "CREATE TABLE IF NOT EXISTS t(id int PRIMARY KEY, v int); INSERT INTO t VALUES (1,0),(2,0) ON CONFLICT DO NOTHING;" && (psql -c "BEGIN; UPDATE t SET v=1 WHERE id=1; SELECT pg_sleep(1); UPDATE t SET v=1 WHERE id=2; COMMIT;" & psql -c "BEGIN; UPDATE t SET v=2 WHERE id=2; SELECT pg_sleep(1); UPDATE t SET v=2 WHERE id=1; COMMIT;"; wait)

Prevent

• Keep transactions short; no network calls inside them.
• Set log_lock_waits = on to see waits before they deadlock.
• Index foreign-key columns to shrink lock scope.