MySQL是怎么解决幻读和不可重复读的？

事务

1、事务的特性

四大特性：

• 原子性

• 隔离性

• 一致性

• 持久性

2、事务的隔离性

2.1.什么是隔离性

每个事务都有一个完整的数据空间，对其他并发事务是隔离的。

说白了，隔离就是指这个事务能读到什么样的数据。而这个隔离程度又有以下几种：

• 读未提交：一个事务还没提交时，它做的变更就能被别的事务看到。

• 读提交：一个事务提交之后，它做的变更才会被其他事务看到。

• 可重复读：一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致的。

• 串行化：对于同一行记录，“写”会加“写锁”，“读”会加“读锁”。当出现读写锁冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行。

具体选择哪个，应该是看 能不能接受其他并发事务对数据进行改变的情况。

2.2.事务的隔离级别是可重复读的应用场景

当我们要进行备份时，我们需要开启一个隔离级别是可重复读的事务然后再进行备份。因为这样可以保证在备份时，读取到的数据与事务启动时所看到的数据是一致的。

比如说，刚备份完课程表，然后一个用户购买了课程，用户余额表发生改变，接着备份余额表。最后就会发现刚刚备份的余额表和课程表有出入，余额表多扣了钱，而课程表并没有相应购买的课程。

2.3.可重复读的实现

可重复读是通过MVCC实现的。MVCC：多版本并发控制。

• MVCC简单来说就是在事务启动时为整个库拍了个快照。

• 而这个快照是通过ReadView和undolog实现的

• 这个ReadView就是视图数组，InnoDB会为每个事务构造一个视图数组用来保存事务启动时的所有活跃事务的id

• 并根据活跃事务的id记录最低水位和最高水位。（最低水位就是活跃事务里最小的事务id，最高水位就是活跃事务里最大是事务id+1）

• 另外，数据表中的一行记录都是有多个版本的，每个版本都记录着一个事务id，表示是这个数据是被这个事务所更新。并且undolog会为每条数据记录回滚操作。

• 在可重复读的隔离级别下，当一个事务要去访问一条数据时，首先会判断这条记录版本的事务id处于视图数组的哪一部分。

• 总结就是，如果这个版本数据的事务还未提交，那么这个数据是不可见的。如果这个版本数据的事务已提交，需要判断这个事务是在视图创建之前还是之后提交的，如果是之后则不可见，如果是之前则可见。（其实这个规则就是可重复读的规则），具体的过程如下：

• 如果这个记录版本的事务id低于最低水位，表示这个版本是已提交的事务或者是当前事务自己生成的，那么这个数据记录是可见的。

• 如果这个记录版本的事务id处于最低水位和最高水位之间，那么就要判断是否在视图数组中

  • 如果这个记录版本的事务id在视图数组中，表示这个版本是由还没提交的事务生成的，是不可见的。

  • 如果这个记录版本的事务id不在视图数组中，表示这个版本是已经提交了的事务生成的，是可见的。

    这里解释为什么记录版本的事务id不在视图数组中，表示数据是被已提交的事务更新的。比如说视图数组为[2,3,5]，最低水位是2，最高水位是6。如果这个记录版本的事务id是4，不在视图数组里，即不在活跃事务里，原因可能是2，3是个长事务，4这个事务已经提交了，而2，3事务还处于活跃状态。

• 如果这个记录版本的事务id高于最高水位，表示这个版本是由将来启动的事务生成的，是不可见的。

• 如果数据可见，那么直接读取；如果数据不可见，那么就用undolog回滚操作得到之前的版本，然后再进行判断。

2.4.读提交的实现

读提交与可重复读的区别在于执行语句时使用的是什么时候生成的视图。

可重复读执行事务中的每条语句时，使用的都是事务启动时生成的视图；而读提交执行事务中的每条语句时，使用的都是这条语句执行前的视图。

以上MVCC实现可重复读/读提交中，使用的是快照读/一致性读。

而 update语句、select加锁语句 使用的是当前读。

2.5.当前读

当前读：就是要能读到所有已经提交的记录的最新值。

举个例子：一个表的数据如下

如果事务C变成下面这种情况时：

3、并发事务出现的问题

