MySQL解决幻读详解

简单来说就是通过mvcc + next-key locks 防止幻读

幻读是什么？

当前事务读取了一个范围的记录，另一个事务在该范围内插入了新记录，当前事务再次读取该范围内的记录就会发现新插入的记录，这就是幻读

以下MySQL的隔离界别都是可重复读（RR）

mvcc与next-key分别在什么情况下起作用？

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

快照读与当前读

1. 快照读：所有普通的select语句都算快照读，它并不会给表中任何记录做加锁操作，其他事务可以对表中记录做任何改动

2. 当前读：加锁的操作都叫当前读，分为s锁，x锁

   • 共享锁：S锁。在事务要读取一条记录时，需要先获取该记录的S锁

     select … lock in share mode

   • 独享锁（排他锁）：X锁。事务要改动一条记录时，需要先获取X锁

     select … for update、insert、update、delete

     S锁与S锁是兼容的；S锁与X锁是不兼容；X锁与X锁也是不兼容。

简单了解下跟防止幻读有关的行级锁

1. record locks：把当前记录上锁

2. gap locks：如果当前列具有唯一索引，那么就仅仅是把当前行加锁；只有当前列没有索引或者具有非唯一索引，才会锁定前面的间隙

什么意思呢？

如下例：

CREATE TABLE `user` (
 `id` int NOT NULL,
 `score` int DEFAULT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB;
insert into user values(1,79),(3,91),(6,59);

	事务1	事务2
1	begin; select * from user where score=91 for update;
2		begin; insert into user values(2,98); // 因为gap锁的原因，插入失败 commit;
3	commit；

也就是在 id (1, 3) 之间加x锁

而如果把事务1的查询语句改成

select * from user where id=3 for update;

则前面的间隙不会上锁，事务2会成功插入！

3. next-key locks：就是record locks跟gap locks的组合，既能保护该条记录，又能防止其他事务插入该记录前面的间隙中。

   如上述加x锁的区间就变成了 (1, 3]

简单了解下mvcc

1. 具有三个隐藏字段：

   • DB_TRX_ID：记录最后进行插入、更新操作的事务

   • DB_ROLL_PTR：滚动指针，指向修改前的记录

   • DB_ROW_ID：如果没有聚簇索引，该字段会构建聚簇索引（相当于隐藏的自增主键）

2. readview：会记录当前活跃事务的id范围，根据事务id来判断哪个版本是对当前事务可见的

   如下例（还是上面表，默认三条数据）：

	事务1	事务2
1	begin; update user set score=50 where id=1; update user set score=60 where id=1;
2		begin; select * from user where id=1; //score=79
3	commit;
4		select * from user where id=1; //score=79 commit;

​ 为什么？

​ 假设事务1的事务id是100

1. **事务1未提交：**事务2在执行select之前会生成一个readview，活跃的只有事务1，该readview的事务范围就是100，在该范围内都不符合要求。根据滚动指针（DB_ROLL_PTR）找之前的版本，直到事务id小于100，也就是找到事务1开启之前的版本，那时的score就是79

	DB_TRX_ID（事务id）	id	score	DB_ROLL_PTR（滚动指针）
1	100	1	60	2
2	100	1	50	3
3	80（肯定小于100）	1	79

2. 事务1提交：
   • 上述的例子是在MySQL默认隔离级别（RR）下，在该隔离级别下，只在第一次select前生成一个readview。在事务1未提交之前已经生成过了，所以搜索到的score还是79。
   • 如果隔离级别是RC，那么第二次select前会再次生成一个readview，那么score就是60

上面内容过一遍后，在回过头来考虑幻读问题，这不就已经解决了嘛！

• 快照读的情况下，通过mvcc来避免幻读
• 当前读的情况下，通过next-key来避免幻读