MVCC可重复读与幻读测试

MVCC原理参看本专栏的《MVCC多版本并发控制实现原理》即可。

先构建一张表，添加点数据，后续用于测试

DROP TABLE IF EXISTS `course`;
CREATE TABLE `course` (
  `course_no` varchar(50) NOT NULL,
  `course_name` varchar(255) DEFAULT NULL,
  `teacher_no` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`course_no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
​
-- ----------------------------
-- Records of course
-- ----------------------------
INSERT INTO `course` VALUES ('201', '历史', '001');
INSERT INTO `course` VALUES ('202', '数学', '002');
INSERT INTO `course` VALUES ('203', '政治', '003');
INSERT INTO `course` VALUES ('204', '化学', '004');
INSERT INTO `course` VALUES ('205', '物理', '005');
INSERT INTO `course` VALUES ('206', '生物', '006');
INSERT INTO `course` VALUES ('207', '英语', '007');
INSERT INTO `course` VALUES ('208', '语文', '008');
INSERT INTO `course` VALUES ('209', '地理', '009');
INSERT INTO `course` VALUES ('210', '音乐', '001');

MVCC解决了快照读部分场景的幻读，nextkey解决了当前读的幻读(其实就是靠串行化解决的)。

先说两个结论：

1. 在快照读的情况下，会通过mvcc来避免幻读

2. 在当前读的情况下，会通过next-key来避免幻读

情况一：分别按如下顺序开启两个事务，按顺序执行相关语句，查看结果

事务A
begin;
select * from course;
事务B
begin;
insert into course VALUES ('211', '美术', '001');
commit;
事务A
select * from course;  （第一处）
commit;
select * from course;   （第 二处 ）

上面的例子 结果非常容易推导出来，因为事务A开启后执行了查询，此时隔离级别为RR，可重复读，事务A只会生成一个读的视图。因此当事务B提交了新记录后，事务A 再去查询时，由于读视图生成时，事务B提交的记录 根据数据可见 算法，事务A并不能读取（事务版本大于等于事务A的读视图的最大事务+1）。当事务 A提交后，再去读，相当于开启了新的事务，此时再读，无疑是可以读取的。

情况二：与情况一相似，不过添加改成删除，实测第一处还是和一开始读到的一致。第二处读取得到最新结果，即少了一行。

测试如上图所示，数字代表执行的顺序。将添加改成删除，结果一样。

情况三：在事务A未提交前，执行了更新操作，再去读。注意，此时更新的行为本来就存在的行，并不是新添加的行。

事务A
begin;
select * from course;  （第一处）
事务B
begin;
insert into course VALUES ('211', '美术', '001');
commit;
事务A
select * from course;  （第二处）
update course set course_name = '美术1' where course_name = '音乐';
select * from course; （第三处）
commit;
select * from course; （第四处）

测试结果表明，在第三处，新添加行并没有被读取出来，依然读的是之前的数据，但原本course_name为“音乐”的这一行读出来已经变成了“美术1”。在第四处才真正把新添加的那行读出来。此时可以认为还没出现幻读。

情况四：如果把更新操作改成更新在事务B中新添加的行，结果就不一样，此时在第三处，能读到新添加的行，也就是出现了幻读。恢复数据到原样继续测试。

事务A
begin;
select * from course;  （第一处）
事务B
begin;
insert into course VALUES ('211', '美术', '001');
commit;
事务A
select * from course;  （第二处）
update course set course_name = '美术1' where course_name = '美术 ';  （注意此处更新的是事务B添加的行）
select * from course; （第三处）
commit;
select * from course; （第四处）

结果如下

可见，这些的结果和上面的不一样，此时出现了幻读。从测试结果来看，RR级别下 ，只有当事务执行了更新新添加的行，此时会出现幻读，被更新的行被读到了。

如果要严格避免以上的幻读情况，则需要把快照读退化为当前读，如select * from tb where id<xx for update。通过对特定或范围记录加锁以避免幻读。但这样会影响性能，应严格根据实际应用来考虑是否应该读取加锁。