Locking is how MySQL keeps concurrent transactions from corrupting each other’s data — necessary, but the source of deadlocks when two transactions each hold a lock the other needs.
-- Transaction A START TRANSACTION; UPDATE accounts SET balance = balance - 100 WHERE id = 1; -- locks row 1 -- ... then tries to lock row 2 -- Transaction B (running concurrently) START TRANSACTION; UPDATE accounts SET balance = balance - 50 WHERE id = 2; -- locks row 2 -- ... then tries to lock row 1 -> DEADLOCK
Diagnosing Deadlocks
SHOW ENGINE INNODB STATUS\G -- Look for the "LATEST DETECTED DEADLOCK" section
MySQL automatically detects deadlocks and kills one transaction to break the cycle — your application code needs to catch that error and retry. The most reliable fix is ensuring all transactions lock rows in a consistent order (e.g., always by ascending ID) across your entire codebase.