How MySQL InnoDB Gap Locks Affect Concurrent Inserts

In MySQL’s InnoDB storage engine, an insert waits when another transaction holds a lock on the index position where the new row belongs, even if that transaction is reading or updating a different row. InnoDB sometimes locks parts of an index range, including spaces where no row exists yet. These gap locks prevent inserts into those spaces; next-key locks combine a lock on an existing index entry with a gap lock on the space immediately before it. The locks therefore protect ranges, not just existing rows.

This behavior commonly appears when a transaction scans a range under InnoDB’s default REPEATABLE READ isolation level. To understand why an insert waits, check which index entries the other transaction scanned and which locks it holds. Comparing only the rows named in each request misses locks on the spaces between index entries.

How a range lock blocks an insert

InnoDB stores rows in indexes. Its locks can cover an index entry or the gap between entries. The InnoDB locking rules explain how next-key locks cover both an index entry and the gap before it.

Suppose a table has primary-key values 90 and 102. Session A runs a locking range read, and Session B tries to insert 101:

-- Initial setup
CREATE TABLE child (
    id INT NOT NULL,
    PRIMARY KEY (id)
) ENGINE = InnoDB;

INSERT INTO child (id) VALUES (90), (102);

Session A

START TRANSACTION;

SELECT *
FROM child
WHERE id > 100
FOR UPDATE;

Session B

START TRANSACTION;

INSERT INTO child (id) VALUES (101);

Session A finds 102. Under REPEATABLE READ, its range scan can take a next-key lock on that index entry, protecting the gap before it. Since 101 belongs between 90 and 102, Session B’s insert waits until Session A commits or rolls back. MySQL documents this example and identifies the waiting insert’s request as an insert-intention lock.

An insert-intention lock signals where a transaction plans to add an index entry. It does not by itself force all inserts into the same gap to run one at a time: transactions inserting at different positions in an otherwise-unlocked gap can proceed concurrently. The wait occurs when another transaction already holds a conflicting lock on the insertion position. The locks used by different InnoDB statements describe these insert and range-lock interactions.

When InnoDB locks gaps

When a transaction reads a range with SELECT ... FOR UPDATE or SELECT ... FOR SHARE, InnoDB can lock gaps or take next-key locks at REPEATABLE READ. UPDATE and DELETE statements that search across a range can also lock the index entries they scan. These locks help prevent another transaction from inserting a row into a range the first transaction has read.

The access path matters. InnoDB bases locks on the index records a statement scans, not only on the rows an application expects its condition to match. A broad scan, including one caused by a missing suitable index, can therefore lock more index entries and gaps. A locking lookup that uses all columns of a unique index to find one exact row needs only a record lock for that row. A condition that searches across multiple values, or a lookup that uses only some columns of a composite unique index, does not count as an exact lookup.

A plain SELECT normally reads a consistent snapshot without taking these row locks. Adding FOR UPDATE or FOR SHARE changes the read into a locking read. SERIALIZABLE is an exception to the usual behavior of plain reads.

How isolation level changes insert waits

At InnoDB’s default REPEATABLE READ level, range scans use next-key locking to prevent phantom rows: new rows that would match a range read before the transaction finishes. At READ COMMITTED, InnoDB disables gap locking for ordinary locking reads, updates, and deletes, so inserts into those ranges are less likely to wait on those locks. The isolation-level rules also describe the tradeoff: other transactions can insert rows into ranges already read, allowing phantoms.

READ COMMITTED retains gap locking for duplicate-key and foreign-key checks. Foreign-key validation can also take shared record-level locks on records it inspects, which can cause contention without being a gap lock. Changing isolation levels therefore does not eliminate every insert wait, and it changes the consistency behavior the application receives.

How to investigate and reduce contention

When an insert is blocked, its transaction cannot finish the statement until the other transaction releases its lock. If inserts keep encountering the same locked range, the waits increase latency and reduce how many transactions finish. The effect depends on which index entries the statements scan and how long transactions hold their locks. The workload also affects the result.

Start by checking the locking statement’s execution plan with EXPLAIN. A suitable index narrows the scan, so InnoDB locks fewer index entries. Keep transactions short: commit after making the needed database changes instead of holding locks while unrelated work runs. MySQL’s guidance on reducing deadlocks and lock contention also recommends short transactions and well-chosen indexes.

To find the cause of a particular wait, inspect the data_locks and data_lock_waits tables in Performance Schema. These tables show which transactions request locks and which transactions are waiting. Use that information to distinguish a gap-lock conflict from a record lock, duplicate-key check, or foreign-key check. MySQL documents these tables in its lock and lock-wait information.

What I'm building

Delegate tasks. Get software.

Give Vroni a GitHub issue, bug report, spec, or rough idea. It reads the repo, plans the change, writes code, runs checks, and works toward a review-ready pull request.

Take a look at vroni.com