MySQL：逃不掉的锁事，间隙锁( 二 ) _MySQL

1.2 幻读的问题1.2.1 语义上的问题sessionA在T1时刻声明：把所有d=5的行锁?。?辉市砥渌?氖挛窠?卸列床僮?nbsp;，但是sessionB和sessionC却能够随意改变语义，新增或者通过修改了对应行的值。

文章插图
1.2.2 数据一致性问题锁的设计不仅仅是数据库内存数据状态的一致性，还包括数据与日志在逻辑上的一致性。

文章插图
如果没有间隙锁，上面的操作在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，出现异常；
进一步，我们增加写锁。

文章插图
【MySQL：逃不掉的锁事，间隙锁】在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)*/

2 幻读的解决方法2.1 next-key lock因此上面的幻读产生的原因就是说，行锁只是锁住了行，但是新插入记录这个动作，要更新的是记录之间的间隙。这也是InnoDB引入间隙锁（Gap Lock）的原因。

文章插图
间隙锁的增加逻辑为：

对主键或者唯一索引，如果当前读时，where条件全部精准命中（=或者in），这种场景本身就不会产生幻读，所以只会加行记录锁；
没有索引的列，当前读操作时，会加全表的gap锁；
非唯一索引列，如果where条件部分命中（>/</like等）或者全部没有命中，则会加附近Gap间的间隙锁；例如，某表数据如下，非唯一索引2,6,9,9,11,15 。如下语句要操作非唯一索引列9的数据，gap锁将会锁定的列是(6,11]，该区间内无法插入数据。
跟间隙锁存在冲突关系的，是“往这个间隙中插入/更新/删除一条新的记录”这个操作，间隙锁之间不存在冲突关系。

间隙锁和行锁合称 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] 。
2.2 next-key lock引入的问题如下的示例，在索引唯一的时候，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操作也会引入死锁。

sessionA执行select ... for update语句，由于id=9不存在，因此会加上间隙锁（5，10）；
sessionB执行select ... for update语句，由于id=9不存在，因此会加上间隙锁（5，10），间隙锁之间不存在冲突，因此可以执行成功；
session B 试图插入一行 (9,9,9)，被 session A 的间隙锁挡住了，只好进入等待；
session A 试图插入一行 (9,9,9)，被 session B 的间隙锁挡住了。

即：间隙锁的引入，可能会导致同样的语句锁住更大的范围，影响并发度。
2.3 读提交+row模式的Binlog解决幻读间隙锁在可重复读隔离级别下才会出现，因此，如果把隔离级别设置为读提交，就可以避免幻读的问题。同时，为了解决可能出现的数据和日志不一致的问题，需要将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'