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

定义与问题

定义

1. 幻读：在同一个事务内，前后两次查询同一范围的时候，后一次查询看到了前一次查询没有看到的行。幻读专指新插入的行
2. 在RR隔离级别下，普通查询是快照读，是看不到其他事务插入的数据的。幻读仅在当前读时才会出现

PS:以下问题1,2假设 假设SELECT * FROM t WHERE d=5 FOR UPDATE;只会在id=5这一行上加X Lock

问题1 破坏语义

隔离级别为rc read commit：
PS：实验环境是mysql8，打开三个session SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; 设置当前session的隔离级别为rc。
假设SELECT * FROM t WHERE d=5 FOR UPDATE;只会在id=5这一行上加X Lock，执行时序如下：

时刻	sessionA	sessionB	sessionC
T1	BEGIN;SELECT * FROM t WHERE d=5 FOR UPDATE;result:(5,5,5)
T2		UPDATE t SET d=5 WHERE id=0;UPDATE t SET c=5 WHERE id=0;
T3	SELECT * FROM t WHERE d=5 FOR UPDATE;result:(0,5,5),(5,5,5)
T4			INSERT INTO t VALUES (1,1,5);UPDATE t SET c=5 WHERE id=1;
T5	SELECT * FROM t WHERE d=5 FOR UPDATE;result:(0,5,5),(1,1,5),(5,5,5)
T6	commit;

1. T1返回id=5这1行
2. T3返回id=0和id=5这2行
   1. id=0不是幻读，因为不是新插入的行
   2. T5返回id=0、id=1和id=5的这三行
3. id=1是幻读，因为这是新插入的行
   1. 显然只有行锁（RC）是无法解决幻读问题的

问题2 破坏数据一致性

时刻	sessionA	sessionB	sessionC
T1	BEGIN;SELECT * FROM t WHERE d=5 FOR UPDATE;UPDATE t SET d=100 WHERE d=5;
T2			UPDATE t SET d=5 WHERE id=0;UPDATE t SET c=5 WHERE id=0;
T3	SELECT * FROM t WHERE d=5 FOR UPDATE;
T4		INSERT INTO t VALUES (1,1,5);UPDATE t SET c=5 WHERE id=1;
T5	SELECT * FROM t WHERE d=5 FOR UPDATE;
T6	commit;

1. UPDATE与SELECT…FOR UPDATE的加锁语义一致（X Lock）
2. T1时刻，id=5这一行变成了(5,5,100)，在T6时刻才正式提交
3. T2时刻，id=0这一行变成了(0,5,5)
4. T4时刻，新插入了一行(1,5,5)

上述语句对应的binlog

1. T2时刻，session B事务提交，写入两条语句
2. T4时刻，session C事务提交，写入两条语句
3. T6时刻，session A事务提交，写入UPDATE t SET d=100 WHERE d=5;

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

1. 该binlog如果在备库上执行，最终结果为(0,5,100)，(1,5,100)，(5,5,100)，id=0和id=1这两行数据会与主库不一致
2. 原因：SELECT * FROM t WHERE d=5 FOR UPDATE;只给id=5这一行X Lock

假设加强行锁 假设扫描过程中所有碰到的行，都加上X Lock

时刻	sessionA	sessionB	sessionC
T1	BEGIN;SELECT * FROM t WHERE d=5 FOR UPDATE;UPDATE t SET d=100 WHERE d=5;
T2			UPDATE t SET d=5 WHERE id=0;(blocked)UPDATE t SET c=5 WHERE id=0;
T3	SELECT * FROM t WHERE d=5 FOR UPDATE;
T4		INSERT INTO t VALUES (1,1,5);UPDATE t SET c=5 WHERE id=1;
T5	SELECT * FROM t WHERE d=5 FOR UPDATE;
T6	commit;

1. session A把所有的行都加了X Lock，因此session B在执行第一个update语句时被锁住了
2. 需要等到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)

1. id=0这一行的最终结果也是(0,5,5)，因此id=0这一行的数据是一致的
2. 对于id=1这一行数据而言，在数据库端的结果为(1,5,5)，而根据binlog的执行结果是(1,5,100)，数据不一致
   1. 并且依然存在幻读
