Database Deadlocks & Lock Ordering.

🚨The symptom

Your payments service throws this ~15 times a day, only in production, only at peak:

ERROR: deadlock detected
DETAIL: Process 4821 waits for ShareLock on transaction 99120;
        blocked by process 4977.
        Process 4977 waits for ShareLock on transaction 99118;
        blocked by process 4821.
HINT: See server log for query details.

It never reproduces locally. Retrying the request works. Tempting conclusion: "flaky DB, add a retry, move on."

🔍Step 1 — read what it actually says

Two transactions each hold a row lock the other one wants. Postgres doesn't hang forever: after deadlock_timeout (default 1s) it detects the cycle, kills the cheaper victim, and the other one commits. So it's not corruption or a bug in Postgres — it's your code asking for locks in two different orders.

🔍Step 2 — find the two paths

The transfer endpoint:

BEGIN;
UPDATE accounts SET balance = balance - 50 WHERE id = $from;  -- locks row $from
UPDATE accounts SET balance = balance + 50 WHERE id = $to;    -- locks row $to
COMMIT;

Now Ali sends money to Sara while Sara sends money to Ali, at the same second:

T1: lock A ... wants B
T2: lock B ... wants A     ← cycle

That's it. Nothing exotic. Two rows, two transactions, opposite order.

💡Step 3 — the concept: locks are held to COMMIT

An UPDATE takes a row lock and keeps it until the transaction ends — not until the statement ends. So every extra statement between the first UPDATE and COMMIT widens the window. A cycle needs only two transactions touching the same rows in different orders.

🛠Step 4 — the fix: a global lock order

Pick any total order on the rows and always take locks in it. Sorting by primary key is free and works:

lo, hi = sorted([from_id, to_id])
cur.execute("SELECT id FROM accounts WHERE id IN (%s,%s) ORDER BY id FOR UPDATE", (lo, hi))
# now both UPDATEs, in any order — the locks are already held

Both concurrent transfers now grab the lower id first. One waits, one proceeds. No cycle, ever.

⚠️Where people actually get bitten

• Multi-row UPDATE ... WHERE status = 'pending' — the lock order is the plan's row order, which can flip between an index scan and a seq scan. Add ORDER BY id and lock explicitly.
• A batch job doing DELETE ... WHERE id IN (...) with an unsorted list from a Set — sort the list.
• Foreign keys: inserting a child row takes a lock on the parent. Two transactions inserting children of two parents in opposite order deadlock without a single UPDATE in sight.
• Long transactions: an HTTP call inside BEGIN...COMMIT turns a 2ms lock window into 400ms. Do I/O outside the transaction.

🔁And still keep the retry

Ordering removes the deadlocks you designed; it can't remove every one. Retry on SQLSTATE 40P01 (Postgres) / error 1213 (MySQL) with a small jitter, max 3 attempts — but retry from the top of the transaction, not from the failed statement. The whole transaction was rolled back; re-running one UPDATE on a dead transaction just errors again.

🧪 Also useful: log_lock_waits = on plus deadlock_timeout = 1s tells you which queries were waiting, not just which pids.

✍️Your exercise

Open two psql sessions. In session 1: BEGIN; UPDATE accounts SET balance=balance WHERE id=1;

In session 2: BEGIN; UPDATE accounts SET balance=balance WHERE id=2;

Then in session 1 update id=2, and in session 2 update id=1. Watch one of them die after ~1 second, and read the DETAIL line.

Then: is a deadlock possible with only ONE row involved? Why or why not?