· Development

MySQL InnoDB Gap Locks: What Gets Locked When the Row Doesn't Exist

Why InnoDB locks gaps between index records under REPEATABLE READ, why check-then-insert with FOR SHARE can deadlock, and what READ COMMITTED gives up. Based on the MySQL 8.4 manual.

mysqlinnodbdatabaseconcurrencylocking

Sometimes a SELECT ... FOR UPDATE in MySQL returns zero rows, yet another session’s INSERT sits there waiting. Nothing was locked, so what is it waiting for? The answer is that InnoDB locks not only rows but also the gaps between index records.

This post follows the MySQL 8.4 Reference Manual. The manual has pages for other versions too, and details can differ between them.

Locks live on index records, not rows

InnoDB record locks are placed on index records. Even if you define no index on a table, InnoDB creates a hidden clustered index and locks records in that.

The manual distinguishes these locks:

  • Record lock: locks a single index record.
  • Gap lock: locks the gap between index records, or the gap before the first record or after the last one.
  • Next-key lock: a record lock combined with a gap lock on the gap before that record.
  • Insert intention lock: a type of gap lock that INSERT sets before inserting a row.

InnoDB’s default isolation level is REPEATABLE READ. A plain SELECT at that level is a nonlocking consistent read served from a snapshot, so it takes no locks. Locking reads (SELECT ... FOR UPDATE or FOR SHARE), UPDATE, and DELETE are different: they lock the index range they scan, using next-key or gap locks to block inserts into it. The exception is a unique index searched with a unique condition, which locks only the record found. The goal of locking the range is to prevent phantom rows. A phantom is a row that shows up the second time the same query runs within a transaction but was not there the first time.

Think in intervals

Using the manual’s example, if an index contains 10, 11, 13, and 20, the intervals a next-key lock can cover are:

(negative infinity, 10]
(10, 11]
(11, 13]
(13, 20]
(20, positive infinity)

The last interval is attached to the supremum pseudo-record, not a real record. The supremum is a virtual record higher than any value in the index, so in effect this next-key lock only locks the gap after the largest value.

Let’s walk through the manual’s own example. The child table has id values 90 and 102.

-- Session A
START TRANSACTION;
SELECT * FROM child WHERE id > 100 FOR UPDATE;

This query does more than lock 102. It also locks the gap before 102 (between 90 and 102) and the gap after 102. If session B runs INSERT INTO child (id) VALUES (101);, it cannot get its insert intention lock and waits. The manual explains that this blocks inserts of any id greater than 100. That is why running the same SELECT again in session A will not show a new row.

Gap locks don’t conflict with each other

This is the property that trips people up. The manual calls gap locks “purely inhibitive”: their only purpose is to stop other transactions from inserting into the gap.

  • One transaction can hold a shared gap lock on a gap while another holds an exclusive gap lock on the same gap.
  • There is no difference between shared and exclusive gap locks, and they do not conflict with each other.

An insert intention lock, on the other hand, conflicts with another transaction’s gap lock and waits. Insert intention locks don’t wait for each other if they target different positions in the same gap. The manual’s example is two transactions inserting 5 and 6 into the gap between 4 and 7.

Put those two rules together and you have the ingredients for a wait cycle.

How “insert if missing” can become a deadlock

The manual says you can use next-key locking to implement a uniqueness check in your application. If you read in share mode and see no duplicate, the next-key lock taken during the read stops anyone from inserting a duplicate in the meantime. In the manual’s words, it lets you “lock” the nonexistence of something.

Duplicates really are prevented. The trouble can start when two transactions run the same check at the same time. Take a table with a non-unique index on email:

CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  email VARCHAR(255) NOT NULL,
  KEY idx_email (email)
) ENGINE = InnoDB;

The interleaving below is worked out from the documented rules, not captured from a live server. It assumes the isolation level is REPEATABLE READ, the SELECT actually uses idx_email (check with EXPLAIN), no row with that email exists yet, and the existing index values place both sessions’ locks on the same gap, the one the new entry would go into.

-- Session A
START TRANSACTION;
SELECT id FROM users WHERE email = 'kim@example.com' FOR SHARE;  -- 0 rows

-- Session B
START TRANSACTION;
SELECT id FROM users WHERE email = 'kim@example.com' FOR SHARE;  -- 0 rows, gap locks are compatible

-- Session A
INSERT INTO users (email) VALUES ('kim@example.com');  -- may wait on B's gap lock

