✏️ Explanatory Question

Gap locks and next-key locks — why an INSERT mysteriously blocks under REPEATABLE READ

👁 10 Views
📘 Detailed Answer
🟢 Easy
← Previous our nightly report shows inconsistent totals — phantom reads and choosing the right isolation
💡

Answer with Explanation

107

Gap locks and next-key locks — why an INSERT blocks under REPEATABLE READ

Level: Very Hard — Deep InnoDB internals; gap locks confuse even experienced developers because they lock rows that don't exist yet.

Scenario: Transaction A runs SELECT ... WHERE age BETWEEN 20 AND 30 FOR UPDATE. Transaction B then tries to INSERT a brand-new employee with age = 25 — and it hangs, even though that row doesn't exist yet and A never touched it. Why is a non-existent row locked?

Sample Data — employees (indexed on age)

emp_idage
118
222
328
435

The cause: Under REPEATABLE READ, InnoDB uses next-key locks (a row lock + a gap lock on the range before it) to prevent phantom reads. When A locks age BETWEEN 20 AND 30, it doesn't just lock rows 22 and 28 — it locks the gaps around them (18–22, 22–28, 28–35). Any INSERT into those gaps (like age 25) must wait.

Why gap locks exist: They stop phantom rows. If B could insert age 25, then A re-running its query would see a new row that wasn't there before — a phantom. Gap locks make A's range "immutable" for its duration, guaranteeing repeatable results.

The Three Lock Types

Lock TypeWhat It LocksPrevents
Record LockA single index rowUpdate/delete of that row
Gap LockThe space BETWEEN rows (no rows)Inserts into the gap
Next-Key LockA row + the gap before itBoth (phantom protection)

Demonstrating the Block

-- Txn A: range lock under REPEATABLE READ (default)
START TRANSACTION;
SELECT * FROM employees
WHERE age BETWEEN 20 AND 30
FOR UPDATE;
-- Locks rows 22, 28 AND the gaps around the 20-30 range

-- Txn B: tries to insert into the locked gap -> BLOCKS
START TRANSACTION;
INSERT INTO employees (emp_id, age) VALUES (5, 25);  -- HANGS / waits
-- Waits until Txn A commits or rolls back

How to Reduce Gap Locking

  • Use READ COMMITTED: It largely disables gap locks (only record locks remain), allowing the insert — but you lose phantom-read protection.
  • Lock by unique/primary key: An exact match on a unique index uses a plain record lock (no gap lock), since no phantom is possible.
  • Keep transactions short: Release the locks quickly to minimize blocking.
  • Avoid wide ranges in FOR UPDATE when you only need specific rows.

The READ COMMITTED Alternative

-- Under READ COMMITTED, gap locks are mostly disabled
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

START TRANSACTION;
SELECT * FROM employees WHERE age BETWEEN 20 AND 30 FOR UPDATE;
-- Only rows 22 and 28 are locked; the gaps are NOT

-- Now Txn B's INSERT of age 25 SUCCEEDS (no gap lock blocking it)
-- Trade-off: phantom reads become possible
The trade-off is real: Gap locks are the reason InnoDB's REPEATABLE READ prevents phantoms (going beyond the SQL standard). Removing them (via READ COMMITTED) boosts insert concurrency but reopens the phantom-read window. Choose based on whether your workload needs phantom protection or insert throughput.

Interviewer follow-up: "You're getting deadlocks specifically from gap locks on a high-insert table — what's the pragmatic fix?" → Switch that workload to READ COMMITTED (many high-throughput systems, including those following Oracle-like semantics, run RC by default), ensure you're locking via unique keys, and add retry logic. Gap-lock deadlocks are common on hot insert paths under REPEATABLE READ.
← Previous our nightly report shows inconsistent totals — phantom reads and choosing the right isolation