mysq在RR级别是如何解决不可重复读和幻读

1、不可重复读、幻读

1.1、sql

CREATE TABLE `t_isolation`  (
  `id` int(32) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `name` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '姓名',
  `age` int(32) NOT NULL COMMENT '年龄',
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `idx_name`(`name`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 6 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '隔离级别' ROW_FORMAT = Compact;

INSERT INTO `t_isolation` VALUES (1, 'tom', 40);
INSERT INTO `t_isolation` VALUES (2, 'bob', 30);

1.2、不可重复读

不可重复读 T1读取一行，T2然后修改或删除该行数据并且提交事务，T1再次读取结果不一样

-- 设置隔离级别 REDA_COMMITTED
set global transaction isolation level read committed;
show global variables like '%isolation%';
BEGIN;
UPDATE t_isolation SET age=40 WHERE `name`='tom';
COMMIT;
ROLLBACK;

BEGIN;
-- 第一次查询 结果1
SELECT * FROM t_isolation WHERE `name`='tom';
-- 第二次查询 结果2
SELECT * FROM t_isolation WHERE `name`='tom';
COMMIT;
ROLLBACK;

说明

sql1 执行查询 结果1
sql2 执行更新
sql1 再次执行查询 结果2
解决方案：设置隔离级别READ_REPEATABLE、SERIALIZABLE

1.3、幻读

幻读 T1按条件读取查询数据，T1按照同样的条件新增一条或多条数据并且提交事务，T1再次读取结果不一致

-- 设置隔离级别 REDA_COMMITTED
set global transaction isolation level read committed;
show global variables like '%isolation%';

BEGIN;
-- 第一次查询 结果1
SELECT * FROM t_isolation WHERE `name`='tom';
-- 第二次查询 结果2
SELECT * FROM t_isolation WHERE `name`='tom' FOR UPDATE;
COMMIT;
ROLLBACK;

BEGIN;
INSERT INTO `t_isolation`(`name`, `age`) VALUES ('tom', 40);
COMMIT;
ROLLBACK;

sql1 执行查询 结果一条
sql2 执行新增
sql1 再次执行查询 结果2条
解决方案：设置隔离级别SERIALIZABLE 后面会详解

2、undo log

简单来说undo log 就是回滚日志  它有两个作用：提供回滚和多个行版本控制(MVCC) undo log 日志会记录对应的事务 id，
还会记录当前事务将数据修改后的最新值，以及指向当前行数据上一个版本的 undo log 的指针，也就是 roll_pointer。

2.1 undo 实例

1、事务A新增一条数据 因为这个数据是第一条数据 所以没有上一个版本

2、接着事务 B(trx_id=30)，将这行数据的值修改为 dataB，同样也会记录一条 undo log，如下图所示

3、再接着，事务 C(trx_id=40)，将这行数据的值修改为 dataC，同样也会记录一条 undo log

3、readview

在事务开启的时候，会基于当前系统中数据库的数据，为每个事务生成一个快照，也叫做 ReadView
ReadView 会根据这 4 个属性，再结合 undo log 版本链，来实现 MVCC 机制，决定让一个事务能读取到哪些数据，不能读取到哪些数据。

3.1、readview的四个属性

1、creator_trx_id：当前事务id
2、m_ids：当前系统中所有未提交的事务id的集合
3、min_trx_id：m_ids中最小的事务id
4、max_trx_id：  m_ids中最大的事务id在加1，也就是下一个要生成的事务id

3.2、属性说明

1、版本的trx_id==READVIEW中的creator_trx_id，表示当前读事务正在读取被自己修改过的记录，
   该版本可以被当前事务访问；
2、版本trx_id < min_trx_id，表明生成该版本的事务在当前事务生成READVIEW前已经提交了，
   所以该版本可以被当前事务访问；
3、版本的trx_id > max_trx_id，表明生成该版本的事务在当前事务生成READVIEW后才开启的，
  该版本不可被当前事务访问；
4、版本的trx_id在READVIEW的min_trx_id和max_trx_id之间，需要分两种情况  
   1）row_trx_id 在 m_ids 数组中，那么当前事务不能读取到。为什么呢？
   row_trx_id 在 m_ids 数组中表示的是和当前事务在同一时刻开启的事务。（保证可重复读）
   2）row_trx_id 在不在m_ids 数组中，那么当前事务可以读到?【只会发生在读已提交】
      说明该事务在生成redview的时候已经被提交 所以当前事务可以读取该数据

3.3、readview实例

1、事务A和事务B并发执行操作数据

事务A的readview 
creator_trx_id：20、m_ids：[20,30]、min_trx_id： 20、max_trx_id： 31
事务B的readview 
creator_trx_id：30、m_ids：[20,30]、min_trx_id： 20、max_trx_id： 31

因为该行数据的trx_id=10,所以事务A和B都可以读取
2、接着事务A修改该行数据，并且提交事务 那么undolog的版本链如下图

3、然后事务A再次读取数据 事务A读取数据结果如下图

4、什么是间隙锁

间隙锁是根据索引结构产生的间隙区域，一般发生在普通索引和索引的范围查询，被间隙锁锁定的区域无法加入排他锁

4.1、sql

CREATE TABLE `lock_user` (
  `id` int(11) NOT NULL,
  `name` varchar(8) NOT NULL,
  `age` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `index_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `lock_user` (`id`, `name`,`age`) VALUES ('1', 'a','15');
INSERT INTO `lock_user` (`id`, `name`,`age`) VALUES ('3', 'c','20');
INSERT INTO `lock_user` (`id`, `name`,`age`) VALUES ('5', 'e','16');
INSERT INTO `lock_user` (`id`, `name`,`age`) VALUES ('7', 'g','19');
INSERT INTO `lock_user` (`id`, `name`,`age`) VALUES ('9', 'i','34');

4.2、间隙锁实例

BEGIN;
UPDATE lock_user SET age=17 WHERE name='e'
COMMIT;
ROLLBACK;

BEGIN;
-- 不阻塞
INSERT INTO `lock_user`(`name`, `age`) VALUES ('b', 15);
-- 阻塞
INSERT INTO `lock_user`(`name`, `age`) VALUES ('d', 15);
-- 不阻塞
INSERT INTO `lock_user`(`name`, `age`) VALUES ('h', 15);
-- 不阻塞
UPDATE lock_user SET age=16 WHERE name='c'

COMMIT;
ROLLBACK;

说明

 在执行更新语句时 会锁定 （c,e] [e,g) 区间范围 所以d会阻塞 但是c、b、h是不会影响

5、RR级别如何避免不可重复读

1、事务A和事务同事操作data数据

那么此时对于事务 A 而言
它的 ReadView 中，m_ids=[20,30]，min_trx_id=20，max_trx_id=31，creator_trx_id=20
对于事务 B 而言
它的 ReadView 中，m_ids=[20,30]，min_trx_id=20，max_trx_id=31，creator_trx_id=30

2、 B(trx_id=30)去修改数据

B(trx_id=30)去修改数据，将数据修改为 data_B，此时 MySQL 会写一条对应的 undo log，
数据就会新增一个版本，undo log 版本就变成了如下图所示的结构

3、 事务A(trx_id=20)开始查询数据

此时在 undo log 版本链中，数据最新版本的事务 id 为 30，根据 ReadView 机制，
发现 30 处于事务 A[trx_id=20]ReadView 中 min_trx_id 和 max_trx_id 之间，
因此还需要判断 30 是否处于 m_ids 数组内，结果发现 30 确实在 m_ids 数组中，
[并发事务开启]所以需要沿着 undo log 版本链往前找，
所以 trx_id=30 的版本事务 A 也不能读到，继续向前找，最终读取到 trx_id=10 的版本数据

6、RR级别如何避免幻读

6.1、快照度

在RR级别是可以的快照度是可以避免幻读，读取逻辑和可重复读一致，如果是当前读则无法避免

6.2、当前读

6.2.1、什么是当前读

select lock in share mode(共享锁),
select for update ; update, insert ,delete(排他锁)这些操作都是一种当前读

6.2.2、当前读如何避免幻读

当前读避免的幻读的方式是通过间隙锁，通过加锁的方式导致其他事务无法新增同条件的数据。从而避免的幻读

参考文章

https://www.modb.pro/db/43933
https://www.modb.pro/db/43963
https://blog.csdn.net/qq_42651904/article/details/110622818