Deadlock from inconsistent lock ordering

Deadlock from inconsistent lock ordering
Type: incident simulation · Topic: Locks & Transactions · Level: L3 · Duration: 10–15 min
Launch: ride postgres start stage-02/03-deadlock

POSTMORTEM (root cause · how it was found · the fix · lesson)
Root cause: two transactions updated the same two accounts in opposite orders —
one did account 1 then 2, the other did 2 then 1. Each held one row and waited
for the other, a cycle PostgreSQL resolves by aborting one transaction with
`ERROR: deadlock detected`. The database did its job; the bug is in the
application's lock ordering.

How it was found: the deadlock error appeared in the server log; inspecting the
transactions showed the opposite update orders.

The fix: make every transaction take row locks in a consistent order — e.g.
always update accounts in ascending id order:
  BEGIN;
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
  UPDATE accounts SET balance = balance + 100 WHERE id = 2;
  COMMIT;
With a global ordering, a cycle is impossible.

Lesson: deadlocks are an application design problem, not a server tuning knob.
Acquire locks in a consistent order (and keep transactions short). Retrying the
same inconsistent order just reproduces the deadlock.

INVESTIGATION HINTS (the staged path to diagnose and fix)
1. The server log shows `ERROR: deadlock detected`. A deadlock means two transactions grabbed the same rows in opposite order and waited on each other.
2. Look at the deadlock detail: one transaction locked account 1 then wanted 2, the other locked 2 then wanted 1. Inspect pg_stat_activity / pg_locks while it reproduces.
3. The fix isn't a database setting — it's consistent lock ordering. Always update the accounts in the same order (ascending id) inside every transaction, and they can't deadlock.