Understanding InnoDB Row Locks: Shared, Exclusive, Gap, Next‑Key, and Insert‑Intention Locks

1. Preparation

Verify that the transaction isolation level is REPEATABLE-READ :

show variables like 'transaction_isolation';

Create a test table and insert sample data:

CREATE TABLE `t1` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `i1` int DEFAULT '0',
  PRIMARY KEY (`id`) USING BTREE,
  KEY `idx_i1` (`i1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

INSERT INTO `t1` (`id`,`i1`) VALUES (10,101),(20,201),(30,301),(40,401);

Prepare a query to inspect lock information:

select engine_transaction_id, object_name, lock_type, lock_mode, lock_status, lock_data
from performance_schema.data_locks
where object_name = 't1' and lock_type = 'RECORD'\G

2. Shared & Exclusive Locks

InnoDB row locks are divided into shared (S) and exclusive (X) locks, each further classified into three types:

Record lock (LOCK_REC_NOT_GAP)

Gap lock (LOCK_GAP)

Next‑Key lock (LOCK_ORDINARY)

Exclusive locks also include a special LOCK_INSERT_INTENTION used by INSERT statements.

3. Record Locks

Record locks protect the row itself without locking the preceding gap. Multiple transactions can acquire shared record locks simultaneously, but exclusive record locks are mutually exclusive.

Example of a shared record lock:

begin;
select * from t1 where id = 10 lock in share mode;
-- check lock status
***************************[ 1. row ]***************************
engine_transaction_id | 281479865470888
object_name           | t1
lock_type             | RECORD
lock_mode             | S,REC_NOT_GAP
lock_status           | GRANTED
lock_data             | 10

Example of an exclusive record lock:

begin;
select * from t1 where id = 10 for update;
-- check lock status
***************************[ 1. row ]***************************
engine_transaction_id | 221456
object_name           | t1
lock_type             | RECORD
lock_mode             | X,REC_NOT_GAP
lock_status           | GRANTED
lock_data             | 10

4. Gap Locks

Gap locks protect the interval before a row. Both shared and exclusive gap locks exist, and they do not block each other.

Shared gap lock example:

begin;
select * from t1 where id < 10 lock in share mode;
-- check lock status
***************************[ 1. row ]***************************
engine_transaction_id | 281479865470888
object_name           | t1
lock_type             | RECORD
lock_mode             | S,GAP
lock_status           | GRANTED
lock_data             | 10

Exclusive gap lock example:

begin;
update t1 set i1 = i1 + 66 where id < 10;
-- check lock status
***************************[ 1. row ]***************************
engine_transaction_id | 221457
object_name           | t1
lock_type             | RECORD
lock_mode             | X,GAP
lock_status           | GRANTED
lock_data             | 10

Three concurrent sessions demonstrate that shared and exclusive gap locks can coexist without blocking each other.

5. Next‑Key Locks

Next‑Key locks combine the effects of a record lock and a gap lock, locking both the row and the preceding gap. Their lock_mode is simply S or X , and they are mutually exclusive.

Shared Next‑Key lock example:

begin;
select * from t1 where id <= 10 lock in share mode;
-- check lock status
***************************[ 1. row ]***************************
engine_transaction_id | 281479865470888
object_name           | t1
lock_type             | RECORD
lock_mode             | S
lock_status           | GRANTED
lock_data             | 10

Exclusive Next‑Key lock example:

begin;
update t1 set i1 = i1 + 66 where id <= 10;
-- check lock status
***************************[ 1. row ]***************************
engine_transaction_id | 221459
object_name           | t1
lock_type             | RECORD
lock_mode             | X
lock_status           | GRANTED
lock_data             | 10

When a transaction already holds a record lock and then requests a Next‑Key lock on the same row, InnoDB upgrades to a gap lock instead of a true Next‑Key lock.

6. Insert‑Intention Locks

Insert‑intention locks are a special kind of gap lock used only by INSERT statements. They are represented by INSERT_INTENTION in the lock_mode.

Two sessions illustrate the blocking behavior:

-- session 1
begin;
select * from t1 where id <= 10 lock in share mode;

-- session 2
begin;
insert into t1(id,i1) values (5,51);
-- check lock status
***************************[ 1. row ]***************************
engine_transaction_id | 221455
object_name           | t1
lock_type             | RECORD
lock_mode             | X,GAP,INSERT_INTENTION
lock_status           | WAITING
lock_data             | 10

The INSERT statement must wait because the gap is held by a shared Next‑Key lock.

7. Summary

• Record locks lock only the row (lock_mode contains REC_NOT_GAP ) and are mutually exclusive between shared and exclusive modes.

• Gap locks lock only the preceding gap (lock_mode contains GAP ) and allow shared and exclusive locks to coexist.

• Next‑Key locks lock both the row and its gap (lock_mode is S or X ) and behave like ordinary row locks regarding mutual exclusion.

• Insert‑intention locks are a variant of gap locks with the INSERT_INTENTION flag.