mysql演示幻读现象 mysql幻读解决

1 数据准备

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

如下语句怎么加锁，何时释放？

begin;
select * from t where d=5 for update;
commit;

该语句会命中d=5这行，对应主键id=5。
因此在select 语句执行完后，id=5这行会加写锁。因两阶段锁协议，写锁会在执行commit语句时释放。

由于字段d无索引，该查询语句会全表扫。那其他被扫到但不满足条件的5行记录会不会被加锁？

2 幻读

假设只在id=5一行加行锁

	SA	SB	SC
T1	begin select * from t where d=5 for update; // Q1 result (5,5,5);
T2		update t set d=5 where id=0;
T3	select * from t where d=5 for update; // Q2 result (0,0,5) (5,5,5)
T4			insert into t values(1,1,5)
T5	select * from t where d=5 for update; // Q3 result (0,0,5) (1,1,5) (5,5,5)
T6	commit

SA执行三次查询-Q1、Q2和Q3，SQL相同：查所有d=5的行，且使用当前读并加写锁。

• Q1只返回id=5一行
• T2时，S B把id=0一行的d值改成5，因此T3时Q2查出来的是id=0和id=5这两行
• T4时，S C插入（1,1,5），因此T5时Q3查出来的是id=0、id=1和id=5的三行

Q3读到id=1这行称为“幻读”，即一个事务在前后两次查询同一范围时，后一次查询看到前一次查询没看到的行。
可重复读下，普通查询是快照读，不会看到别的事务插入的数据。因此，幻读在“当前读”才会出现。SB修改结果被SA之后的select语句用“当前读”看到，不能称为幻读。幻读仅专指新插入的行而非更新。

这三查询都加for update，都是当前读。当前读就是要能读到所有已提交的记录的最新值。
SB和SC的两条语句，执行后就会提交，所以Q2和Q3应看到这俩事务的操作效果，所以这和事务的可见性不矛盾。

但这里还真有问题。

3 幻读的问题

3.1 语义问题

SA T1时刻就声明，“我要把所有d=5的行锁住，不准别的事务读写”。而这语义被破坏了。

再往SB和SC里分别加条SQL语句，再看会咋样。假设只在id=5这行加行锁：

	SA	SB	SC
T1	begin select * from t where d=5 for update; // Q1
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; // Q2 result (0,0,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; // Q3 result (0,0,5) (1,1,5) (5,5,5)
T6	commit

SB的第二条语句update t set c=5 where id=0，由于在T1，SA还只是给id=5这行加行锁， 并未给id=0这行加锁。
因此，SB在T2，可执行这两条update。这就破坏了SA里Q1语句要锁住所有d=5的行的加锁声明。
同理，SC对id=1这行的修改，也破坏了Q1的加锁声明。

3.2 数据一致性问题

锁是为保证数据一致性。而这一致性，不止是DB内部数据状态在此刻的一致性，还包含数据和日志在逻辑上的一致性。

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

假设只在id=5这一行加行锁：

	SA	SB	SC
T1	begin select * from t where d=5 for update; // Q1 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; // Q2
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; // Q3
T6	commit

update加锁语义和select …for update 一致。SA声明说“要给d=5的语句加锁”，就是为更新数据，新加的这条update语句就是把它认为加上锁的这行d值改成100。

执行结果：

• T1后，id=5这行变成 (5,5,100)，该结果最终在T6提交
• T2后，id=0这行变成(0,5,5)
• T4后，表里多了行(1,5,5)

其他行跟该执行序列无关，保持不变。这样看，数据也没啥问题，但看binlog：

• T2，SB事务提交，写入两条语句
• T4，SC事务提交，写入两条语句
• T6，SA事务提交，写入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

只给d=5 id=5这行加锁”导致。

所以我们认为，上面设定不合理，要改。

4 怎么改？

把扫描过程中碰到的行，都加上写锁，再看执行效果。

假设扫描到的行都被加行锁：

	SA	SB	SC
T1	begin select * from t where d=5 for update; // Q1 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; // Q2
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; // Q3
T6	commit