3. 原因：只能给加锁时存在的行加X Lock
   1. 在T3时刻，在给所有的行加X Lock时，此时id=1这一行还不存在，因此也就加不上X Lock了
   2. 即使在所有的记录都加上了X Lock，依旧阻止不了插入新纪录

解决方案

Gap Lock

1. 产生幻读的原因：行锁只能锁住行，新插入记录这个动作，要更新的是记录之间的间隙
2. 为了解决幻读，InnoDB引入了新的锁：间隙锁（Gap Lock）

表初始化，插入了6个记录，产生了7个间隙

1. SELECT * FROM t WHERE d=5 FOR UPDATE;
   1. 给已有的6个记录加上X Lock，同时还会加上7个Gap Lock，这样就确保无法再插入新纪录
2. 上锁实体
   1. 数据行
   2. 数据行之间的间隙

冲突关系

行锁

SLock 共享锁与XLock排它锁

	SLock	XLock
SLock	兼容	冲突
XLock	冲突	冲突

间隙锁

Gap Lock是在RR隔离级别下才生效的（在RC隔离级别是没有Gap Lock的）
跟间隙锁存在冲突关系的是往这个间隙插入一个记录的操作_，间隙锁之间不会相互冲突
如果你要尝试下面的例子，你必须先将隔离级别改回来。
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

SessionA	SessionB
BEGIN;SELECT * FROM t WHERE c=7 LOCK IN SHARE MODE;
	BEGIN;SELECT * FROM t WHERE c=7 FOR UPDATE;

1. session B并不会被阻塞，因为表t里面并没有c=7的记录
   1. 因此session A加的是间隙锁(5,10)，而session B也是在这个间隙加间隙锁
   2. 两个session有共同的目标： 保护这个间隙，不允许插入值，但两者之间不冲突

Next-Key Lock

1. 间隙锁和行锁合称Next-Key Lock，每个Next-Key Lock都是左开右闭区间
2. SELECT * FROM t WHERE d=5 FOR UPDATE;形成了7个Next-Key Lock，分别是
   1. (-∞,0],(0,5],(5,10],(10,15],(15,20],(20,25],(25,+supremum]
   2. +supremum：InnoDB给每一个索引加的一个不存在的最大值supremum
3. 约定：Gap Lock为左开右开区间，Next-Key Lock为左开右闭区间

可能死锁

-- 并发执行
-- 死锁并不是大问题，回滚重试即可
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;

SessionA	SessionB
BEGIN;SELECT * FROM t WHERE id=9 FOR UPDATE;
` BEGIN;
SELECT * FROM t WHERE id=9 FOR UPDATE;`
	INSERT INTO t VALUES (9,9,9);(blocked)
INSERT INTO t VALUES (9,9,9);(Deadlock fund)

1. session A执行SELECT * FROM t WHERE id=9 FOR UPDATE;，id=9这一行不存在，会加上间隙锁(5,10)
2. session B执行SELECT * FROM t WHERE id=9 FOR UPDATE;，间隙锁之间不冲突，同样会加上间隙锁(5,10)
3. session B试图插入一行(9,9,9)，被session A的间隙锁阻塞
4. session A试图插入一行(9,9,9)，被session B的间隙锁阻塞，两个session相互等待，形成死锁
   1. InnoDB的死锁检测很快就会发现死锁，并让session A的insert语句报错返回
5. 解决方案：假如只有一个唯一索引，可以用INSERT … ON DUPLICATE KEY UPDATE来替代

小结

1. 引入Gap Lock，会导致同样的语句锁住更大的范围，影响并发度
2. Gap Lock是在RR隔离级别下才生效的（在RC隔离级别是没有Gap Lock的）
3. 解决数据与日志不一致的另一个方案：RC + binlog_format=row
   1. 如果RC（没有Gap Lock，锁范围更小）隔离级别够用，业务并不需要可重复读的保证，可以选择RC

PS：参考资料 《MySQL实战45讲》