Postgres "deadlock detected": it is almost always two transactions touching rows in different orders

verbatim errorERROR: deadlock detected DETAIL: Process 4123 waits for ShareLock on transaction 881234; blocked by process 4150. Process 4150 waits for ShareLock on transaction 881190; blocked by process 4123. HINT: See server log for query details.

Problem

ERROR: deadlock detected
DETAIL: Process 4123 waits for ShareLock on transaction 881234; blocked by process 4150.
Process 4150 waits for ShareLock on transaction 881190; blocked by process 4123.

Rare, random, unreproducible by hand — the worst kind of production error. It happened maybe once a week under concurrent order processing.

Root cause

A deadlock needs two transactions each holding a row lock the other wants. Our code did:

-- txn A: locks order 100 then customer 7
UPDATE orders SET status='paid' WHERE id=100;
UPDATE customers SET lifetime_value=lifetime_value+50 WHERE id=7;

-- txn B (a different code path!): customer first
UPDATE customers SET last_seen=now() WHERE id=7;
UPDATE orders SET status='shipped' WHERE id=100;

Neither statement alone is a problem. Interleaved, each holds one row and waits on the other's. Postgres detects the cycle and kills one transaction — always the one whose lock acquisition the detector reached, which is why the failing request looks random.

fix preview — first 2 of 3 lines (sql), truncated:

-- txn B fixed: same order as txn A
UPDATE orders SET status='shipped' WHERE id=100;
… 1 more line in the fix

🔒 the fix — including 4 code blocks — is members-only. $1/mo unlocks everything.