T1 moves money from account 1 to account 2. T2, at the same moment, moves money from 2 to 1. Each one has to update both rows.
Two transactions, two rows, and a wait that can only end with one of them being killed. Slowed down so you can watch the cycle close.
Scroll to advance. The figure keeps running while you read.
T1 moves money from account 1 to account 2. T2, at the same moment, moves money from 2 to 1. Each one has to update both rows.
T1 updates row 1 and takes its lock. T2 updates row 2 and takes that one. A row lock is held until the transaction ends. Nobody is waiting yet.
T1 now wants row 2, which belongs to T2. So T1 waits. This is normal: it happens constantly and ends as soon as T2 commits.
T2 now wants row 1, which belongs to T1. Each one is waiting for the other to finish, and neither can. Without help, this wait never ends.
Postgres doesn't check for cycles on every wait, because that would be expensive. After deadlock_timeout, one second by default, the waiting backend walks the graph of who waits for whom. If it finds a loop, it aborts itself.
One transaction gets ERROR: deadlock detected and rolls back. The other carries on. The application sees SQLSTATE 40P01 and should retry the whole transaction, not just the last statement.
If every transaction locks rows in the same order, a loop cannot form. Update the lower account id first, or lock both up front with SELECT … FOR UPDATE ORDER BY id.
Ordering needs you to know every row in advance, and sometimes you don't. Then keep transactions short and retry on 40P01. A rare deadlock is survivable. Long lock waits under load are what hurt.