Dev Learning Lab

The labs on this site need JavaScript to run. The same content is written out below.

Learn › Database › Transactions and locking

Gap locks and next-key locks

Why is an INSERT blocked when I queried a row that does not exist?

Transaction A locks rows with SELECT ... FOR UPDATE. MySQL InnoDB does not lock only the rows. Depending on the condition, it also locks the gaps between rows, so that other transactions cannot insert a new row in between.

Checked against real execution · Checked the actual locks and waits of 12 combinations against MySQL 8.4.11

Concepts in this lab

Record locks, gap locks, and next-key locks
The kinds of locks that MySQL InnoDB sets on indexes. A record lock locks one index record. A gap lock locks the empty gap between index records so that no new row can be inserted there. A next-key lock combines a record lock with a gap lock on the gap before that record. Gap locks and next-key locks exist to stop new rows (phantoms) from slipping into a range that was locked with a range condition.
Shared locks and exclusive locks
A shared lock (S) is a lock for reading, and several transactions can hold it at the same time. An exclusive lock (X) is a lock for writing, and only one transaction can hold it. While data has an exclusive lock, no other transaction can take a shared lock or an exclusive lock on it.
Transaction isolation level
A level that sets how much of each other's changes concurrent transactions can see. There are four levels: READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, and SERIALIZABLE. The later levels block more of the effects that transactions have on each other, but the amount of work that can be handled at the same time goes down. The default is REPEATABLE READ in MySQL (InnoDB) and READ COMMITTED in PostgreSQL and Oracle.
Phantom read
A situation in which a transaction runs a query with the same condition twice and gets a different number of rows, because another transaction inserted or deleted rows in between. It differs from a non-repeatable read in that values do not change. Instead, rows that did not exist appear, or rows that existed disappear.
Pessimistic locking
An approach that assumes a conflict will happen and locks the data first, at the time it is read. When you lock a row with SELECT ... FOR UPDATE, another transaction that tries to modify the row waits until the lock is released. It is reliable when conflicts are frequent, but it brings waiting and the possibility of deadlock.
Deadlock
This is a state in which two or more threads (or transactions) can never make progress, because each is waiting for a resource that the other holds. The typical case is that A holds resource 1 and waits for 2 while B holds resource 2 and waits for 1. It can be prevented by always acquiring resources in the same order.

Try these

Running with the default settings

  1. The coupon table has rows with ids 10, 20, 30, and 40, and also rows with much larger ids. In the index, there are gaps between the rows, such as (10~20), (20~30), (30~40), and (after 40).
  2. Transaction A queries ids 20 through 30 using FOR UPDATE. It locks rows 20 and 30, which match the condition. It also locks the gap (20~30) between them. A lock on a row together with the lock on the gap before it is called a next-key lock.
  3. Transaction B tries to insert the row with id = 25. An INSERT checks whether the gap where the new row would go is locked.
  4. The place where id 25 goes is the gap (20~30), and A has locked that gap. The INSERT of B waits until A commits or rolls back. If it waits too long, it fails with a lock wait timeout error.
  5. When A commits, its locks are released, and only then does the INSERT of B that was waiting run.

Result of this run

Code

-- The ids (primary key) in the table: 10, 20, 30, 40, and rows far away from them
-- (the queried range is a small part of the table, so only that range is scanned)

-- Transaction A
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;
SELECT * FROM coupon WHERE id BETWEEN 20 AND 30 FOR UPDATE;

-- Transaction B (A has not committed yet)
INSERT INTO coupon (id) VALUES (25);

Assumptions of this model

The lab is based on queries by primary key (a unique index) in MySQL InnoDB. The following follow the explanation in the MySQL documentation (InnoDB Locking): when a unique index finds exactly one existing row, only a record lock is placed, and when the query looks for a row that does not exist or for a range, a gap lock is placed as well; and at READ COMMITTED, queries do not use gap locks. The boundary of the gap that is actually locked by a range query can be wider than shown here, depending on the index type and the MySQL version (the gap after the end of the range may also be locked). All 12 combinations were run on a real MySQL 8.4.11 with two sessions, and the locks that A held (the record locks, gap locks, and next-key locks recorded in performance_schema.data_locks) and whether the INSERT of B waits were confirmed to match the screen. However, this result is for the case where the range query scans only that range of the index. InnoDB locks the index records that it scans while it searches, not the rows that match the condition. When the test was run with a table that had only the four rows 10, 20, 30, and 40, MySQL chose a plan that scans the entire index, so all the rows and all the gaps were locked, and even the INSERT of id = 45, which is outside the range, waited. The same result as on the screen appeared only after 500 more rows far away from these were inserted, so that the queried range became a small part of the table.

Previous lab: Isolation levels · Next lab: Stock decrement and locks

Concepts in this lab

What this lab shows

Settings

Code


      
    


    

      
Diagram

      

      

    


    

      

      

      

    


  


  

    

      
Run comparison

      

      

    

    

      
Try these