MySQL Tuning, Indexing & Concurrency
10+ Years (Principal / Architect)
Expert
Production Scenario
Explain InnoDB Locking Modes: Record Locks, Gap Locks, and Next-Key Locks, and how they prevent Phantom Reads.
Real-World Scenario / Incident Description
Under `REPEATABLE READ` isolation level, two concurrent transactions inserting users with IDs between 10 and 20 resulted in lock wait timeouts and deadlocks.
278 views
•
Updated 1 month ago
The Three InnoDB Row Lock Types
- Record Lock: Locks the physical index record itself (e.g.
WHERE id = 5 FOR UPDATE). - Gap Lock: Locks the gap between index records (or before the first / after the last) to prevent other transactions from inserting new rows in that gap.
- Next-Key Lock: A combination of a Record Lock on the index record plus a Gap Lock on the gap preceding it.
Why MySQL Uses Next-Key Locks by Default
Under the default REPEATABLE READ isolation level, MySQL uses Next-Key locking to solve the Phantom Read problem (preventing another transaction from inserting a new row that matches your query's range before you commit).
Reducing Lock Contention:
Switching transaction isolation to READ COMMITTED (with binlog_format=ROW) disables Gap Locks except for foreign key constraint checks and duplicate key checks.
Related Concepts:
#InnoDB Locks
#Gap Locks
#Next-Key Locks
#Phantom Reads
#Transactions
More MySQL Tuning, Indexing & Concurrency Interview Questions
How does the InnoDB B+Tree Index work internally, and why does Leftmost Prefix Rule dictate composite index column ordering?
Read →
How do you optimize deep pagination (`SELECT * ... LIMIT 5000000, 20`) on large tables with 100M rows?
Read →
How do you interpret MySQL `EXPLAIN ANALYZE` output to diagnose `Using filesort` and `Using temporary`?
Read →
Explain MySQL Table Partitioning (Range, List, Hash) vs. Application-Level Sharding for 1TB+ databases.
Read →
How does the InnoDB Buffer Pool manage dirty pages, and how do you size `innodb_buffer_pool_size` for production servers?
Read →