MySQL Gap Lock Interview Questions: Next-Key Locks, Range Queries, and Insert Blocking

A transaction selects rows where id > 100. Another transaction tries to insert 90 and waits. If your explanation is “the first query locked matching rows,” you have missed the index gap that makes this MySQL interview question interesting.

The short answer: InnoDB can lock index intervals, including spaces where no row exists. Under REPEATABLE READ, a locking range scan can prevent inserts into those intervals until its transaction ends. Predicting the wait requires the index, access path, isolation level, and transaction boundary. Oracle documents the same boundary effect in its next-key locking example.

This walkthrough supplies a concrete sequence you can change one variable at a time.

Evidence boundary: Official facts below refer to MySQL 8.4 and InnoDB, checked September 21, 2026. The SQL exercises and interview follow-ups are our preparation examples; their expected results are derived from the documentation, not captured output from a server run for this article. We did not verify firsthand candidate reports establishing these exact questions or their frequency.

What does a gap lock protect?

Official fact: A record lock protects an index record. A gap lock protects an interval between index records, or the space before the first or after the last record. A next-key lock combines a record lock with the gap immediately before that record. InnoDB locking reference.

For neighboring keys 80 and 120, draw the next-key interval as (80, 120]. The open endpoint excludes 80; the closed endpoint includes 120. Inserting 110 needs space inside the gap. Updating the existing 120 touches the record component. These are different conflicts even though one next-key lock can explain both.

An interview answer should name which component matters. “The row is locked” cannot explain an insert for a row that does not yet exist. “The entire table is locked” loses the index-level reasoning that the exercise is testing.

Can you reproduce an insert waiting behind a range query?

Use a scratch database in separate connections. This fixture has one integer primary key, no secondary indexes, and no foreign keys. Those choices remove alternative explanations from the first experiment.

Run setup once, with both test sessions idle:

CREATE TABLE gap_lock_demo (
    id INT PRIMARY KEY
) ENGINE = InnoDB;
INSERT INTO gap_lock_demo VALUES (80), (120), (160);
COMMIT;

In Session A, select the scratch database, then run:

SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;
SELECT id FROM gap_lock_demo WHERE id > 100 FOR UPDATE;
-- Leave this transaction open.

In Session B, select the same database:

SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;
INSERT INTO gap_lock_demo VALUES (110);
-- Expected: waits while Session A holds the relevant gap.

While B waits, inspect the locks from a third connection using the query below. Then issue COMMIT in A. Assuming no other blocker or timeout, B’s insert can finish. Run ROLLBACK in B so 110 does not remain in the fixture.

Official fact: Locking reads need an explicit transaction or autocommit disabled. Their locks are released by commit or rollback. The return of A’s SELECT does not end the explicit transaction. Locking reads.

Before recording results, check SELECT VERSION(), @@transaction_isolation; in each session. Run EXPLAIN SELECT id FROM gap_lock_demo WHERE id > 100 FOR UPDATE; separately to inspect the access path. If your output differs, preserve the version, plan, schema, and transaction sequence before changing the example.

Why can a value outside the WHERE condition still wait?

Worked inference from the documented range-scan example: With keys 80, 120, 160, the relevant next-key intervals are (80, 120], (120, 160], and the gap above 160. The first interval starts at the predecessor of the first matching record, not at the literal constant 100.

Repeat the experiment from a clean fixture for each probe. Keep A’s query unchanged, and change only B’s inserted value:

Insert probe	Expected explanation while A remains open
90	Waits in the gap between 80 and 120, despite failing id > 100.
110	Waits in that same gap and would satisfy the predicate.
130	Waits between the matching records 120 and 160.
170	Waits in the protected gap above the largest key.

These fixture-specific predictions follow the official phantom-prevention walkthrough. They are not a universal lock map for every access path.

A useful follow-up is: “What would change if I added another index?” First inspect whether MySQL chooses it. Do not redraw locks from the SQL text alone.

Does a unique index eliminate gap locks?

Official fact: A unique-index search using a unique search condition locks only the found index record. Range searches generally lock scanned index ranges. A query using only part of a multicolumn unique index does not qualify merely because the index is called unique. Locks set by SQL statements.

Compare three fresh runs against our primary key:

• WHERE id = 120 FOR UPDATE finds an existing unique record. Explain the record lock before predicting a gap conflict.
• WHERE id > 100 FOR UPDATE is a range search, although id itself is unique.
• WHERE id = 110 FOR UPDATE finds no record. There is no found record to which the record-only exception can apply; a locking search can protect absence through a gap. Preventing phantoms.

For a unique index on (tenant_id, external_id), WHERE tenant_id = 7 leaves many possible keys. Your answer should identify the incomplete lookup before discussing locks.

Official fact: Locking operations generally lock index records they scan, rather than remembering the exact WHERE condition. Without a suitable index, a scan can therefore lock much more broadly. SQL statement locking rules.

Design inference: Add a selective index when it supports the real access pattern, then verify the resulting plan and wait footprint. An index recommendation is incomplete until you explain which scanned interval it should shrink.

What changes under READ COMMITTED or an ordinary SELECT?

Official fact: InnoDB defaults to REPEATABLE READ. Ordinary consistent reads use snapshots; they do not acquire the range locks discussed here. Under READ COMMITTED, gap locking is disabled for searches and index scans, with exceptions for foreign-key and duplicate-key checks. Transaction isolation levels.

Apply that distinction before blaming the isolation level:

Session A operation	What to investigate
RR: plain SELECT ... WHERE id > 100	Snapshot visibility; this is not our locking-read experiment.
RR: SELECT ... WHERE id > 100 FOR UPDATE	Scanned index intervals and transaction lifetime.
RC: the same FOR UPDATE range query	Record conflicts remain possible; ordinary search gap protection changes.
Either level: inserting an existing primary key	Duplicate-key checking, not proof of the original range-gap conflict.

