MySQL gap lock, next key lock by example

MySQL’s gap lock or next key lock is a specific mechanism to guarantee it’s isolation level.

Gap lock is set on before or after values of the accessed record. Next key lock is the accessed row lock + gap lock. Therefore they have similar features.

Gap lock is activated in the following conditions.

• Isolation level is Repeatable read (default value)
• Storage engine is innodb
• The query is executed in shared mode or exclusive mode

Example table schema

create table gap_lock_test(
col1 varchar(10) primary key,
col2 varchar(20)
);

create index gap_lock_test_idx1 on gap_lock_test(col2);

insert into gap_lock_test values('1', '1');
insert into gap_lock_test values('3', '3');
insert into gap_lock_test values('5', '5');

Lock example 1 – lock within the range

Seq	Session 1	Session 2
1	set session transaction isolation level REPEATABLE READ; start transaction;	set session transaction isolation level REPEATABLE READ; start transaction;
2	select * from gap_lock_test where col1 between ‘1’ and ‘3’ lock in share mode; => ‘1’, ‘3’ returned
3		insert into gap_lock_test values(‘2’, ‘2’); => lock wait
4	commit;	the lock is released
5		rollback;

Lock example 2 – lock above the range

Seq	Session 1	Session 2
1	set session transaction isolation level REPEATABLE READ; start transaction;	set session transaction isolation level REPEATABLE READ; start transaction;
2	select * from gap_lock_test where col1 between ‘1’ and ‘5’ lock in share mode; => ‘1’, ‘3’, ‘5’ returned
3		insert into gap_lock_test values(‘6’, ‘6’); => lock wait
4	commit;	the lock is released
5		rollback;

Lock example3 – No gap lock is set for equal query

No gap lock is set if a record is accessed with a unique index and equal condition.

Seq	Session 1	Session 2
1	set session transaction isolation level REPEATABLE READ; start transaction;	set session transaction isolation level REPEATABLE READ; start transaction;
2	select * from gap_lock_test where col1 = ‘5’ for update;
3		insert into gap_lock_test values(‘6’, ‘6’); => no wait
4	commit;
5		rollback;

Lock Example 4 – Gap lock is set for equal query

Gap lock is set for equal query with non unique index.

Seq	Session 1	Session 2
1	set session transaction isolation level REPEATABLE READ; start transaction;	set session transaction isolation level REPEATABLE READ; start transaction;
2	select * from gap_lock_test where col2 = ‘5’ for update; * the record is accessed with non unique index
3		insert into gap_lock_test values(‘6’, ‘6’); => lock wait
4	commit;	the lock is released
5		rollback;

 

Advertisement