Mysql 间隙锁

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20

# create new schema
create schema test;
use test;

# create table test
create table user
(
    id   int primary key,
    age  int
);

alter table user
    add index age_idx (age);

# insert some test data
insert into user
values (3, 10),
       (5, 20),
       (8, 30);
       

1
2
3
4
5
6
7
8
9

# session 1, the row is exist for id = 3 , so it doesn't lock.
begin;

select * from user where id = 3 for update;

# session 2, execute successful.
begin;

insert into user value (1, 20);

1
2
3
4
5
6
7
8
9

# session 1, the row isn't exist for id = 2, so it locks range (,3]
begin;

select * from user where id = 2 for update;

# session 2, execute block.
begin;

insert into user value (1, 20);

1
2
3
4
5
6
7
8
9

# session 1, it locks range [1,5] 
begin;

select * from user where id >= 1 and id <= 5 for update;

# session 2, execute block
begin;

insert into user value (2, 20);

1
2
3
4
5
6
7
8
9

# session 1, it locks range [3,8]
begin;

select * from user where age = 20 for update;

# session 2, execute block.
begin;

insert into user value (4, 20);

1
2
3
4
5
6
7
8
9

# session 1, it locks range [3,5]
begin;

select * from user where age = 15 for update;

# session 2, execute block.
begin;

insert into user value (4, 20);

1
2
3
4
5
6
7
8
9

# session 1, it locks range [3,8]
begin;

select * from user where age >= 12 and age <= 28 for update;

# session 2, execute block.
begin;

insert into user value (4, 20);

1

drop schame test;