MYSQL RR级别是否能解决幻读问题

---

前

最近研究了下mysql在rr级别是否能彻底解决幻读问题。我本地的mysql版本为8.0.12

一、幻读是什么？

幻读主要是指，
1.同一个事务中，同一条sql第一次查询到的数据，和第二次查询不一致，多了数据或者少了数据，称为幻读，或者还有另外一种版本的说法，
2. 事务A第一次查询出来的数据中，例如是没有id为1的数据，这时候事务b插入了一条id为1的数据，事务b提交事务，如果这时候事务A，插入id为1的数据，这时候就会报错主键冲突，导致数据插入不了。

二、建立表和插入数据测试

1.建表

DROP TABLE IF EXISTS `a`;
CREATE TABLE `a` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `name` varchar(255) DEFAULT NULL,
  `sex` varchar(255) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  UNIQUE KEY `id` (`id`),
  KEY `index3` (`age`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of a
-- ----------------------------
INSERT INTO `a` VALUES ('1', '小明', '男', '1');
INSERT INTO `a` VALUES ('2', '小红', '男', '2');
INSERT INTO `a` VALUES ('3', '小西', '女', '4');

2.测试幻读

1.快照读场景 【即没有数据更新的场景】

1.先开启事务A，查询表中所有数据，

START TRANSACTION;
select *from a;

2.然后开启事务B，插入一条数据并且提交

start TRANSACTION;
insert into a values(5,'小红','女',3);
commit;

插入成功

3. 在事务A中再次查询表中所有数据
   
   可以看到并没有事务B插入的数据，可以得出结论，在快照读的情况，RR级别是可以避免幻读情况，这是由于mysql使用了mvcc，即给数据增加两个版本号，一个创建版本，一个删除版本，然后使用版本号来控制读取到的数据，保证了一个事务中读取到的数据始终是一致的。

2.当前读场景

1.事务A中开启一个事务查询，

START TRANSACTION;
select *from a where age>1 for update;

2.事务B中插入一条数据

start TRANSACTION;
insert into a values(5,'小红','女',3);
commit;

可以看到sql一直在执行，并不能继续下去，我们查询下表的状态，

可以看到表a已经被锁住了，无法插入事务b的数据，
3.在事务A中再次查询数据，也还是原来的数据，这就不用测试了，因为B事务都没提交成功，肯定是和原来一样的。
4.原因：在当前读的情况有两大因素，
1.当前读的时候，即加上for update，数据库会把当前这行数据即age=1 这条数据，加上行锁，并且加上间隙锁，组合在一起叫 next-key lock，
2. 条件age 要有索引，这时候才能根据索引数据去生成间隙锁，我的条件是age>1,可以看到数据中age>1的数据有两条，索引的区间，间隙锁的锁住的区间应该是(1,+∞],一个左开右闭的区间，这时候我再插入age=3,的数据是在(1,+∞]这个区间内的，因此被锁住了，无法插入。

3.当前读 ，插入区间外的数据，插入区间外的数据，可以证明间隙锁的存在，如果能插入成功，则证明是锁住的是区间。

1.锁住age<2数据

2.插入age为3的数据，可以发现，是可以正常插入的，因此可以证明锁住的是间隙

4. 当前读，age字段不加索引的情况

1.在 当前读的情景下，再次测试了，插入age=3，的情况

这时候也是锁住了，无法插入数据，
2.这时候改变条件，事务A age<2,如果插入age为3的数据，是不在间隙锁的区间内的，测试是否能插入

3. 事务B，插入age为3的记录，

4. 原因：由此可以推测出，如果没有索引，for update会把整个表都锁住。

总结

1.在快照读，mysql能避免幻读
2.在当前读的时候，数据库通过next-key lock也能解决幻读
3.第三个测试证明了 间隙锁的存在。
4.第四个测试则表明间隙锁是依赖索引进行，如果没有索引则会把整个表都锁住，这个隔离级别就是串行化了，不建议使用。
5.虽然上面的测试可以得出结论是解决了幻读，但是其实没有真正的串行化，比如快照读的时候，如果A事务更新了所有数据，这时候是会更新到事务B插入的数据，并不是只更新事务A读取到的数据，其实还是存在缺陷的。要想完全避免数据的更新错误，只能是使用串行化的隔离级别。
6.如果有不对的地方欢迎指出。
7.至于幻读的第二个版本的情况，加上for update 使事务加上锁，也可以解决，这样事务B就无法插入id为1的数据了。