TIL: deadlocks — the fix is lock order, not speed

Ana grabs the frying pan off the stove and starts on her sauce. She'll need the wooden spoon in a second.

Across the kitchen, Ben grabs the wooden spoon for his own dish. He'll need the pan next.

"Hey, pass the spoon when you're done?" Ana says. "Sure — soon as you're done with the pan," Ben says.

Neither moves. Ana's holding the pan, waiting on the spoon. Ben's holding the spoon, waiting on the pan. Neither one is willing to put theirs down first, because putting it down means losing their spot mid-recipe.

A minute passes. Their roommate walks in, clocks the standoff immediately, and says: "Ana, put the pan down. Ben, go ahead." Ana sighs, sets the pan aside, and restarts her sauce a minute later once Ben's finished with both.

That standoff is a deadlock, and the roommate is exactly what Postgres runs internally to break one.

Mechanism, in the real vocabulary — two rows in an accounts table:

-- T1                                    -- T2
BEGIN;
UPDATE accounts SET balance = 400
  WHERE id = 1;      -- locks row 1
                                          BEGIN;
                                          UPDATE accounts SET balance = 100
                                            WHERE id = 2;      -- locks row 2
UPDATE accounts SET balance = balance - 100
  WHERE id = 2;      -- blocks, waits on T2
                                          UPDATE accounts SET balance = balance + 100
                                            WHERE id = 1;      -- blocks, waits on T1

Both statements are now frozen mid-execution — neither transaction can even reach COMMIT, because the blocked UPDATE never returns. Ana holding the pan while blocked on the spoon is T1 holding row 1's lock while blocked on row 2.

Postgres doesn't let this sit forever. Once a backend has waited past deadlock_timeout (1 second by default), it checks the waits-for graph — an edge from T1 to T2 if T1 is blocked on a lock T2 holds. Here: T1 → T2 → T1. That's a cycle, which means it's a real deadlock, not just a slow lock.

Postgres picks a victim — the transaction that ran the check aborts itself with ERROR: deadlock detected, releasing its locks. The survivor proceeds and commits. That's the roommate telling Ana, not Ben, to stand down.

The aborted transaction wasn't wrong — it just lost a coin flip on timing. So the fix on the app side isn't to surface the error to the user; it's to catch it and retry the transaction, same as any other transient conflict.

The structural fix is upstream of ever hitting this: have every transaction touch shared rows in the same order. Take a transferMoney(fromAccountId, toAccountId, amount) function — the naive version locks whichever account is from, then whichever is to:

BEGIN;
SELECT * FROM accounts WHERE id = fromAccountId FOR UPDATE;
SELECT * FROM accounts WHERE id = toAccountId FOR UPDATE;
-- ...balance updates...
COMMIT;

That deadlocks the moment User A pays User B while User B pays User A at the same instant — one call locks account 5 then wants 2, the other locks 2 then wants 5. Classic crossed order.

The fix isn't to lock fromAccountId and toAccountId as a pair and hope for the best — WHERE id IN (fromAccountId, toAccountId) alone doesn't guarantee which one gets locked first, it just happens to work until it doesn't. The actual guarantee comes from an explicit order:

SELECT * FROM accounts
WHERE id IN (fromAccountId, toAccountId)
ORDER BY id
FOR UPDATE;

Postgres locks FOR UPDATE rows in the order they're returned, so ORDER BY id means the lower id is always locked first — regardless of which account is from and which is to, regardless of which of the two concurrent calls got there first. No cycle can form if nobody ever acquires locks in reverse order of someone else.

One more shape worth checking: what if two transactions share only one row instead of two? Say T1 touches ids {1, 2, 3} and T2 touches ids {3, 4, 5} — the only overlap is id 3. Whichever transaction reaches id 3 first locks it; the other blocks and waits its turn. That's ordinary contention, not a deadlock — a cycle needs T1 waiting on something T2 holds and T2 waiting on something T1 holds, at the same time. With only one shared row, there's nothing left for the winner to wait on, so it just finishes and releases the lock. A deadlock needs at least two rows in common, acquired in opposite order — one shared row just means someone waits in line.

A few other practices help too, beyond just lock order:

• Retry on deadlock. The aborted transaction wasn't wrong, it just lost the timing coin flip — catch deadlock detected and retry.
• Keep transactions short. If transferMoney calls out to a slow fraud API before committing, every transfer holds its locks for however long that call takes. At 100 transfers/second, a 5ms transaction has ~0.5 overlapping in flight at any instant; a 3-second one has ~300 overlapping — a 600x wider window for two of them to collide on the same rows, even with perfect ordering. Do slow or external work before BEGIN or after COMMIT, never while holding a lock.
• Lock only what you need. A narrow WHERE that touches exactly the rows being updated, not a broad range "just in case," shrinks both the blocking radius and the deadlock surface.
• Set a lock_timeout. The deadlock detector only fires for genuine cycles — an ordinary wait (like the single-shared-row case above) can still hang indefinitely if the lock holder never commits. lock_timeout caps how long a statement waits before erroring out, so a stuck transaction fails fast instead of queuing up waiters behind it.

Related: pessimistic vs optimistic locking covers the single-row locking FOR UPDATE uses here; MVCC — why reads don't block writes covers why the plain reads in that same table never contend in the first place.