InnoDB Gap Lock Contention Causing Latency Spikes Under Concurrent Range Scans

When multiple concurrent transactions execute range-based SELECT ... FOR UPDATE or range DELETE statements under REPEATABLE READ isolation, InnoDB acquires next-key locks that combine record locks with gap locks on the scanned index intervals. This locking behavior prevents phantom reads but can serialize inserts into the same gaps even when the inserted values do not conflict with existing rows.

The precise conditions under which gap locks on non-unique secondary indexes block concurrent inserts remain incompletely documented, particularly when the range scan touches pages that are not fully populated. Lock wait timeouts and deadlock frequency increase non-linearly with concurrency, yet the relationship between index cardinality, page fill factor, and gap lock duration has not been quantified in the official documentation.

What determines whether a gap lock on a non-unique secondary index blocks an insert that targets a different primary key value? How does the page-level locking granularity interact with the B-tree structure during concurrent range scans? Can the lock scope be predicted from the EXPLAIN output and index statistics alone?