Read the deadlock report (SHOW ENGINE INNODB STATUS, SQL Server deadlock graph/XEvents, Postgres log) to see which transactions and locks formed the cycle. The usual cause is two transactions locking the same rows in opposite order. Fix by enforcing a consistent access order, shortening transactions, indexing to lock fewer rows, and retrying the victim.
Detect deadlocks from the engine's logs/tools, then fix the ordering that causes them.
Read the deadlock report:
SHOW ENGINE INNODB STATUS -> 'LATEST DETECTED DEADLOCK' shows both transactions, the locks held/waited, and the rolled-back victim. Enable innodb_print_all_deadlocks to log every one.system_health session) or trace flag 1222.A typical culprit:
-- T1: UPDATE accounts SET bal=bal-10 WHERE id=1; then id=2
-- T2: UPDATE accounts SET bal=bal-10 WHERE id=2; then id=1
They lock rows in opposite order and deadlock. : impose a consistent order, e.g., always update rows in ascending id order:
UPDATE accounts SET bal = bal - 10
WHERE id IN (1,2) ORDER BY id; -- or issue single-id updates sorted by id
Also keep transactions short, add indexes so statements lock fewer rows, and wrap the operation in retry-with-backoff since the victim's abort is expected and safe to re-run.