Mysql间隙锁实战

前言

本次实战是基于数据库mysql5.7.14

什么是间隙锁？

间隙锁是对索引记录之间间隙的锁，或者对第一个索引记录之前或最后一个索引记录之后的间隙的锁。例如:SELECT c1 FROM t WHERE c1 BETWEEN 10 and 20 For UPDATE;防止其他事务向列t.c1插入值15，无论列中是否已经有这样的值，因为范围内所有现有值之间的差距是锁定的。

什么时候会使用间隙锁？

InnoDB 存储引擎隔离级别是RR前提下同时有事务发生。

间隙锁有什么作用？

首先有个前提是知道数据库隔离级别，RC 中存在问题是不可重复读和幻读，而RR 存在的问题是幻读。 通过MVVC(多版本并发控制) 可以解决不可重复读和部分幻读，为啥是部分幻读，就是因为select *from tb_xxxx where id = x; 的时候读取的还是快照读，但是你要前面查询没加同一个事务第二次是加上lock in share mode（共享锁） 或者for update（排它锁）,读取的就是当前读。这个时候就会出现幻读情况。

操作实战

建表

CREATE TABLE `tb_lock_test` (
  `id` bigint(20) DEFAULT NULL,
  `a` varchar(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert  into `tb_lock_test`(`id`,`a`) values (1,'a1'),(3,'a3'),(5,'a5'),(7,'a7');

测试无索引下的间隙表情况

开启两个Cmd 模拟两个事务
事务A:

事务B模拟插入

发现阻塞了，事务A commit后 释放间隙锁后，执行成功。

插入一个不是查询范围也发现被锁了说明没有索引的间隙锁会把整个表锁住。

增加普通索引测试

ALTER TABLE tb_lock_test ADD INDEX index_id (id)

有普通索引的不在范围内的不会加锁

下面截图发现 在范围的查询和精确的查询都加了间隙锁。间隙范围就是1-3-5 这个间隙中。1、2 和4 都不可以插入。5以及以后就可以。按照咱们测试场景就是【1，5）符合左闭右开

增加唯一索引测试

ALTER TABLE tb_lock_test ADD UNIQUE index_id (id)

有唯一索引的不在范围内的不会加锁，只会在范围查的时候加锁。从截图可以发现范围查找 符合左开右闭 eg:【1,5）

下面截图发现 在精确的查询只加了行锁。其他范围的都可以插入

增加主键索引测试

ALTER TABLE tb_lock_test ADD PRIMARY KEY(id)

从截图可以发现范围查找 符合左开右闭 eg:【1,5）

下面截图发现 在精确的查询只加了行锁。其他范围的都可以插入和唯一索引一样。

总结

• 不同场景使用间隙锁锁的范围不一样，以及开区间闭区间区别。学会灵活运用。
• 虽然InnoDB中通过间隙锁的方式解决了幻读问题，但是加锁之后一定会影响到并发性能，因此，如果对性能要求较高的业务场景中，可以把隔离级别设置成RC，这个级别中不存在间隙锁。