-- Session B
INSERT INTO users (email) VALUES ('kim@example.com');  -- may wait on A's gap lock → cycle
  1. Both SELECT ... FOR SHARE statements lock the same gap. Gap locks don’t conflict, so neither read waits.
  2. A’s INSERT needs an insert intention lock, which conflicts with B’s gap lock, so it waits.
  3. B’s INSERT waits on A for the same reason.

If the locks line up like this, the two transactions wait on each other. InnoDB detects the cycle, rolls back one side, and returns ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction, the same error format shown in the manual’s deadlock example. With a different access path, different neighboring values, or a wider lock footprint, the waits can play out differently or not form a cycle at all. Check your own case with the tools described below.

Either way, no duplicate row gets created; the cost is that a concurrent request may fail with a deadlock instead. Code that uses this pattern should catch error 1213, treat the transaction as rolled back, and, where it makes sense, retry the whole transaction with a bounded number of attempts.

A similar deadlock happens with unique keys. According to the manual’s page on locks set by different SQL statements, when an INSERT hits a duplicate-key error, it sets a shared lock on that record. If three sessions insert the same primary key value and the first one rolls back, the other two both get shared locks and then deadlock waiting for each other’s exclusive lock. INSERT ... ON DUPLICATE KEY UPDATE takes an exclusive lock instead of a shared one.

When there is no gap lock

Not every locking read locks a gap.

  • When a unique index is used to find a single unique row, only the index record found is locked, not the gap before it. For example, if id has a unique index, WHERE id = 100 uses only a record lock.
  • However, if the search condition uses only some columns of a multiple-column unique index, gap locking does occur.
  • If the search column has no index or a non-unique index, the preceding gap is locked as well.

There is an easy-to-miss point related to the last item. Locking reads, UPDATE, and DELETE set locks on every index record they scan, regardless of WHERE conditions. If no suitable index exists and the whole table is scanned, every row in the table is locked and all inserts by other users are blocked. If gap locks seem wider than expected, start by checking with EXPLAIN which index the query uses.

Switching to READ COMMITTED

According to the isolation levels documentation, READ COMMITTED turns off gap locking for searches and index scans. Gap locks are used only for foreign-key constraint checking and duplicate-key checking. The “insert if missing” cycle above depends on gap locks from the search step, so it does not arise the same way at this level.

The cost is just as clear:

  • Other sessions can insert new rows into gaps, so phantoms can occur. The FOR SHARE uniqueness check above no longer prevents duplicates.
  • Only row-based binary logging is supported with READ COMMITTED. With binlog_format=MIXED, the server automatically uses row-based logging.
  • UPDATE and DELETE release record locks on non-matching rows after evaluating the WHERE condition.

So changing the isolation level is less a deadlock fix than a trade of guarantees. The sensible order is to move duplicate prevention onto a unique constraint first and consider the isolation level after that. With a UNIQUE constraint on email, you can INSERT directly without a lookup and handle the duplicate-key error.

Seeing the locks yourself

Observing is faster than guessing.

performance_schema.data_locks shows locks that are held or requested.

SELECT ENGINE_TRANSACTION_ID, OBJECT_NAME, INDEX_NAME,
       LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA
FROM performance_schema.data_locks;
  • A LOCK_MODE that includes GAP, such as X,GAP, is a gap lock. The manual’s deadlock example also shows record locks without the gap, such as S,REC_NOT_GAP.
  • LOCK_STATUS is GRANTED or WAITING.
  • A LOCK_DATA of supremum pseudo-record means the gap after the largest value is locked.
  • performance_schema.data_lock_waits shows which requests are blocked by which locks.

In SHOW ENGINE INNODB STATUS, these appear as text. lock_mode X locks rec but not gap is a record lock, a bare lock_mode X is a next-key lock, and lock_mode X locks gap before rec insert intention waiting is an insert intention lock waiting on a gap lock. The command also shows the cause of the most recent deadlock. To write every deadlock to the error log, the manual suggests enabling innodb_print_all_deadlocks and turning it off when you are done debugging.

Summary

  • Locking reads under REPEATABLE READ lock not only the rows they find but also gaps in the scanned range. A query that returns zero rows can still block inserts.
  • Gap locks don’t conflict with each other; they only block inserts. So two transactions that check the same gap and then both insert can deadlock.
  • Finding a single row through a unique index takes no gap lock. Without a usable index, the entire table gets locked instead.
  • READ COMMITTED removes search gap locks, and phantom protection goes with them.

The manual’s guidance on handling deadlocks says to always be prepared to re-issue a transaction that fails due to deadlock. Understanding gap locks tells you in advance which code paths are most likely to need it.

Back to the blog