How Gap Locks Block an INSERT in MySQL
An INSERT adds a brand-new row. So what could it possibly be waiting for? Under MySQL's default isolation level, it can be waiting for a gap: the space between rows, locked by a transaction that asked about a row that isn't there. Here's a gap lock, the insert it blocks, and how READ COMMITTED removes it.
Prerequisites
- MySQL, and two sessions, plus a root session to read the locks. The examples use MySQL 9.7.4 in Docker.
SELECT ... FOR UPDATE.
Step 1: Lock a Row That Doesn't Exist
No product has the ID 1000, which is higher than any in the table:
START TRANSACTION;
SELECT Id, Sku FROM products WHERE Id = 1000 FOR UPDATE;
Empty set (0.001 sec)
No rows, yet there's a lock:
SELECT INDEX_NAME, LOCK_MODE, LOCK_STATUS, LOCK_DATA
FROM performance_schema.data_locks
WHERE LOCK_TYPE = 'RECORD';
+------------+-----------+-------------+------------------------+
| INDEX_NAME | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+------------+-----------+-------------+------------------------+
| PRIMARY | X | GRANTED | supremum pseudo-record |
+------------+-----------+-------------+------------------------+
1 row in set (0.001 sec)
supremum pseudo-record is an imaginary row after the last real one, and a lock on it covers the gap between them. MySQL's InnoDB locking page says: "A gap lock is a lock on a gap between index records, or a lock on the gap before the first or after the last index record."
Step 2: Try to Insert into the Gap
A new product gets the next AUTO_INCREMENT ID, which lands in that gap:
SET SESSION innodb_lock_wait_timeout = 4;
INSERT INTO products (Sku, Name, CategoryId, UnitPrice, QuantityOnHand, ReorderLevel, IsArchived, Version, CreatedAtUtc, UpdatedAtUtc)
VALUES ('GAP-TEST', 'Gap test', 1, 1, 0, 0, 0, 0, NOW(6), NOW(6));
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
It waited for a lock on a row that doesn't exist, and gave up. Other tables aren't affected: an INSERT into categories at the same moment went straight through.
The page gives the reason: "Gap locks in InnoDB are 'purely inhibitive', which means that their only purpose is to prevent other transactions from inserting to the gap." That's how REPEATABLE READ makes a repeated FOR UPDATE return the same rows.
Step 3: READ COMMITTED Has No Such Lock
ROLLBACK;
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;
SELECT Id, Sku FROM products WHERE Id = 1000 FOR UPDATE;
Empty set (0.003 sec)
No locks at all, and the same INSERT goes through while the transaction is open:
Query OK, 1 row affected (0.004 sec)
The Deadlock This Causes
Gap locks don't conflict with each other, and that sets a trap. Two sessions check for a row, find nothing, and both go to insert:
START TRANSACTION;
SELECT Id, Sku FROM products WHERE Id = 1000 FOR UPDATE;
START TRANSACTION;
SELECT Id, Sku FROM products WHERE Id = 1000 FOR UPDATE;
Each holds the gap, so each insert waits for the other:
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
Fixed: Deadlock on INSERT After SELECT FOR UPDATE on a Missing Row covers the fix: insert first, and handle a duplicate.
Common Mistakes
| Mistake | What happens | Do this instead |
|---|---|---|
"Check if it exists with FOR UPDATE, then insert" | Gap locks, and deadlocks between two sessions | Insert, and handle the duplicate error |
Holding a FOR UPDATE on a range open for long | Inserts into the range wait | Keep it short |
Assuming an INSERT can't wait | It waits for gap locks and for unique checks | Look at data_locks |
Troubleshooting
| Symptom | Likely cause | Fix |
|---|---|---|
An INSERT hangs, with no conflicting row | A gap lock | See who's blocking |
ERROR 1213 on an INSERT | Two gap locks, and two inserts | The fix |
A lock on supremum pseudo-record | A gap lock at the end of the index | End the transaction that holds it |
FAQ
Should I switch to READ COMMITTED to avoid them?
Only if you've thought through what it changes: READ COMMITTED vs REPEATABLE READ in MySQL. Often the better fix is a query that doesn't need one, such as locking an existing row by its primary key, which takes no gap lock.
Conclusion
Under REPEATABLE READ, a locking read also locks the gaps it passes through, including where a missing row would be. That's what makes an INSERT wait on nothing you can see. Look at data_locks for supremum pseudo-record or X,GAP, and avoid the "check, then insert" pattern.
Additional Resources
- InnoDB Locking — gap, next-key and insert intention locks.
- Phantom Rows — why they exist.
