MySQL Deadlocks: How to Diagnose and Prevent

An InnoDB deadlock is a cycle where each transaction holds a lock the other needs. InnoDB detects the cycle with its wait-for graph and rolls back the transaction with fewer modified rows. The first thing I do when a deadlock fires in production is run SHOW ENGINE INNODB STATUS and look at the LATEST DETECTED DEADLOCK section. It shows both transactions, the SQL each was running, the lock each holds and the lock each is waiting for, including the exact page, heap number, and whether it's a record lock, gap lock, or next-key lock. Reading that output carefully almost always reveals the cause. Common patterns: two transactions updating the same pair of rows in opposite orders — tx1 updates A then B, tx2 updates B then A. Fix: enforce a consistent ordering in code, e.g., always sort the target IDs ascending before updating. Another pattern: INSERT conflicting with gap locks under Repeatable Read. Transaction A does SELECT ... FOR UPDATE WHERE col > 100 which takes a gap lock on (100, +∞), transaction B tries INSERT with col = 150 and blocks on the gap lock, then B holds a lock A needs. Fix: use a narrower predicate that doesn't take a large gap lock, or lower isolation to RC (which has no gap locks for most operations), or use SELECT without FOR UPDATE. A third pattern is a missing index — a DELETE or UPDATE with no suitable index scans the whole table under lock, turning a small operation into a table-wide lock and deadlocking with anything else touching the table. Always ensure UPDATEs/DELETEs use an index. Prevention beyond these: keep transactions short — ideally a transaction holds locks for tens of milliseconds, not seconds; never do network I/O (external API calls, queue publishes) inside a DB transaction; set innodb_lock_wait_timeout (default 50s, I usually set 3–5s to fail fast); and in application code, wrap writes in a retry loop catching deadlock errors because occasional deadlocks are unavoidable in high-concurrency systems. For chronic deadlock hotspots, the real fix is often a schema or ordering change, not more retries.