Official fact: Under READ COMMITTED, record locks on nonmatching rows are released after MySQL evaluates the predicate. Under REPEATABLE READ, mixing snapshot reads and locking operations can expose different views of the data. Isolation-level behavior.

Design inference: Changing isolation may reduce this wait while changing the application’s concurrency guarantees. State which business invariant must survive before proposing that change. A shorter transaction may solve the operational problem with less behavioral change.

How can gap locks coexist while an insert still blocks?

Official fact: Pure gap locks are compatible with one another; even shared and exclusive gap locks can coexist. Their purpose is to inhibit insertion. That compatibility does not mean a transaction may insert into the protected gap. Gap locks.

Official fact: An insert intention lock signals an intended insertion. Two inserts at different positions in the same gap need not block each other, but an existing gap lock can prevent an insert intention from proceeding. Insert intention locks.

In the interview, separate “can these existing gap locks coexist?” from “can this insertion proceed?” Those questions concern different compatibility relationships. Also distinguish a one-way wait from a deadlock: B waiting for A is not, by itself, a cycle.

How do you prove which transaction blocks the insert?

Use a third connection in the same scratch database, with permission to read Performance Schema:

SELECT r.ENGINE_TRANSACTION_ID AS waiting_trx,
       b.ENGINE_TRANSACTION_ID AS blocking_trx,
       r.INDEX_NAME, r.LOCK_MODE AS waiting_mode,
       r.LOCK_DATA AS waiting_data,
       b.LOCK_MODE AS blocking_mode,
       b.LOCK_DATA AS blocking_data
FROM performance_schema.data_lock_waits AS w
JOIN performance_schema.data_locks AS r
  ON r.ENGINE = w.ENGINE
 AND r.ENGINE_LOCK_ID = w.REQUESTING_ENGINE_LOCK_ID
JOIN performance_schema.data_locks AS b
  ON b.ENGINE = w.ENGINE
 AND b.ENGINE_LOCK_ID = w.BLOCKING_ENGINE_LOCK_ID
WHERE r.OBJECT_SCHEMA = DATABASE()
  AND r.OBJECT_NAME = 'gap_lock_demo';

Official fact: data_lock_waits links requested locks to blocking locks; the relationship can be many-to-many. That is why the query joins both sides instead of treating the waiting transaction as the blocker. Wait relationships.

Official fact: data_locks exposes the index, lock mode, status, and lock data. A supremum pseudo-record identifies the upper-bound case; lock data can also be unavailable. Lock instrumentation.

Diagnostic inference: Capture the wait while B is blocked, correlate it with A’s open transaction, and compare it after A commits. Do not diagnose a gap conflict from a table intention lock alone. Tie the index and record/gap information to the actual insertion position.

If the join returns no rows, record when you sampled it. An empty observation after A commits cannot disprove an earlier wait. Check whether B finished, timed out, or used a different database. A screenshot of an idle connection is weaker evidence than a timestamped sequence of statements and the wait relationship captured between them.

For your own run, keep a compact observation record: the inserted value, A’s predicate, both session isolation levels, chosen index, waiting transaction, blocking transaction, and the action that released the wait. Leave fields blank when you did not observe them. This makes the experiment useful when a later schema change alters the result.

When presenting the result, distinguish your prediction from your measurement. “I expect insert 90 to wait because it lies between 80 and 120” is a hypothesis. “I captured B waiting on A, then observed B finish after A committed” describes evidence. If the evidence disagrees, revisit the setup before repeating the textbook answer.

A strong follow-up explanation also proposes a counterexample: repeat the same insertion after replacing A’s range search with the existing-key equality lookup. That isolates the locking operation while preserving the table and inserted value. Run it as a separate transaction, not as an additional statement inside A’s original transaction.

What should happen after a timeout or deadlock?

Official fact: InnoDB rolls back the entire transaction on deadlock. A lock-wait timeout normally rolls back only the waiting statement; innodb_rollback_on_timeout changes that behavior. Locks are not released merely by rolling back one statement. InnoDB error handling.

For this exercise, explicitly roll back B after a timeout before starting another probe. Otherwise, leftover transaction state can contaminate the next observation.

Design inference: In application code, retry a deadlock victim’s whole transaction from a fresh decision point, with bounded retries. Keep external side effects safe against repetition. Shorten lock-holding work and use a consistent access order before adding retries that merely repeat the same contention.

Practice explaining the boundary, then the remedy

Start with the scheduling exercise; annotate each wait with an index interval and the transaction that must finish.

Question	Use it to practice
Simulate Transaction Lock Scheduling	Trace grants, waits, and release order.
Reason About Indexes, Kafka, and Database Locking	Connect access paths to contention.
Explain Composite and Covering Indexes in MySQL	Explain full-key versus prefix searches.
Identify and fix deadlock in locked code	Distinguish a wait from a cycle.
Implement an in-memory database with record locking	Contrast record ownership with gap protection.

Then choose another Backend Engineer question and change one assumption: the index, isolation level, or commit timing. Explain which predicted wait changes and which business guarantee your remedy preserves.

Sources and Further Reading

• MySQL 8.4: InnoDB locking
• MySQL 8.4: Locks set by SQL statements
• MySQL 8.4: Transaction isolation levels
• MySQL 8.4: Locking reads
• MySQL 8.4: Phantom rows and next-key locking
• MySQL 8.4: Performance Schema data_locks
• MySQL 8.4: Performance Schema data_lock_waits
• MySQL 8.4: InnoDB error handling