我们知道在MySQL中存在幻读的情况，也就是一个事务在读取某个范围内的记录时，发现了另一个事务在该范围内新增了记录（或者删除了记录），导致两次读取的记录数量不一致，进而产生了“幻觉”一般的现象。也就是说，幻读是指在多个事务同时读取同一范围内的记录时所产生的矛盾现象。
MySQL为了解决幻读一般采用快照读和间隙锁的方式，其中快照读在之前的文章已经多次提及，本篇文章重点介绍间隙锁。
间隙锁意如其名，就是锁定符合条件但是实际不存在的记录，也就是一定的区间，防止其他事务在某个事务执行期间向该区间插入新的记录。
为清楚梳理间隙锁的作用，我们在本文中使用的示例表如下：
CREATE TABLE `t` (
`id` int(11) NOT NULL,
`c` int(11) DEFAULT NULL,
`d` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `c` (`c`)
) ENGINE=InnoDB;
insert into t values(0,0,0),(5,5,5),(10,10,10),(15,15,15),(20,20,20),(25,25,25);
在示例表中执行如下语句：
begin;
select * from t where d=5 for update;
commit;
语句中的select for update就是为了在查询时，对相关语句进行加锁，避免其他用户对该表进行插入、修改、删除等操作，造成表的不一致。
d=5这一行对应主键为Id=5，执行select语句后改行会被加写锁，并在commit后释放。但是由于d列没有索引，所以会被全表扫描，这时候真实的加锁逻辑为：
RC级别下，只会在满足条件的行加行锁（直至事务commit/rollback才会释放），不满足条件的是先加锁然后再直接释放锁；
RR级别下会加行锁+全表间隙锁(next-key lock是左开右闭，间隙锁是左开右开）；
注意，如下的结论都是假设存在，从而引入间隙锁的概念。
如果没有间隙锁，只有行锁，即：上面的语句只会锁住：id=5的这一行数据，那么就会出现如下图所示的场景：
for update在当前读可以理解为：MySQL认为for update已经给当前的行加了写锁，因此没有必要再进行快照读，但是这样会造成幻读的问题。
如果没有间隙锁，就会出现如下的结果：
Q3读到id=1这一行的现象就是”幻读“，即：在同一个事务中，两次读取到的数据不一致的情况可称为幻读和不可重复读，其中幻读针对insert导致的数据不一致，不可重复读针对的delete/update导致的数据不一致。注意：这里的读指的是当前读，比如查询语句中包含for update、in share mode，以及修改删除语句都会开启当前读，否则就是快照读。
SELECT ... LOCK IN SHARE MODE走的是IS锁(意向共享锁)，即在符合条件的rows上都加了共享锁，这样的话，其他session可以读取这些记录，也可以继续添加IS锁，但是无法修改这些记录直到你这个加锁的session执行完成(否则直接锁等待超时)。
SELECT ... FOR UPDATE 走的是IX锁(意向排它锁)，即在符合条件的rows上都加了排它锁，其他session也就无法在这些记录上添加任何的S锁或X锁。如果不存在一致性非锁定读的话，那么其他session是无法读取和修改这些记录的，但是innodb有非锁定读(快照读并不需要加锁)，for update之后并不会阻塞其他session的快照读取操作；
除了select ...lock in share mode和select ... for update这种显示加锁的查询操作。 通过对比，发现for update的加锁方式无非是比lock in share mode的方式多阻塞了select...lock in share mode的查询方式，并不会阻塞快照读
sessionA在T1时刻声明：把所有d=5的行锁住，不允许其他的事务进行读写操作，但是sessionB和sessionC却能够随意改变语义，新增或者通过修改了对应行的值。
锁的设计不仅仅是数据库内存数据状态的一致性，还包括数据与日志在逻辑上的一致性。
如果没有间隙锁，上面的操作在binlog的记录（binlog是在commit提交时进行记录）就是：
/** session B提交语句 */
update t set d=5 where id=0; /*(0,0,5)*/
update t set c=5 where id=0; /*(0,5,5)*/
/** session C提交语句 */
insert into t values(1,1,5); /*(1,1,5)*/
update t set c=5 where id=1; /*(1,5,5)*/
/** session A提交语句 */
update t set d=100 where d=5;/*所有d=5的行，d改成100*/
使用该binlog恢复或者备份，三行中d=100，出现异常；
进一步，我们增加写锁。
在binlog的记录为：
insert into t values(1,1,5); /*(1,1,5)*/
update t set c=5 where id=1; /*(1,5,5)*/
update t set d=100 where d=5;/*所有d=5的行，d改成100*/
update t set d=5 where id=0; /*(0,0,5)*/
update t set c=5 where id=0; /*(0,5,5)*/
因此上面的幻读产生的原因就是说，行锁只是锁住了行，但是新插入记录这个动作，要更新的是记录之间的间隙。这也是InnoDB引入间隙锁（Gap Lock）的原因。
间隙锁的增加逻辑为：
间隙锁和行锁合称 next-key lock，每个 next-key lock 是前开后闭区间。也就是说，我们的表 t 初始化以后，如果用 select * from t for update 要把整个表所有记录锁起来，就形成了 7 个 next-key lock，分别是 (-∞,0]、(0,5]、(5,10]、(10,15]、(15,20]、(20, 25]、(25, +supremum]。
如下的示例，在索引唯一的时候，Insert ... on duplicate key update可用，但是如果有多个唯一键的时候，会有异常。
begin;
select * from t where id=N for update;
/*如果行不存在*/
insert into t values(N,N,N);
/*如果行存在*/
update t set d=N set id=N;
commit;
在并发情况下，即使没有后续的update操作也会引入死锁。
即：间隙锁的引入，可能会导致同样的语句锁住更大的范围，影响并发度。
间隙锁在可重复读隔离级别下才会出现，因此，如果把隔离级别设置为读提交，就可以避免幻读的问题。同时，为了解决可能出现的数据和日志不一致的问题，需要将Binlog的格式设置为row。
举例： 删除 statement记录的是这个删除的语句，例如： delete from t where age>10 and modified_time<='2020-03-04' limit 1 而row格式记录的是实际受影响的数据是真实删除行的主键id，例如： delete from t where id=3 and age=12 and modified_time='2020-03-05'
那为什么RR级别不需要修改binlog_format呢：
如果业务不需要可重复读场景，考虑在读提交下操作数据的锁范围更小（没有间隙锁），这个选择是合理的。
可重复读的场景举例，比如说：金融业务，财务需要统计过去一段时间内某些数据，需要反复根据某些条件查找，此时如果有新数据行插入，会导致统计时发生数据不一致的情况，此时需要使用可重复读的隔离级别。
又比如说逻辑备份时，mysqldump备份线程会设置为可重复读，这样在导数据时就会启动一个事务，确保拿到一致性视图。由于MVCC的支持，过程中数据可正常更新。使用可重复读，是为了保证备份的数据都是那一时刻的最新数据，然后通过binlog再做后续的恢复即可。
业务线程是读提交，备份线程是可重复读，同时存在两种事务隔离级别，是否会冲突？
答案是不会，因为不管是RC还是RR，都是MVCC支持，唯一不同在于生成快照的时间点不同，也就是能够看到的数据版本不同，所以并不影响。备份完成后，恢复为RC即可。
加锁规则总结如下：
原则2也就解释了：
本节还是使用章节组开始的表进行说明。
表中没有id=7的记录，因此：
因此，插入id=8的记录会被锁住，等待sessionA锁释放，sessionC修改id=10这一行可以正常执行。
这个例子说明的就是原则2中的对象。
注意：sessionA要给索引c=5加读锁，而且是索引c获取主键，实际上就是覆盖索引，不需要回表。
同时需要注意的是：
因此，这里也就存在说，如果要使用lock in share mode给行家读锁防止数据行被更新，就必须绕过覆盖索引的优化
对于表t，如下两条语句的加锁范围完全不同，语句1只会加行锁，那么语句2呢?
mysql> select * from t where id=10 for update;
mysql> select * from t where id>=10 and id<11 for update;
此时sessionA锁的范围为id=10的行锁和(10,15]的间隙锁，因此sessionB和sessionC被阻塞；
可以使用语句“select * from performance_schema.data_locks”表获取加锁的数据。
使用索引c进行范围查询：
由于c不是唯一索引，因此需要加(5,10]和(10,15]两个next-key lock，因此后两个会话的操作全部被阻塞。
注意，这个bug在8.0.18版本及之后的版本已经优化，不再存在。
session A 是一个范围查询，按照原则 1 的话，应该是索引 id 上只加 (10,15]这个 next-key lock，并且因为 id 是唯一键，所以循环判断到 id=15 这一行就应该停止了。
但是实现上，InnoDB 会往前扫描到第一个不满足条件的行为止，也就是 id=20。而且由于这是个范围扫描，因此索引 id 上的 (15,20]这个 next-key lock 也会被锁上。
执行插入语句：
mysql> insert into t values(30,10,30);
虽然有两个c=10的索引，但是主键不同，因此，c=10记录存在间隙。
sessionA在遍历的时候，先访问到第一个c=10的记录，根据原则1，加锁为：(c=5,id=5)到(c=10,id=10)这个next-key lock，即c的索引为(5,10]。
然后sessionA向右查找，直至(c=15,id=15)，循环结束。根据优化2，等值查询，退化为(c=10,id=10)到(c=15,id=15)的间隙锁，即c的索引为(10,15)；
主键索引上，增加了行锁id=10和id=30；
因此，索引c上的加锁范围为下图蓝色区域：
蓝色两边是虚线，表示开区间，即 (c=5,id=5) 和 (c=15,id=15) 这两行上都没有锁。
这里再次举例： 如果session b插入(4,5,50)，不会被锁，如果插入（6,5,50） 会被锁住，因为二级索引的叶子节点存储的是主键值，二级索引的叶子节点也是有序的，这样6,5,50根据二级索引来排的话 是在5,5,10后面的 。
sessionA的delete语句加了limit 2，表内只有两条数据，删除效果一样，但是加锁效果不同。
delete语句加了limit 2的限制，遍历到(c=10,id=30)这一行之后，满足条件的语句已经有两条，循环结束。因此，索引c的加锁范围变成了（c=5,id=5) 到（c=10,id=30) 这个前开后闭区间。
因此说，在执行删除的时候尽量加Limit，但是这里需要注意的是，删除的行数不清楚，可能会带来业务的bug。
session B 的“加 next-key lock(5,10] ”操作，实际上分成了两步，先是加 (5,10) 的间隙锁，加锁成功；然后加 c=10 的行锁，这时候才被锁住的。也就是说，我们在分析加锁规则的时候可以用 next-key lock 来分析。但是要知道，具体执行的时候，是要分成间隙锁和行锁两段来执行的。
就算分成了两步，为什么session B加(5,10)就能成功呢？session A不是加了(5, 10]的锁吗？ 前面应该也是提到过的，间隙锁和间隙锁之间并不冲突，间隙锁和insert到这个间隙的语句才会冲突，因此session B加间隙锁(5, 10)是可以成功的，但是如果往(5, 10)里面插入的话会被阻塞。 但是如果直接加next-key lock(5, 10]，那么肯定是会被阻塞的，因此这个例子确实说明，加锁的步骤是分两步的，先是间隙锁，后是行锁。而且只要理解了间隙锁和行锁之间冲突的原则是不一样的，也就很容易理解这两个锁并不是一起加的了。
京公网安备 11010802035086号