Order Row Locks to Prevent Deadlocks
Acquire locks in one deterministic order across every writer, add a lock timeout, and prove with a concurrency test that 40P01 stops happening.
Task
Eliminate deadlocks between concurrent transfers that lock the same two account rows in opposite order.
When to use this
Two people transfer money in opposite directions between the same accounts. Each transaction locks its own row and then waits for the other; PostgreSQL kills one with 40P01 and a random user sees a failure.
Prerequisites
- PostgreSQL 13 or newer
- Two psql sessions for reproducing the deadlock
- A shared data-access helper that all writers use
Steps
1. Reproduce the cycle: hold row 1 in the first session while the second locks row 2 and then requests row 1.
psql -d appdb -c 'BEGIN; SELECT id FROM accounts WHERE id = 1 FOR UPDATE; SELECT pg_sleep(5);' & sleep 1; psql -d appdb -c 'BEGIN; SELECT id FROM accounts WHERE id = 2 FOR UPDATE; SELECT id FROM accounts WHERE id = 1 FOR UPDATE;'Expected output: One of the two sessions reports ERROR: deadlock detected (SQLSTATE 40P01) after about a second.
2. Lock rows sequentially in a globally agreed order derived from the row keys.
const ids = [fromId, toId].sort((a, b) => a - b);
for (const id of ids) {
await client.query('SELECT id FROM accounts WHERE id = $1 FOR UPDATE', [id]);
}
Expected output: Every transfer requests the lower id first, so the second transaction waits instead of forming a cycle.
3. Bound the wait with a transaction-local lock timeout so blocked writers fail fast and can be retried.
SET LOCAL lock_timeout = '2s';
SELECT id FROM accounts WHERE id = 1 FOR UPDATE;
Expected output: A blocked transfer aborts with SQLSTATE 55P03 after about two seconds instead of waiting indefinitely.
4. Run a concurrency test in both directions and assert no deadlock occurs.
const codes = [];
await Promise.all(Array.from({ length: 40 }, (_, i) =>
transfer(i % 2 ? 1 : 2, i % 2 ? 2 : 1, 1).catch((e) => codes.push(e.code))));
assert.equal(codes.filter((c) => c === '40P01').length, 0);
Expected output: Zero deadlocks, all transfers commit, and the total account balance is unchanged.
Troubleshooting
Problem
Deadlocks return when a nightly batch job runs
Solution
Every writer must follow the same order, including batch jobs, admin scripts, and importers. Put the ordering inside the shared repository helper rather than in each caller.
Problem
The deadlock involves a row the transaction never locked explicitly
Solution
Foreign key inserts take KEY SHARE locks on parent rows. Include those parents in the ordering or avoid concurrent inserts that touch the same parents.
Problem
Throughput drops because transactions queue behind each other
Solution
Shorten the transaction and never hold locks during network calls; serialize only the rows that must be consistent.
Limitations
- Ordering only helps writers that follow the convention; enforce it centrally and review raw SQL.
- Locking earlier increases contention, so keep transactions minimal and avoid user think-time inside them.
- It does not prevent deadlocks caused by concurrent DDL or internal index page splits.
Deadlocks are cycles, not slow queries
PostgreSQL detects a wait-for cycle and aborts one transaction. The classic shape is two transactions locking rows A and B in opposite order. Neither is wrong on its own; the pair creates a cycle. Retries help, but they hide the design problem and waste work, because the cycle will recur every time the timing lines up.
Impose a total order
Pick an order that every writer can compute from the row itself, usually the primary key ascending. Then lock the rows sequentially in that order. A single statement with ORDER BY ... FOR UPDATE looks tempting but the planner does not guarantee the physical locking sequence, so sequential statements are easier to reason about. Apply the same rule to every table you lock, and give multi-table operations a fixed table order too.
const ids = [fromId, toId].sort((a, b) => a - b);
for (const id of ids) {
await client.query('SELECT id FROM accounts WHERE id = $1 FOR UPDATE', [id]);
}Add a timeout and a retry
Even a correct order can block behind a long transaction. SET LOCAL lock_timeout = '2s' turns an unbounded wait into a bounded failure that the request layer can retry. Map 55P03 to a retryable response, and keep “waiting on a lock” visible as a metric: a rising rate means a transaction is holding locks too long, not that the timeout is too short.
Watch implicit locks
Foreign keys take KEY SHARE locks on referenced rows. An insert into payments that references accounts can block a transfer that locks the same account, and vice versa. If deadlocks involve rows nobody locked explicitly, look at foreign keys, SELECT FOR UPDATE inside triggers, and ON CONFLICT paths. Include those rows in the ordering or reduce the transaction scope.
Prove it with a test
Two psql sessions reproduce the bug once, but only a repeated concurrency test proves the fix. Run dozens of transfers in both directions against a test database and assert that no request fails with 40P01. Also assert the invariant, such as the sum of balances, to catch a “fix” that silently skips a transfer.