Figure 1: A holds row 1 and wants row 2. B holds row 2 and wants row 1. Neither can move.
I ran 500 money transfers between 10 accounts, 20 at a time. 179 of them failed with deadlock detected, and the run took 93 seconds.
The code looked fine. Each transfer was one transaction with two UPDATEs. The problem was only the order the rows were locked in.
I will be sharing how I reproduced the deadlock with two requests, how to read the Postgres log, and the two fixes: lock ordering, and a retry on error 40P01. Everything was run on Postgres 16 in Docker with Node 20 and pg.
This article expects you to have minimum knowledge of SQL transactions, but nothing is really complicated here.
How it happens
Press enter or click to view image in full size
Figure 2: Two transfers in opposite directions. After step 4 each one waits for the other.
An UPDATE takes a row lock and keeps it until the transaction ends. Any other transaction that wants to update the same…
Your lock-ordering fix removes the cycle for the two account rows, which is the right invariant to test. I’d carry that ordering through every lock a transfer can take, including ledger or summary rows acquired by triggers; otherwise a second code…