• 脏读：读到人家事务还未提交的数据

• 不可重复读：两次读同一个数据不一样。也就是读到人家事务已提交的数据，但与之前读到的不一样。

• 幻读：一个事务在前后两次查询同一个范围的时候，后一次查询看到了前一次查询没有看到的行。

对于事务隔离性的程度：

• 读提交能解决脏读。

• Mysql在默认隔离级别(可重复读)下，

  • select语句（快照读），使用MVCC解决了不可重复读、幻读问题。

  • select加锁语句（当前读），使用next-key lock（记录锁+间隙锁）解决了不可重复读、幻读问题。

4、当前读下产生的幻读和不可重复读问题

4.1.不可重复和幻读产生的问题

举例：

我们假设，select加锁，只锁住当前读到的这一行记录。

不可重复读就是前后两次查询，数据被修改过，导致两次查询查到的数据不一样。

幻读就是前后两次查询，查询到的记录条数不一样。且专指“新插入的行”。比如说第一次查询查询到2条记录，第二次却查询到3条记录。

上面的例子中就出现了不可重复读和幻读的问题。

幻读和不可重复读会导致什么问题吗？

1、语义上的问题

session A 里 Q1 语句声明了要锁住所有 d=5 的行的加锁，但session A 还只是给 id=5 这一行加了行锁。session B和session C执行完后都出现了d = 5 的其他行。于是就破坏了sessionA Q1的初衷。

2、数据不一致问题

这里的数据不一致指的是数据库里的数据和binlog里的数据不一致。

binlog里内容是这样的：

update t set d=5 where id=0; /*(0,0,5)*/
update t set c=5 where id=0; /*(0,5,5)*/
​
insert into t values(1,1,5); /*(1,1,5)*/
update t set c=5 where id=1; /*(1,5,5)*/
​
update t set d=100 where d=5;/*所有d=5的行，d改成100*/

也就是说如果拿binlog去备份从库的话，从库的数据变成这样：(0,5,100)、(1,5,100) 和 (5,5,100)。

综上，如果 select加锁 只锁一行是有问题的。

因此，select加锁 应该锁住 在数据库表中 扫描到的所有行记录 和 间隙 。

当sessionA中执行select * from t where d=5 for update时，因为字段d没有索引，进行全表扫描，因此会锁住表中的6条记录和这6条记录中的7个间隙。

这样，就可以避免上面出现的不可重复读和幻读问题。事实上，执行select加锁语句，InnoDB也确实是这样帮我们做的。因此就有了最开始的结论：select加锁是当前读，当前读是通过行锁+间隙锁解决不可重复读和幻读问题。除了select加锁是当前读以外，update、delete语句也是当前读，也同样会加锁。

4.2.next-key lock

next-key lock：间隙锁和行锁的合称。前开后闭区间。

4.2.1.行锁

行锁，分成读锁和写锁。

有上图可知，行锁和行锁之间可能是有冲突的。

4.2.2.间隙锁(Gap Lock)

间隙锁：锁的就是两个值之间的空隙。

间隙锁与间隙锁之间不存在冲突，跟间隙锁存在冲突关系的，是“往这个间隙中插入一个记录”这个操作。

间隙锁产生的死锁问题：

间隙锁的引入，可能会导致同样的语句锁住更大的范围，这其实是影响了并发度的。

4.2.2.当前读加锁规则

判断当前读是怎样加锁的，其实就是去看怎么加锁能防止幻读的产生。

但我总结了以下规律：

查询不管是在主键索引查还是在二级索引查，不管是等值查询还是范围查询，

我们要做的就是：

1. 在sql选择的索引上进行查找

   • 唯一索引上如果找到满足条件的，会停下

   • 普通索引如果找到满足条件的，会继续往下查找，直到不满足条件为止

   • 不论是等值查找还是范围查找，每查找到一种结果就走下面的步骤进行加锁。

1. 查找结果分为两种：

   • 找到满足条件的

   • 找到不满足条件的

2. 对于以上两种查找结果，我们处理步骤如下：

   • 直接加next-key lock，左开右闭。（ 满足条件的上一条记录索引，满足条件的记录索引 ]

   • 如果是唯一索引上找到等值满足条件的，退化为行锁。

   • 对于不满足条件的，退化为间隙锁。

