Acquire related row locks in a consistent order
One transfer locks account A then account B. Another locks B then A. Each can wait for the row held by the other, creating a deadlock. Consistent lock ordering reduces this avoidable cycle.
Order by stable identity
For an operation touching several accounts, sort their IDs and lock them in that order inside the transaction. After obtaining the locks, validate current state and perform the transfer. Every code path participating in the same workflow should follow the same ordering convention.
transfer A → B: lock lower ID, then higher ID
transfer B → A: lock lower ID, then higher ID
This does not eliminate every deadlock. Index access, additional tables, and other transactions can still introduce cycles.
Keep a retry boundary
InnoDB can choose a victim and roll back work to resolve a deadlock. The application should handle the relevant failure with a bounded retry where safe. Keep nontransactional side effects outside the retried database block or protect them with a separate idempotency design.
Test competing transfers against MySQL using controlled concurrent connections. A single-threaded unit test cannot prove lock behavior. Assert the final invariant, such as the combined balance, and record that one operation may need a retry.
Investigate recurring deadlocks using the available database diagnostics and the actual queries involved. Do not merely increase every timeout; that can make users wait longer without removing the cycle.