Interpreting the Latest Detected Deadlock in InnoDB
When two or more InnoDB transactions each hold a lock that the other needs, MySQL automatically detects the circular dependency and rolls back the transaction with the least undo log data — the so-called deadlock victim. The LATEST DETECTED DEADLOCK section of SHOW ENGINE INNODB STATUS records every detail of that event: transaction IDs, the exact rows locked, the lock modes held and waited for, and the rolled-back query. Mastering this output is the fastest path from a "Deadlock found when trying to get lock" error to a permanent fix. This guide walks you through every line of the deadlock output, explains the root causes behind the most common InnoDB deadlock patterns, and gives you actionable strategies to prevent recurrence.
Understanding the Deadlock Output Structure
Transaction Victim Selection and Lock Chains
InnoDB selects the deadlock victim by comparing the undo log volume of each involved transaction: the transaction that has modified fewer rows is chosen as the victim and rolled back, because undoing its work is cheaper. The deadlock output prints "*** WE ROLL BACK TRANSACTION (N)" to identify the victim. The surviving transaction automatically receives all locks it was waiting for and continues execution. Understanding victim selection helps you design retry logic: if your application always performs lightweight read-before-write operations first, those short transactions become victims more often, simplifying rollback handling. You can also influence victim selection with SET innodb_deadlock_detect=ON (the default) or by using the innodb_lock_wait_timeout variable as a coarser fallback mechanism.
Reading Held and Waiting Lock Details
Each transaction block in the deadlock output lists "HOLDS THE LOCK(S)" followed by the lock mode and "WAITING FOR THIS LOCK TO BE GRANTED" with the blocking record. Lock modes appear as combinations of S (shared), X (exclusive), GAP, INSERT INTENTION, and REC NOT GAP — see the quick-reference table below. The hex dump below each lock line (e.g. 000000000000000F) shows the index record in hexadecimal; convert to decimal to obtain the primary key (0x000F = 15). pt-deadlock-logger from Percona Toolkit automates this parsing and stores deadlocks with human-readable key values for historical analysis. Always read both transaction blocks together to reconstruct the full lock chain.
| Lock Mode | Meaning | Common Cause |
|---|---|---|
| S | Shared (Read) | Foreign key checks, SELECT … LOCK IN SHARE MODE |
| X | Exclusive (Write) | UPDATE, DELETE, SELECT … FOR UPDATE |
| GAP | Gap between index keys | REPEATABLE READ isolation level, range queries |
| INSERT INTENTION | Intent to insert into a gap | Concurrent INSERTs into the same key range |
| REC NOT GAP | Record lock only, no gap | READ COMMITTED isolation level |
Root Causes of InnoDB Deadlocks
Gap Locks vs Next-Key Locks in Concurrent Transactions
Gap locks protect ranges between index values, preventing phantom reads under the REPEATABLE READ isolation level. A next-key lock combines a gap lock on the interval preceding a record with a record lock on the record itself. Deadlocks involving gap locks frequently occur during concurrent INSERT and UPDATE on adjacent key ranges, because both transactions acquire overlapping next-key locks and then each needs the other's gap. Switching to READ COMMITTED eliminates most gap lock-based deadlocks by restricting InnoDB to record locks only. For tables with integer primary keys and monotonically increasing INSERT workloads, setting innodb_autoinc_lock_mode=2 (interleaved) also reduces AUTO_INCREMENT table-level lock contention that can trigger similar patterns.
Foreign Key Constraint Locks and Index Design
InnoDB enforces referential integrity by acquiring shared locks on parent rows whenever a child row is inserted or updated — even when foreign_key_checks=ON is set at the session level. This hidden locking behavior surprises many developers: a simple INSERT into a child table silently takes an S lock on the parent, which can deadlock with another transaction holding an X lock on that same parent row for an unrelated UPDATE. To diagnose FK-related deadlocks, look for "TABLE LOCK table `db`.`parent` trx id ... lock mode S" lines in the deadlock output. Reducing the scope of FK indexes by ensuring parent columns are covered by the narrowest possible index minimises the number of rows locked per FK check and reduces deadlock probability significantly.
Resolving and Preventing Deadlocks
Enabling innodb_print_all_deadlocks for Historical Analysis
By default, SHOW ENGINE INNODB STATUS retains only the single most recent deadlock — if two deadlocks occur one second apart, the earlier one is lost forever. SET GLOBAL innodb_print_all_deadlocks = ON causes InnoDB to write every deadlock with full lock chain detail to the MySQL error log, enabling historical pattern analysis. The setting is dynamic and takes effect immediately without a restart. Pair it with a log rotation strategy (logrotate or MySQL's log_error_services) to prevent unbounded log growth on systems with frequent deadlocks.
Application-Level Retry Logic and Transaction Ordering
The only truly safe response to an InnoDB deadlock at the application level is to catch the "Error 1213: Deadlock found" error and unconditionally retry the entire transaction from scratch. Never attempt to retry only the failed statement — partial transaction state leads to data inconsistency. Implement exponential backoff with jitter (e.g., 50 ms × 2^attempt + random 0–50 ms) to avoid thundering-herd retry storms. Beyond retry logic, reordering DML operations so that all transactions always lock rows in the same global order eliminates circular lock chains by construction. For example, if transaction A always updates table orders before order_items and transaction B does the same, the two can never deadlock on those tables.
Index Optimization to Minimize Lock Scope
Covering indexes dramatically reduce deadlock frequency by eliminating the secondary-index-to-clustered-index lookup ("bookmark lookup") that doubles the number of locks acquired per row read. When a SELECT … FOR UPDATE uses a covering index, InnoDB locks only the secondary index entries rather than also locking the corresponding clustered index records. Composite indexes on (status, created_at) or (user_id, order_id) on frequently joined columns can reduce the row range scanned — and therefore the lock footprint — by several orders of magnitude. Run EXPLAIN on the queries involved in your deadlock to verify index usage, and use the optimizer hints USE INDEX or FORCE INDEX to ensure the narrower index path is always chosen in high-concurrency code paths.