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

定义与问题

定义

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

解决思路

只有行锁

假设SELECT * FROM t WHERE d=5 FOR UPDATE;只会在id=5这一行上加X Lock，执行时序如下：

时刻	session A	session B	session C
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;

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

幻读的问题

破坏语义

session A在T1时刻声明：锁住所有d=5的行，不允许其他事务进行读写操作
session B在T2时刻修改了id=0,d=5这一行
session C在T4时刻修改了id=1,d=5这一行

破坏数据一致性

数据

时刻	session A	session B	session C
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;

UPDATE与SELECT...FOR UPDATE的加锁语义一致（X Lock）
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;

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;只给id=5这一行X Lock

加强行锁

增强为：扫描过程中所有碰到的行，都加上X Lock，执行序列如下

时刻	session A	session B	session C
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;

session A把所有的行都加了X Lock，因此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)，数据不一致
- 并且依然存在幻读
原因：只能给加锁时存在的行加X Lock
- 在T3时刻，在给所有的行加X Lock时，此时id=1这一行还不存在，因此也就加不上X Lock了
- 即使在所有的记录都加上了X Lock，依旧阻止不了插入新纪录

解决方案

Gap Lock

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

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

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

冲突关系

行锁

行锁的冲突关系（跟行锁有冲突关系的是另一个行锁）

	S Lock	X Lock
S Lock	兼容	冲突
X Lock	冲突	冲突

间隙锁

跟间隙锁存在冲突关系的是_往这个间隙插入一个记录的操作，间隙锁之间不会相互冲突_

session A	session B
BEGIN; SELECT * FROM t WHERE c=7 LOCK IN SHARE MODE;
	BEGIN; SELECT * FROM t WHERE c=7 FOR UPDATE;

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

Next-Key Lock

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

session A	session B
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)

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

小结

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