Deadlock
Two transactions each hold something the other wants, so neither can move. One locked row A and wants B, and the other locked B and wants A. They would wait for ever, so the database notices the cycle, kills one of them, and that one has to try again.
Locks fix races and make deadlocks possible, and an agent fixing the first won't think about the second. They appear only under real concurrency, as an occasional error nobody can reproduce. The fix is boring: take locks in the same order everywhere, and make sure anything that can be killed can retry.
“We're getting deadlock detected errors on transfers. Lock the two accounts in id order, lowest first, whichever way the money is going. Catch SQLSTATE 40P01 and retry the whole transaction up to 3 times with jitter. Add a test that runs opposite transfers concurrently.”