InnoDB Gap Lock Contention Causing Latency Spikes Under Concurrent Range Scans

The short answer

Whether a gap lock blocks your insert has nothing to do with the primary key value you are inserting. It depends on the insert's sort position in the locked index. InnoDB orders non-unique secondary index entries by (secondary_key, primary_key), so two rows with the same secondary value occupy different positions. An insert is blocked if — and only if — its (key, PK) position falls inside an interval covered by another transaction's gap or next-key lock.

One correction to the premise: InnoDB has no page-level DML locks. Locking is at the record/gap level. The page-correlated contention you observe is real but indirect — many logical gaps are physically clustered on the same B-tree leaf pages, so lock and latch contention concentrates there even though the locks themselves are per-index-record.

Why the insert blocks

Under REPEATABLE READ, a range scan or equality search on a non-unique index takes next-key locks on the matched records plus a gap lock on the interval after the last match. This is what prevents phantom rows of that value from appearing mid-transaction.

Before inserting, a transaction must acquire an insert intention lock on the target gap. That lock conflicts with any gap or next-key lock another transaction holds on the same interval — regardless of whether the actual row values overlap. So an insert of a completely different primary key still stalls if its sort position lands in the locked gap. This is also why lock waits grow non-linearly with concurrency: overlapping next-key locks create cyclic wait-for edges, and deadlock frequency rises faster than the transaction count.

Can EXPLAIN predict the lock scope?

Partially, and only as a lower bound. EXPLAIN tells you the access type (range, ref, index) and which index is used, so you can infer roughly which index intervals will be next-key locked. But the exact lock set depends on rows actually examined at runtime, which can exceed estimates when statistics are stale, plus runtime predicate evaluation. Treat EXPLAIN as a floor for the locked range, not a guarantee.

How to verify on your workload

Reproduce with two sessions against a test table:

1. Session A: BEGIN; SELECT ... FOR UPDATE on a secondary-index range under REPEATABLE READ.
2. Session B: insert a row with a different PK whose (key, PK) position falls inside the scanned gap. Confirm it stalls.
3. Inspect the actual locks: SELECT * FROM performance_schema.data_locks; on MySQL 8.0+ (or information_schema.innodb_locks on 5.7). Look for GAP, NEXT_KEY, and waiting INSERT_INTENTION modes.
4. Compare EXPLAIN ANALYZE rows-examined against the observed lock set to test how well the plan predicted scope.

Mitigations, in order of preference

• Switch to READ COMMITTED. Gap locks for search and index scans are largely disabled; only foreign-key and duplicate-key checks retain them. Repeat your two-session test afterward to confirm the insert no longer blocks. Caveat: statement-based binary logging relies on REPEATABLE READ semantics, so this effectively requires binlog_format=ROW.
• Use a unique index where possible. Equality lookups on a unique index degrade to plain record locks — no gap locking needed.
• Shorten transactions. Locks release at commit; shrinking transaction duration shrinks the blocking window without changing semantics.

Exact locking details are version-sensitive across MySQL 5.7, 8.0, and 8.4, so confirm against the release you run. The one detail that would sharpen this advice: which MySQL version and binlog_format you are on, since that determines whether READ COMMITTED is a safe switch for you.