Senior LevelSQL & MySQL
How do Next-Key Locks and Gap Locks in InnoDB eliminate Phantom Reads in REPEATABLE READ?
❓ Interview Prompt: Explain Record Locks, Gap Locks, Next-Key Locks (`Next-Key Lock = Record Lock + Gap Lock`), and preventing Phantom Reads in InnoDB.
In standard ANSI SQL, 'REPEATABLE READ' allows Phantom Reads. MySQL InnoDB eliminates Phantom Reads under 'REPEATABLE READ' using Next-Key Locking:
Locking Types:
•
Record Lock: Locks the index record itself.
•
Gap Lock: Locks the index gap between records (or before the first / after the last record).
•
Next-Key Lock: Combination of a Record Lock on the index record AND a Gap Lock on the gap preceding it.
•
Effect: Prevents concurrent transactions from inserting new rows into the locked index gap!
Code Implementation Example
sql
-- Next-Key Lock places Gap Locks on the index range (age > 30), blocking inserts!
START TRANSACTION;
SELECT * FROM users WHERE age > 30 FOR UPDATE;
-- Concurrent INSERT INTO users (age=35) blocks until COMMIT!💡Acquiring range locks using Next-Key Locks to block phantom inserts.
🎯 Interview Key Takeaway
InnoDB uses Next-Key Locking to lock index record gaps and prevent Phantom Reads in REPEATABLE READ isolation.
#SQL#Next-Key Locks#Gap Locks#InnoDB#Senior
Back to all SQL & MySQL Questions→