20 | 搞清楚幻读本质（搞清楚什么读？搞什么幻读？什么清楚幻读）

一、上期问题再跟进

现有SQL如下：

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);

上期SQL问题如下：
begin;
select * from t where d=5 for update;
commit;
这个语句是怎么加锁的？

二、逐层拆解

🏁：1.首先明白什么是幻读？【打死都要记住】
幻读指的是一个事务在前后两次查询同一个范围的时候，后一次查询看到了前一次查询没有看到的行。

🏁：2.幻读出现的场景是什么？
在RR隔离级别下，快照读不会看到别的事务插入的行数，只有当前读才会出现幻读的现象。
PS：幻读针对的是“新插入行”。

⭐️：3.幻读带来的问题是什么？（RR隔离级别下）

1. 语义问题
   session A 在 T1 时刻就声明了，“我要把所有 d=5 的行锁住，不准别的事务进行读写操作”。而实际上，这个语义被破坏了。

2. 数据一致性问题（⭐️锁设计的初衷）

   对session A 在 T1 时刻再加一个更新语句，即：update t set d=100 where d=5。

   加上这一句话会有什么问题呢？ 如果仅从执行过程中没有发现问题，但是如果从库根据binlog回放和数据同步的时候会发现数据不一致。即，所有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)*/
   
   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;/**/
   

   ⭐️问题的本质在于：锁只锁住了id=5这一行，所以对于其他事物的更新和插入【幻读】是不起作用的。

🏁：4.至此，MySQL的Innodb引擎是如何解决幻读？由于，行锁只能锁住行，但是新插入记录这个动作，要更新的是记录之间的“间隙”。所以MySQL引入了间隙锁（Gap lock）

举例：比如文章开头的表 t，初始化插入了 6 个记录，这就产生了 7 个间隙。

🏁：5.Gap lock的特点。打死都要记住！！！！

• 跟间隙锁存在冲突关系的，是“往这个间隙中插入一个记录”这个操作。间隙锁之间都不存在冲突关系。
• 间隙锁和行锁合称 next-key lock，每个 next-key lock 是前开后闭区间。
• 间隙锁的引入会造成并发度的下降。具体原因见第6项。

🤔️：6.一个有意思的问题
业务逻辑这样的：任意锁住一行，如果这一行不存在的话就插入，如果存在这一行就更新它的数据，代码如下：

/假设N=9/
begin;
select * from t where id=9 for update; – a1
/如果行不存在/
insert into t values(9,9,9); --a2
/如果行存在/
update t set d=9 set id=9; --a3
commit;

现象是：这个逻辑一旦有并发，就会碰到死锁。你一定也觉得奇怪，这个逻辑每次操作前用 for update 锁起来，已经是最严格的模式了，怎么还会有死锁呢？

⚠️：这个不是 insert … on duplicate key update 就能解决吗？但其实在有多个唯一键的时候，这个方法是不能满足这位提问同学的需求的。后面会有解释为什么？此处自己也不明白。-- mark一下。

答案：当并发执行a1时会加间隙锁（5-10]，到a2时刻就会造成两个线程锁相互等待（PS：这里根本到不了a3时刻）。

🏁：7.间隙锁是在可重复读隔离级别下才会生效的。所以，你如果把隔离级别设置为读提交的话，就没有间隙锁了。但同时，你要解决可能出现的数据和日志不一致问题，需要把 binlog 格式设置为 row。

🏁：8.业务选择的标准，不需要可重复读的保证，这样考虑到读提交下操作数据的锁范围更小（没有间隙锁），这个选择是合理的。

三、课后题

这里 session B 和 session C 的 insert 语句都会进入锁等待状态。你可以试着分析一下，出现这种情况的原因是什么？PS：其中 session C 被锁住这个分析是有点难度的。

四、自古评论出人才
🏁：9.在同一个事务中，两次读取到的数据不一致的情况称为幻读和不可重复读。幻读是针对insert导致的数据不一致，不可重复读是针对 delete、update导致的数据不一致。

🏁：10.innodb_locks_unsafe_for_binlog 这个参数就是这个意思 “不加gap lock”。（PS：8.0就没有了）

🏁：11.怎么开启的锁日志？set global innodb_status_output_locks=1;
然后使用show engine innodb status\G 查看，TRANSACTIONS 相关的信息下，就能看到锁信息。

🤔️：12？不懂。读提交隔离级别一般没有gap lock，不过也有例外情况， 比如insert 出现主键冲突的时候，也可能加间隙锁

🤔️：13？不懂。mysql官方提到自增锁是个表级锁，以及实际项目中高并发insert是否需要避免自增主键？
innodb_auroinc_lock_mode设置为2，binlog_formate设置成row就行，没有表锁问题。参考：https://dev.mysql.com/doc/refman/5.7/en/innodb-auto-increment-handling.html