20 | 幻读是什么，幻读有什么问题？
建表和初始化语句如下
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);
这个表除了主键 id 外，还有一个索引 c，初始化语句在表中插入了 6 行数据。
Q:下面的语句序列，是怎么加锁的，加的锁又是什么时候释放的呢？
begin;
select * from t where d=5 for update;
commit;
A:
这个语句会命中 d=5 的这一行，对应的主键 id=5，因此在 select 语句执行完成后，id=5 这一行会加一个 写锁， 而且由于两阶段锁协议，这个写锁会在执行 commit 语句的时候释放。
Q:由于字段 d 上没有索引，因此 这条查询语句会做全表扫描。那么，其他被扫描到的，但是不满足条件的 5 行记录上，会不会被加锁呢？
A:会
InnoDB 的默认事务隔离级别是可重复读，所以接下来都是设定在可重复读隔离级别下。
幻读是什么？
如果只在 id=5 这一行加锁，而其他行的不加锁的话，会怎么样。
假设只在 id=5 这一行加行锁的场景如下图：
session A 里执行了三次查询，分别是 Q1、Q2 和 Q3。它们的 SQL 语句相同，都是 select * from t where d=5 for update。这个语句的意思你应该很清楚了，查所有 d=5 的行， 而且使用的是当前读，并且加上写锁。现在，我们来看一下这三条 SQL 语句，分别会返回什么结果。
- Q1 只返回 id=5 这一行；
- 在 T2 时刻，session B 把 id=0 这一行的 d 值改成了 5，因此 T3 时刻 Q2 查出来的是 id=0 和 id=5 这两行；
- 在 T4 时刻，session C 又插入一行（1,1,5），因此 T5 时刻 Q3 查出来的是 id=0、id=1 和 id=5 的这三行。
其中， Q3 读到 id=1 这一行的现象，被称为”幻读”。
幻读指的是一个事务在前后两次查询同一个范围的时候，后一次查询看到了前一次查询没有看到的行。
对”幻读”做一个说明：
- 在可重复读隔离级别下， 普通的查询是快照读，是不会看到别的事务插入的数据的。因此， 幻读在”当前读”下才会出现。
- 上面 session B 的修改结果，被 session A 之后的 select 语句用”当前读”看到， 不能称为幻读。 幻读仅专指”新插入的行”。
因为这三个查询都是加了 for update，都是当前读。而当前读的规则，就是要能读到 所有已经提交的记录的最新值。并且，session B 和 sessionC 的两条语句，执行后就会提交，所以 Q2 和 Q3 就是应该看到这两个事务的操作效果，而且也看到了，这跟事务的可见性规则并不矛盾。
幻读有什么问题？
语义上
首先是语义上的。session A 在 T1 时刻就声明了，”我要把所有 d=5 的行锁住，不准别的事务进行读写操作”。而实际上，这个语义被破坏了。
为了体现语义被破坏，再往 session B 和 session C 里面分别加一条 SQL 语句
session B 的第二条语句 update t set c=5 where id=0，语义是”我把 id=0、d=5 这一行的 c 值，改成了 5″。
由于在 T1 时刻，session A 还只是给 id=5 这一行加了行锁， 并没有给 id=0 这行加上锁。因此，session B 在 T2 时刻，是可以执行这两条 update 语句的。这样，就破坏了 session A 里 Q1 语句要锁住所有 d=5 的行的加锁声明。
session C 也是一样的道理，对 id=1 这一行的修改，也是破坏了 Q1 的加锁声明。
数据和日志逻辑一致性
其次，是数据一致性的问题。
锁的设计目的是确保数据一致性。这种一致性不仅包括此时数据库内部数据状态的一致性，还包括数据和日志的逻辑一致性。
[En]
Locks are designed to ensure data consistency. This consistency includes not only the consistency of the internal data state of the database at this moment, but also the logical consistency of data and logs.
给 session A 在 T1 时刻再加一个更新语句，即：update t set d=100 where d=5。
update 的 加锁语义和 select …for update 是 一致的，所以这时候加上这条 update 语句也很合理。session A 声明说”要给 d=5 的语句加上锁”，就是为了要更新数据， 新加的这条 update 语句就是把它认为加上了锁的这一行的 d 值修改成了 100。
分析执行完成后数据库中的结果。
[En]
Analyze what the results will be in the database after the execution is complete.
- 经过 T1 时刻，id=5 这一行变成 (5,5,100)，当然这个结果最终是在 T6 时刻正式提交的 ;
- 经过 T2 时刻，id=0 这一行变成 (0,5,5);
- 经过 T4 时刻，表里面多了一行 (1,5,5);
- 其他行跟这个执行序列无关，保持不变。
数据也没问题，来看看这时候 binlog 里面的内容。
- T2 时刻，session B 事务提交，写入了两条语句；
- T4 时刻，session C 事务提交，写入了两条语句；
- T6 时刻，session A 事务提交，写入了 update t set d=100 where d=5 这条语句。
如果你把它们放在一起，它是这样的：
[En]
If you put it together, it goes like this:
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;/* 所有 d=5 的行，d 改成 100*/
这个语句序列，不论是拿到备库去执行，还是以后用 binlog 来克隆一个库，这三行的结果，都变成了 (0,5,100)、(1,5,100) 和 (5,5,100)。
也就是说，id=0 和 id=1 这两行，发生了数据不一致。
这是我们假设”select * from t where d=5 for update 这条语句只给 d=5 这一行，也就是 id=5 的这一行加锁”导致的。
把扫描过程中碰到的行，也都加上写锁，再来看看执行效果。
由于 session A 把所有的行都加了写锁，所以 session B 在执行第一个 update 语句的时候就被锁住了。需要等到 T6 时刻 session A 提交以后，session B 才能继续执行。
这样对于 id=0 这一行，在数据库里的最终结果还是 (0,5,5)。在 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)*/
可以看到，按照日志顺序执行，id=0 这一行的最终结果也是 (0,5,5)。所以，id=0 这一行的问题解决了。
但同时你也可以看到，id=1 这一行，在数据库里面的结果是 (1,5,5)，而根据 binlog 的执行结果是 (1,5,100)，也就是说幻读的问题还是没有解决。这是由于在 T3 时刻，我们给所有行加锁的时候 ，id=1 这一行还不存在，不存在也就加不上锁。
也就是说，即使把所有的记录都加上锁，还是阻止不了新插入的记录
如何解决幻读？
间隙锁
产生幻读的原因是， 行锁只能锁住行，但是 新插入记录这个动作，要更新的是记录之间的”间隙”。因此，为了解决幻读问题，InnoDB 只好引入新的锁，也就是间隙锁 (Gap Lock)。
间隙锁，锁的就是两个值之间的空隙。比如开头的表 t，初始化插入了 6 个记录， 这就产生了 7 个间隙。
下图是表 t 主键索引上的行锁和间隙锁
这样，当你执行 select * from t where d=5 for update 的时候，就不止是给数据库中已有的 6 个记录加上了行锁，还同时加了 7 个间隙锁。 这样就确保了无法再插入新的记录。
也就是说这时候， 在一行行扫描的过程中，不仅将给行加上了行锁，还给行两边的空隙，也加上了间隙锁。
行锁分为读锁和写锁。下图显示了这两种类型的行锁之间的冲突关系。
[En]
The row lock is divided into read lock and write lock. The following figure shows the conflicting relationship between these two types of row locks.
也就是说，与行锁冲突的是“另一个行锁”。
[En]
In other words, it is “another row lock” that conflicts with the row lock.
但是间隙锁不一样， 跟间隙锁存在冲突关系的，是”往这个间隙中插入一个记录”这个操作。间隙锁 之间都 不存在冲突关系。
举个例子：
这里 session B 并不会被堵住。因为表 t 里并没有 c=7 这个记录，因此 session A 加的是间隙锁 (5,10)。而 session B 也是在这个间隙加的间隙锁。它们 有共同的目标，即： 保护这个间隙，不允许插入值。但， 它们之间是不冲突的。
next-key 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]。
如果没有特别说明，我们把 间隙锁记为开区间，把 next-key lock 记为前开后闭区间。
Q： supremum 从哪儿来的呢？
A：这是因为 +∞是开区间。实现上，InnoDB 给每个索引加了一个不存在的最大值 supremum，这样才符合前面说的”都是前开后闭区间”。
一些”困扰”
间隙锁和 next-key lock 的引入，帮我们解决了幻读的问题，但同时也带来了一些”困扰”。
要求：任意锁定一行，不存在则插入，存在则更新数据，如下所示：
[En]
Requirements: arbitrarily lock a row, insert it if it does not exist, and update its data if it exists, as follows:
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;
Q：不是 insert … on duplicate key update 就能解决吗？
A：但在有多个唯一键的时候，这个方法是不能满足需求（待填）
遇到的现象是，这个逻辑一旦具有并发性，就会遇到死锁。
[En]
The phenomenon encountered is that once this logic has concurrency, it will encounter a deadlock.
Q：这个逻辑每次操作前用 for update 锁起来，已经是最严格的模式了，怎么还会有死锁呢？
A: 间隙锁导致的死锁
用两个 session 来模拟并发，并假设 N=9
按照语句执行的顺序分析它：
[En]
Analyze it in the order in which the statements are executed:
- session A 执行 select … for update 语句，由于 id=9 这一行并不存在，因此会加上间隙锁 (5,10);
- session B 执行 select … for update 语句，同样会加上间隙锁 (5,10)，间隙锁之间不会冲突，因此这个语句可以执行成功；
- session B 试图插入一行 (9,9,9)， 被 session A 的间隙锁挡住了，只好进入等待；
- session A 试图插入一行 (9,9,9)， 被 session B 的间隙锁挡住了。
至此， 两个 session 进入互相等待状态，形成死锁。当然，InnoDB 的死锁检测马上就发现了这对死锁关系，让 session A 的 insert 语句报错返回了。
间隙锁的引入，可能会导致同样的语句锁住更大的范围，影响了并发度
解决方法
如果把隔离级别设置为读提交的话，就没有间隙锁了。但同时，你要解决可能出现的 数据和日志不一致问题， 需要把 binlog 格式设置为 row。
Original: https://www.cnblogs.com/ydssx7/p/16532558.html
Author: ydssx
Title: MySQL实战45讲 20
原创文章受到原创版权保护。转载请注明出处：https://www.johngo689.com/505125/
转载文章受原作者版权保护。转载请注明原作者出处！