由于SA把所有行都加了写锁，所以SB在执行第一个update语句时就被锁住。等到T6时SA提交后，SB才能继续执行。

这样，对id=0这行，DB最终结果还是 (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这行，在DB里结果是(1,5,5)，而根据binlog执行结果(1,5,100)，即幻读依旧。

• 为何把所有记录都加锁，还阻止不了id=1这行的插入和更新？
  T3时，给所有行加锁时，id=1这行还不存在，不存在也就加不上锁。即使把所有记录都加锁，还是阻止不了新插入的记录，这也是为何“幻读”会被单独拿出来解决。

5 InnoDB解决幻读

5.1 幻读的原因

行锁只能锁行，但新插入记录这个动作，要更新的是已有记录之间的“间隙”。因此，为解决幻读，InnoDB需引入间隙锁(Gap Lock)，锁住两值之间的空隙。如表t，初始化插入6个记录，就产生7个间隙。

当执行

select * from t where d=5 for update

就不止是给已有6个记录加行锁，还加了7个间隙锁。这就确保无法再插入新记录。即在一行行扫描过程中，不仅给行加上行锁，还给行两边的空隙加了间隙锁。

数据行是可加锁的实体，数据行之间的间隙，也是可加锁的实体。

5.2 行锁间的冲突关系

跟行锁有冲突关系的是“另一个行锁”。

但间隙锁不一样，跟间隙锁存在冲突关系的，是“往这个间隙中插入一个记录”这操作。间隙锁之间不存在冲突关系。

5.3 间隙锁之间不互锁的案例

SA	SB
begin select * from t where c=7 lock in share mode
	begin select * from t where c=7 for update

SB不会被堵住。因为表t无c=7，因此SA加间隙锁(5,10)。而SB也是在这间隙加的间隙锁。它们有共同目标，保护这个间隙，不允许插入值。但它们之间不冲突。

间隙锁和行锁合称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]。

supremum是啥？

因为+∞是开区间，代码实现上，InnoDB给每个索引加了不存在的最大值supremum，就符合后闭区间了。

间隙锁和next-key lock解决幻读，但也带来“困扰”。

6 案例

6.1 需求

任意锁住一行，若该行:

• 不存在，就插入
• 存在，就更新数据

6.2 实现

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;

就这？

insert … on duplicate key update

不就能解决。但有多个唯一键时，该方法就无法满足需求了。

6.3 分析

该逻辑一旦有并发，就可能死锁。可这逻辑每次操作前用for update锁了，已是最严格模式，怎么还能死锁？

6.4 模拟-间隙锁导致的死锁

两个session并发，假设N=9。

SA	SB
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) (阻塞)
insert into t values(9,9,9) Error 1213(40001):DeadLock found

无需用到后面的update语句，就已死锁。

• SA执行select … for update，由于id=9这行不存在，加间隙锁(5,10)
• SB执行select … for update，同理加间隙锁(5,10)，间隙锁之间不冲突，因此可执行成功
• SB想新增(9,9,9)，被SA的间隙锁挡住，被阻塞
• SA想新增(9,9,9)，被SB的间隙锁挡住

session互等形成死锁。当然，InnoDB的死锁检测马上就发现这对死锁关系，让SA的insert语句报错返回。所以间隙锁的引入可能导致同样的语句锁住更大的范围。

7 总结

无特别说明，本文都是RR 隔离级别，因为间隙锁在RR下才生效。

若设置为RC，就没间隙锁。但同时，要解决可能出现的数据和日志不一致问题，要把binlog格式设为row。这也是互联网常用配置。若RC够用，即业务无需保证RR，考虑到RC下的操作数据的锁范围更小（无间隙锁），选择RC就是合适的。

若都用RC，可逻辑备份时，mysqldump为何要把备份线程设置成RR？然后，在备份期间，备份线程用RR，而业务线程用RC。同时存在两种事务隔离级别，会有问题吗？

即使给所有行加上行锁，仍无法解决幻读，因此引入间隙锁。

行锁确实比较直观，判断规则也相对简单，间隙锁的引入会影响系统的并发度，也增加锁分析的复杂度，但有章可循。