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;

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.