举例：

现在有一个表，分别有三个字段：id（主键索引/唯一索引）、age（普通索引）、name

主键索引/唯一索引数据如下：

普通索引数据如下：

唯一索引等值查询

select * from user where id = 1 for update; 

select加锁是当前读，现在我们要判断是怎么加锁的。

按照我们上面的规则分析，

• 首先我们将眼光聚焦到id主键索引上。

• 在主键索引上我们查询到了id=1的记录，也就是找到了满足条件的。

• 为（-∞，1] 加上 next-key lock

• 因为是在唯一索引上找到了等值的满足条件的，因此退化为行锁。

• 只为 id = 1 这条记录所在的主键索引上加行锁。

于是我们就分析出这个sql语句最终只为id = 1的记录加行锁。其实我们也可以从是否阻止了幻读和可重复读的角度去想这样加锁的意义。如果我们只加了id = 1的行锁，如果其他事务想要update、delete都会被阻塞，相应insert则会因为主键重复的问题而报错。因此这样加锁是完全可以达到防止幻读和可重复读的问题的。

这里补充一点，我们可以通过select * from performance_schema.data_locks\G;这个命令去查询加锁情况。

• 如果 LOCK_MODE 为 X，说明是 next-key 锁；

• 如果 LOCK_MODE 为 X, REC_NOT_GAP，说明是记录锁；

• 如果 LOCK_MODE 为 X, GAP，说明是间隙锁；

select * from user where id = 2 for update;

• 首先我们将眼光聚焦到id主键索引上。

• 在主键索引上我们查询到了id=1的记录，不满足条件。

• 继续往下查询，id = 5，不满足条件，并且能断定找不到满足条件的了。

• 为（1，5] 加上 next-key lock。

• 但对于不满足条件的，应该退化为间隙锁。

• 为（1，5）加上间隙锁。

唯一索引范围查询

select * from user where id > 15 for update;

• 首先我们将眼光聚焦到id主键索引上。

• 在主键索引上我们查询 id>15 的记录，查到id = 15的记录时，不满足，继续查，id = 20 的记录 满足。

• 为（15，20] 加上 next-key lock。

• 继续往下查，查询到特殊记录表示最后一条记录。

• 为（20，+∞] 加上 next-key lock。

• 结束。

因此，最终会为（15，20] 、（20，+∞] 加上next-key lock。

select * from user where id >= 15 for update;

• 首先我们将眼光聚焦到id主键索引上。

• 在主键索引上我们查询 id>=15 的记录，查询到 id = 15 的记录时，满足条件

• 为（10，15] 加上next-key lock。

• 但因为是在唯一索引上找到了等值的满足条件的，因此退化为行锁。

• 为 id = 15 这一条记录加上行锁。

• 继续往下查找，20满足条件

• 为（15，20] 加上next-key lock。

• 继续往下查找，查找到特殊记录表示最后一条记录。

• 为（20，+∞] 加上 next-key lock。

• 结束。

因此，最终会为id = 15 的主键索引上加行锁，为（15，20] 、（20，+∞] 添加next-key lock。

select * from user where id < 6 for update;

• 首先我们将眼光聚焦到id主键索引上。

• 在主键索引上我们查询 id < 6 的记录，查询到 id = 1 的记录时，满足条件

• 为（-∞，1] 加上 next-key lock。

• 继续往下查找，id = 5，满足条件

• 为（1，5] 加上 next-key lock。

• 继续往下查找，id = 10，找到了不满足条件的，并且能断定找不到满足条件的了。

• 为（5，10] 加上 next-key lock。

• 但对于不满足条件的，应该退化为间隙锁。

• 为（5，10）加上 next-key lock。

• 结束。

因此，最终会为（-∞，1]、（1，5] 添加next-key lock，为（5，10） 添加间隙锁。

select * from user where id <= 5 for update;

• 首先我们将眼光聚焦到id主键索引上。

• 在主键索引上我们查询 id <= 5 的记录，查询到 id = 1 的记录时，满足条件

• 为（-∞，1] 加上 next-key lock。

