The first time two of my application servers deadlocked while inserting different rows into the same table, I stared at the deadlock report for an hour before it clicked. Different rows. Different values. No UPDATE in sight. It looked impossible, it was completely deterministic, and the explanation is one of the least intuitive parts of InnoDB: under REPEATABLE READ, InnoDB does not just lock rows. It locks the spaces between rows.
Once you see gap locks and next-key locks for what they are, a correctness mechanism rather than a bug, the deadlocks stop being surprising and start being something you can design around. This is the mental model I use, plus how to read the evidence when it happens to you.
Record, gap, and next-key locks
Start with what REPEATABLE READ promises: within a transaction, repeated reads return the same rows. Preventing phantoms, new rows appearing mid-transaction in a range you already read, requires locking more than the rows that exist, and here is the qualification most explanations skip: this machinery is for statements that lock. An ordinary SELECT under REPEATABLE READ is a consistent nonlocking read, served from a snapshot, and takes no record or gap locks at all. The locks engage for locking reads, SELECT ... FOR UPDATE and SELECT ... FOR SHARE, and for INSERT, UPDATE, and DELETE. For those statements, InnoDB's answer is a family of index-record locks. A record lock pins an existing index record. A gap lock pins the interval between two index records, or before the first, or after the last, and blocks inserts into that interval no matter which row values are involved. A next-key lock is the combination: the record plus the gap in front of it. For a locking read or a write that searches through a secondary index under REPEATABLE READ, next-key locking is the default behavior.
Three consequences follow. First, locks live on indexes, not on the table as an abstraction: a WHERE clause that resolves through a unique index equality match degrades gracefully to a plain record lock, while the same predicate without a usable index can end up locking a wide swath of whatever index InnoDB scanned. Second, "the row does not exist yet" is not protection: inserting into a locked gap waits, even though there is no row to conflict with. Third, ranges are sticky: a SELECT ... WHERE id BETWEEN 10 AND 20 FOR UPDATE holds that interval against inserts for the life of the transaction.
The classic two-insert deadlock
The canonical deadlock needs no UPDATEs at all. Two transactions each probe for the same key that does not exist yet, say both run SELECT ... FOR UPDATE on a value that is absent. FOR UPDATE requests exclusive locks, and with no record to lock, what each transaction ends up holding is a gap lock on the interval where that key would live; gap locks held by different transactions do not conflict with each other, which is exactly why both sessions can hold one over the same interval at the same time. Then each tries to INSERT the row. An insert needs an insert intention lock, a special kind of gap lock that signals "I want to insert at this position"; it is compatible with other insert intentions at different positions but not with an existing gap lock held by another transaction. Session A's insert waits on session B's gap lock, session B's insert waits on session A's gap lock, and the deadlock detector kills one of them. Swap the probe for an upsert and you get the same shape: two concurrent INSERT ... ON DUPLICATE KEY UPDATE statements touching adjacent unique values are a famous producer of this deadlock.
The detail that matters for reading the evidence: insert intention locks show up in the deadlock report as "lock mode X locks gap before rec insert intention waiting", and the row in question often does not exist in the table at all. That is why the report initially looks like InnoDB deadlocked on nothing.
Reading LATEST DETECTED DEADLOCK
The forensic record lives in the engine status output, and 8.0 gives you a live lock view alongside it:
SHOW ENGINE INNODB STATUSG
-- held and waiting locks, live, while it is happening:
SELECT engine_transaction_id, lock_type, lock_mode, lock_status,
lock_data, object_schema, object_name, index_name
FROM performance_schema.data_locks
WHERE object_schema = 'app' AND object_name = 'orders';
-- who blocks whom, right now:
SELECT * FROM sys.innodb_lock_waits;The LATEST DETECTED DEADLOCK section gives you both transactions, the exact statements, the indexes involved, the lock modes held and requested, and which transaction was rolled back. Read it in this order: find the index name first, because the fight is always over an index record or gap; then the lock modes; then map lock_data to the actual key values. "locks gap before rec" tells you a gap was involved; "insert intention waiting" tells you an insert collided with it. In 8.0, performance_schema.data_locks replaced the old information_schema.INNODB_LOCKS table and shows held and waiting locks live, which is invaluable for catching the setup before the detector fires.
Two configuration notes. innodb_deadlock_detect is ON by default and is almost always worth keeping on; disabling it converts deadlocks into innodb_lock_wait_timeout stalls, which is worse for latency and only defensible in very specific high-contention benchmarks. And set innodb_print_all_deadlocks = ON so every deadlock, not just the latest, lands in the error log. Deadlocks the application retries away silently still cost latency and connections, and the error log is where you count them honestly.
READ COMMITTED: what you gain, what you give up
READ COMMITTED removes most gap locking. Outside unique-key duplicate checks and foreign key constraint checks, InnoDB under READ COMMITTED locks only the records it actually touches, which eliminates most of the gap-on-gap insert deadlocks above. Not all of them: duplicate-key checks and foreign-key checks keep their gap locks even under READ COMMITTED, so the concurrent INSERT ... ON DUPLICATE KEY UPDATE variant of that deadlock can survive the switch, and a schema full of foreign keys keeps some gap behavior whether you want it or not. Plenty of large-scale MySQL shops run READ COMMITTED for exactly this reason, and if your application was written assuming PostgreSQL or Oracle semantics, it is also closer to what your code already expects.
The tradeoffs are real. Phantoms come back: a transaction that reads a range twice can see new rows the second time, which breaks reservation and inventory patterns that quietly relied on gap protection. And if you still run statement-based binary logging anywhere, READ COMMITTED is unsafe; InnoDB refuses to combine them, because phantom behavior breaks replay determinism. You need row-based binlog, which you should have anyway. My take: on a mature OLTP schema with good unique keys, READ COMMITTED is the lower-drama default; on a schema with reserve-then-insert patterns and no unique constraints to lean on, REPEATABLE READ's gap locks are doing invisible correctness work you will miss when they are gone.
Reducing deadlock frequency in practice
Whatever isolation level you choose, the same disciplines cut deadlock rates. Keep transactions short: every lock is held until commit, so a transaction that spans an HTTP call is a deadlock generator. Touch rows in a consistent order across code paths; most multi-row deadlocks are two transactions visiting the same rows in opposite orders. Give the optimizer real indexes so range scans lock narrow intervals instead of half the table; a missing index is a gap-lock amplifier. And handle deadlocks as the normal condition they are: InnoDB guarantees a loser, so retry the killed transaction a small number of times with backoff rather than paging someone over ERROR 1213.
If you are chasing one specific recurring deadlock, the fastest path is the error log with innodb_print_all_deadlocks on, plus the processlist state for the participants. Long metadata lock queues behind DDL are a different beast with different evidence; I wrote that one up in diagnosing MySQL metadata locks, and confusing the two wastes hours, because the fix for a lock-wait deadlock never involves killing an ALTER.
Deadlocks deserve a dashboard, not a grep
Nobody should have to tail an error log to count their own deadlocks, and that conviction is shaping the MySQL monitoring we are building at MonPG: deadlock rate as a first-class metric with the participant statements attached to each event, so the pattern is visible without SSH, and lock-wait time separated from query time so contention stops masquerading as slow SQL. The honest boundary, stated plainly: the shipping product monitors PostgreSQL today, MySQL support is being built and is not out yet, and that same evidence-first approach is already live for Postgres on the MonPG platform. The rest of this MySQL series sits on the blog. Until the MySQL side lands: innodb_print_all_deadlocks, an error-log tail, and the discipline to read the index names before the statements.