间隙锁的目的是解决幻读的问题
例如:
现在有张学生表(ID,学号,名称,班级)
班级、学号建了索引
create table 学生表
(
ID int auto_increment,
名称 varchar(16) null,
班级 int null,
学号 varchar(16) null,
constraint 学生表_pk
primary key (ID)
);
create index IDX_学号
on 学生表 (学号);
create index IDX_班级
on 学生表 (班级);
INSERT INTO heytea_pos.学生表 (ID, 名称, 班级, 学号) VALUES (1, 'A1', 1, 'A1');
INSERT INTO heytea_pos.学生表 (ID, 名称, 班级, 学号) VALUES (2, 'B1', 1, 'B1');
INSERT INTO heytea_pos.学生表 (ID, 名称, 班级, 学号) VALUES (3, 'C1', 1, 'C1');
INSERT INTO heytea_pos.学生表 (ID, 名称, 班级, 学号) VALUES (4, 'A2', 2, 'A2');
INSERT INTO heytea_pos.学生表 (ID, 名称, 班级, 学号) VALUES (5, 'B2', 2, 'B2');
INSERT INTO heytea_pos.学生表 (ID, 名称, 班级, 学号) VALUES (6, 'A3', 3, 'A3');
INSERT INTO heytea_pos.学生表 (ID, 名称, 班级, 学号) VALUES (7, 'B3', 3, 'B3');
INSERT INTO heytea_pos.学生表 (ID, 名称, 班级, 学号) VALUES (8, 'A4', 4, 'A4');
INSERT INTO heytea_pos.学生表 (ID, 名称, 班级, 学号) VALUES (9, 'B4', 4, 'B4');
ID | 名称 | 班级 | 学号 |
---|---|---|---|
1 | A1 | 1 | A1 |
2 | B1 | 1 | B1 |
3 | C1 | 1 | C1 |
4 | A2 | 2 | A2 |
5 | B2 | 2 | B2 |
6 | A3 | 3 | A3 |
7 | B3 | 3 | B3 |
8 | A4 | 4 | A4 |
9 | B4 | 4 | B4 |
当事务隔离级别为可重复读时,我们在一个事务里执行
SELECT * FROM 学生表 WHERE 班级 = 1 FOR UPDATE
这时理论上会把 班级 = 1的行都锁住(行锁)
但是实际情况是,如果这时班级=1 来了个插班生,而我们只有行锁的话,那个这个插班生的信息是不会被锁住的。
我们可以随意更改这个插班生的信息
这就违背一开始加锁的语义。
所以,间隙锁就很有必要了
间隙锁 锁的是 班级索引上班级为1的索引叶子的间隙。间隙锁和间隙锁之间是不互斥的,间隙锁只阻塞插入,因为插入前需要获取插入意向锁,它和间隙锁互斥。
锁住的间隙范围如下:
0-> (1,1) -> (1,2) -> (1,3) -> (2,4)
这是,如果有插班生(6,‘D1’,1,‘D1’)需要插入则会阻塞。
间隙锁还有其他几种情况
第一种 普通索引范围查询,两头闭合
当事务隔离级别为可重复读时,我们在一个事务里执行
begin;
SELECT * FROM 学生表 force index (IDX_班级) WHERE 班级 >= 2 AND 班级 <= 3 FOR UPDATE
commit;
这条语句可以看成
1.先找到班级为2的第一位学生A2,间隙锁在 (1,3)-> (2,4)
2.在继续查询到班级为3的每一位学生,间隙锁在
(1,3)-> (2,4)-> (2,5)-> (3,6)->(3,7)
在继续查询到班级大于3的的第一位学生A4,间隙锁在(3,7)->(4,8)
这样子插班生(3,10)或者(1,10) 插入会被阻塞住,直到事务提交。
但是插班生(4,10)则不会。
#阻塞
INSERT INTO 学生表 (ID, 名称, 班级, 学号) VALUES (10, 'C3', 3, 'C3');
#阻塞
INSERT INTO 学生表 (ID, 名称, 班级, 学号) VALUES (10, 'D1', 1, 'D1');
#不阻塞
INSERT INTO 学生表 (ID, 名称, 班级, 学号) VALUES (10, 'C4', 4, 'C4');
第二种 普通索引范围查询,一头不闭合
当事务隔离级别为可重复读时,我们在一个事务里执行
SELECT * FROM 学生表 WHERE 班级 > 2 AND 班级 < = 3 FOR UPDATE
这条语句可以看成
先找到班级大于2的第一位学生A3,间隙锁在 (2,5)->(3,6)
在继续查询到班级大于3的第一位学生A4,间隙锁在 (2,5)->(3,6)->(3,7)->(4,8)
这样子插班生(3,10)或者(2,10) 插入会被阻塞住,直到事务提交
插班生(4,10) 或者(1,10) 则不会被阻塞住
#阻塞
INSERT INTO 学生表 (ID, 名称, 班级, 学号) VALUES (10, 'C3', 3, 'C3');
#阻塞
INSERT INTO 学生表 (ID, 名称, 班级, 学号) VALUES (10, 'C2', 2, 'C2');
#不阻塞
INSERT INTO 学生表 (ID, 名称, 班级, 学号) VALUES (10, 'C4', 4, 'C4');
#不阻塞
INSERT INTO 学生表 (ID, 名称, 班级, 学号) VALUES (10, 'D1', 1, 'D1');