【十四】MySQL Innodb RR隔离级别下到底是不是解决了幻读

我之前一直质疑网传mysql innodb的RR隔离级别下，next-key lock解决了幻读这种说法的准确性。

这次理顺了。

例子准备：

场景一、select * from user 

order	sessionA	sessionB	结果	解释
T1	start transaction;
T2		start transaction;
T3		select * from user;	成功	有三条 快照读
T4	insert into user (mobile_phone,age) values ('1234',121);		成功
T5		select * from user;	成功	只有三条 因为是快照读，且事务A还没提交。 此处是MVCC避免了幻读
T6		select * from user where id = 1;	成功
T7		select * from user where id = 3;	成功
T8		select * from user lock in share mode;	等待	当前读，加共享锁，而事务A锁定了刚insert的id4
	commit;			此时事务B的等待结束，T8的结果是有四条，看到了事务A的insert的内容 出现幻读
		select * from user;	成功	只有三条 因为是快照读。 此处是MVCC避免了幻读
		select * from user lock in share mode;	成功	有四条，看到了事务A的insert的内容 出现幻读

看一下next-key lock的含义：当sql语句按照范围查询非唯一索引列，并且有数据命中的时候会给索引加上临键锁，锁住命中索引项的前一个索引到命中索引项的后一个索引之间的一个左开右闭区间

 该场景结论：

select *  from user 是快照读，是MVCC避免了幻读

select * from user lock in share mode，当前读，触发了幻读。

善意的理解，这种查询SQL并没有走非唯一索引，也不是范围查询，所以next-key lock并没有生效。

换句话说：准确的说，mysql innodb 的 RR隔离下，一样会出现幻读，next-key lock和MVCC只解决了部分幻读的场景。

场景二、select * from user where age >60 and age < 100

order	sessionA	sessionB	结果	解释
T1	start transaction;
T2		start transaction;
T3		select * from user where age >60 and age < 100;	成功	有2条 快照读
T4	insert into user (mobile_phone,age) values ('1234',121); insert into user (mobile_phone,age) values ('1234',50); insert into user (mobile_phone,age) values ('1234',101); insert into user (mobile_phone,age) values ('1234',61); insert into user (mobile_phone,age) values ('1234',91);
T5		select * from user where age >60 and age < 100	成功	有2条 快照读
T6		select * from user where id = 1;
T7		select * from user where id = 3;
T8		select * from user where age >60 and age < 100 lock in share mode;	等待	当前读，加共享锁，而事务A锁定了刚insert的id45678
	commit;			此时事务B的等待结束，T8的结果是有四条，看到了事务A的insert的内容 出现幻读
		select * from user where age >60 and age < 100;	成功	只有三条 因为是快照读。 此处是MVCC避免了幻读
		select * from user where age >60 and age < 100 lock in share mode;	成功	有四条，看到了事务A的insert的内容 出现幻读

该场景结论同场景一一样

场景三、 select * from user where age >60 and age < 100 lock in share mode;

order	sessionA	sessionB	结果	解释
T1	start transaction;
T2		start transaction;
T3		select * from user where age >60 and age < 100 lock in share mode;	成功	有2条 此时加上了next-key lock age=0 到 aget=120
T4	insert into user (mobile_phone,age) values ('1234',121);		成功
		select * from user where age >60 and age < 100 lock in share mode;		有2条 该区间内本来也只有2条
		select * from user where age >60 and age < 100;		有2条 该区间内本来也只有2条
	insert into user (mobile_phone,age) values ('1234',0);		等待	因为T3加了next-key lock
	UPDATE user  set mobile_phone = '11111' WHERE age = 120;		等待	因为T3加了next-key lock
T5	insert into user (mobile_phone,age) values ('1234',50);		等待	因为T3加了next-key lock
T7	insert into user (mobile_phone,age) values ('1234',61);		等待	因为T3加了next-key lock
T8	insert into user (mobile_phone,age) values ('1234',91);		等待	因为T3加了next-key lock
		commit;
				事务A等待结束，insert成功

该场景结论：

用当前读，非唯一索引范围查询，会加next-key lock，另外一个事务在该区间不能再insert值需等next-key lock释放

该场景下的确是next-key lock避免了幻读 

总结

 MySQL Innodb RR隔离级别下能够解决幻读的场景及原因：

1.快照读，由MVCC解决幻读

2.当前读，只有在利用非唯一索引进行范围查询的时候，才会使用next-key lock，避免该范围内的幻读