• 继续往下查找，id = 5，满足条件

• 为 (1, 5 ] 加上 next-key lock。

• 因为已经找到id = 5的记录了，且是在唯一索引上，所以不用继续查询，结束。

因此，最终会为（-∞，1]、（1，5] 添加next-key lock。

select * from user where id < 5 for update;

• 首先我们将眼光聚焦到id主键索引上。

• 在主键索引上我们查询 id <= 5 的记录，查询到 id = 1 的记录时，满足条件

• 为（-∞，1] 加上 next-key lock。

• 继续往下查找，id = 5，不满足条件，并且能断定找不到满足条件的了。

• 为 (1, 5 ] 加上 next-key lock。

• 但对于不满足条件的，应该退化为间隙锁。

• 为（1，5）加上间隙锁。

因此，最终会为（-∞，1]添加next-key lock，为（1，5）加上间隙锁。

非唯一索引等值查询

select * from user where age = 25 for update;

• 首先我们将眼光聚焦到age普通索引上。

• 查询到 age = 39 时不满足条件，并且能断定找不到满足条件的了。

• 为（22，39] 加上 next-key lock。

• 但对于不满足条件的，应该退化为间隙锁。

• 为（22，39）加上间隙锁。

因此，最终会为（22，39）添加间隙锁。

select * from user where age = 22 for update;

• 首先我们将眼光聚焦到age普通索引上。

• 查询到 age = 22 时满足条件。

• 为（21，22] 加上 next-key lock。

• 因为是普通索引，即使查询到满足条件的，还需要继续往下查询。

• 查询到 age = 39，不满足条件，并且能断定找不到满足条件的了。

• 为为（22，39] 加上 next-key lock。

• 但对于不满足条件的，应该退化为间隙锁。

• 为（22，39）加上间隙锁。

因此，最终会为（21，22] 添加next-key lock，为（22，39）添加间隙锁。

非唯一索引范围查询

select * from user where age >= 22  for update;

• 首先我们将眼光聚焦到age普通索引上。

• 查询到 age = 22 时满足条件。

• 为（21，22] 加上 next-key lock。

• 查询到 age = 39，满足条件。

• 为（22，39] 加上 next-key lock。

• 查询到特殊记录表示最后一条记录。

• 为（39，+∞] 加上 next-key lock。

因此，最终会为（21，22] 、（22，39] 、（39，+∞] 添加next-key lock。

补充：lock in share mode 只锁覆盖索引，如果是 for update 不仅锁覆盖索引，还会为主键索引上满足条件的行加上行锁。

上面的当前读的sql都是select加锁，我们现在举个当前读的sql是delete的。只要是当前读，就会用上面的规则进行加锁。

现在有一张表t，字段c是普通索引。普通索引数据如下：

delete from t where c = 10;

• 首先我们将眼光聚焦到c普通索引上。

• 查询到 c= 10 时满足条件。

• 为（5，10] 加上 next-key lock。

• 因为是普通索引，所以需要继续往下查找。查询到下一条记录 c = 10 满足条件。

• 为（c=10 id = 10，c = 10 id = 30] 加上 next-key lock。

• 继续往下查找，查询到c = 15 不满足条件，并且能断定找不到满足条件的了。

• 为（10，15] 加上 next-key lock。

• 但对于不满足条件的，应该退化为间隙锁。

• 为（10，15）加上间隙锁。

因此，锁的范围如下：

以上的加锁规则都是针对查询条件的字段有索引的情况，毕竟查询的时候走的是索引，自然而然把锁加在索引上。但是我们现在来考虑一下这种情况，就是查询条件的字段没有索引，那么查询的时候肯定进行全表查询，每一个记录都查询过去，就会为每个记录加next-key lock。相当于全表都被锁住了，其他事务完全无法访问。

总之一句，对于当前读，select加锁、update、delete都是属于当前读，mysql会为当前读加next-key lock，从而解决幻读和不可重复读问题。至于加锁规则就是上面总结的。

参考

MySQL 是怎么加锁的？ | 小林coding

《MySQL实战45讲》

原文地址：https://blog.csdn.net/m0_73866527/article/details/143868850

免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！