解读mysql的可重复读、幻读及实现原理

最新推荐文章于 2024-08-04 11:37:05 发布

一颗向上的草莓

最新推荐文章于 2024-08-04 11:37:05 发布

阅读量514

点赞数

分类专栏： java面试数据库—Mysql 文章标签： mysql 怎么避免幻读

本文链接：https://blog.csdn.net/u014532775/article/details/106896024

版权

java面试同时被 2 个专栏收录

77 篇文章 3 订阅

订阅专栏

数据库—Mysql

23 篇文章 1 订阅

订阅专栏

事务隔离级别有四种，mysql默认使用的是可重复读，mysql是怎么实现可重复读的？为什么会出现幻读？是否解决了幻读的问题？

一、事务的隔离级别

Read Uncommitted（未提交读）
在该隔离级别，所有事务都可以看到其他未提交事务的执行结果。读取未提交的数据，也被称之为脏读（Dirty Read）。该级别用的很少。

Read Committed（提交读）
这是大多数数据库系统的默认隔离级别（但不是MySQL默认的）。它满足了隔离的简单定义：一个事务只能看见已经提交事务所做的改变，换句话说就是事务提交之前对其余事务不可见。这种隔离级别也支持不可重复读（Nonrepeatable Read），因为同一事务的其他实例在该实例处理其间可能会有新的commit，所以同一select查询可能返回不同结果。

Repeatable Read（可重复读）
这是MySQL的默认事务隔离级别，它确保同一事务的多个实例在并发读取数据时，会看到同样的数据行。不过理论上，这会导致另一个棘手的问题：幻读（Phantom Read）。简单的说，幻读指当用户读取某一范围的数据行时，另一个事务又在该范围内插入了新行，当用户再读取该范围的数据行时，会发现有新的“幻影” 行。InnoDB和Falcon存储引擎通过多版本并发控制（MVCC，Multiversion Concurrency Control）机制解决了该问题（mysql彻底解决了幻读问题？请往下看）。

Serializable（可串行化）
这是最高的隔离级别，它强制事务都是串行执行的，使之不可能相互冲突，从而解决幻读问题。换言之，它是在每个读的数据行上加上共享锁。在这个级别，可能导致大量的超时现象和锁竞争。

事务隔离级别   脏读   不可重复读   幻读
读未提交（read-uncommitted）   是   是   是
不可重复读（read-committed）   否   是   是
可重复读（repeatable-read）   否   否   是
串行化（serializable）   否   否   否

在MySQL的众多存储引擎中，只有InnoDB支持事务，所有这里说的事务隔离级别指的是InnoDB下的事务隔离级别。

二、mysql怎么实现的可重复读

MVCC多版本并发控制(Multi-Version Concurrency Control)是MySQL中基于乐观锁理论实现隔离级别的方式，用于实现读已提交和可重复读取隔离级别。

在《高性能MySQL》中对MVCC的解释如下

举例说明MVCC的实现
新建一张表test_zq如下

id test_id DB_TRX_ID DB_ROLL_PT
MVCC逻辑流程-插入
在插入数据的时候，假设系统的全局事务ID从1开始，以下SQL语句执行分析参考注释信息：

begin;-- 获取到全局事务ID
insert into `test_zq` (`id`, `test_id`) values('5','68');
insert into `test_zq` (`id`, `test_id`) values('6','78');
commit;-- 提交事务

当执行完以上SQL语句之后，表格中的内容会变成：

id   test_id   DB_TRX_ID   DB_ROLL_PT
5   68   1   NULL
6   78   1   NULL
可以看到，插入的过程中会把全局事务ID记录到列 DB_TRX_ID 中去

MVCC逻辑流程-删除
对上述表格做删除逻辑，执行以下SQL语句（假设获取到的事务逻辑ID为 3）

begin；--获得全局事务ID = 3
delete test_zq where id = 6;
commit;

执行完上述SQL之后数据并没有被真正删除，而是对删除版本号做改变，如下所示：

id   test_id   DB_TRX_ID   DB_ROLL_PT
5   68   1   NULL
6   78   1   3
MVCC逻辑流程-修改
修改逻辑和删除逻辑有点相似，修改数据的时候会先复制一条当前记录行数据，同事标记这条数据的数据行版本号为当前是事务版本号，最后把原来的数据行的删除版本号标记为当前是事务。

执行以下SQL语句：

begin;-- 获取全局系统事务ID 假设为 10
update test_zq set test_id = 22 where id = 5;
commit;

执行后表格实际数据应该是：

id   test_id   DB_TRX_ID   DB_ROLL_PT
5   68   1   10
6   78   1   3
5   22   10   NULL
MVCC逻辑流程-查询
此时，数据查询规则如下：

查找数据行版本号早于当前事务版本号的数据行记录

也就是说，数据行的版本号要小于或等于当前是事务的系统版本号，这样也就确保了读取到的数据是当前事务开始前已经存在的数据，或者是自身事务改变过的数据

查找删除版本号要么为NULL，要么大于当前事务版本号的记录

这样确保查询出来的数据行记录在事务开启之前没有被删除

根据上述规则，我们继续以上张表格为例，对此做查询操作

begin;-- 假设拿到的系统事务ID为 12
select * from test_zq;
commit;

执行结果应该是：

id test_id DB_TRX_ID DB_ROLL_PT
6 22 10 NULL
这样，同一个事务中，就实现了可重复读。

三、幻读

什么是幻读，如下：

InnoDB实现的RR通过mvcc机制避免了这种幻读现象。

另一种幻读：

姑且把左边的事务命名为事务A，右边的命名为事务B。
事务B执行后，在事务A中查询没有查到B添加的数据行，这就是可重复读。
但是，在事务A执行了update后，再查询时就查到了事务A中添加的数据，这就是幻读。
这种结果告诉我们其实在MySQL可重复读的隔离级别中并不是完全解决了幻读的问题，而是解决了读数据情况下的幻读问题。而对于修改的操作依旧存在幻读问题，就是说MVCC对于幻读的解决是不彻底的。

快照读和当前读
出现了上面的情况我们需要知道为什么会出现这种情况。在查阅了一些资料后发现在RR级别中，通过MVCC机制，虽然让数据变得可重复读，但我们读到的数据可能是历史数据，不是数据库最新的数据。这种读取历史数据的方式，我们叫它快照读 (snapshot read)，而读取数据库最新版本数据的方式，叫当前读 (current read)。

select 快照读

当执行select操作是innodb默认会执行快照读，会记录下这次select后的结果，之后select 的时候就会返回这次快照的数据，即使其他事务提交了不会影响当前select的数据，这就实现了可重复读了。快照的生成当在第一次执行select的时候，也就是说假设当A开启了事务，然后没有执行任何操作，这时候B insert了一条数据然后commit,这时候A执行 select，那么返回的数据中就会有B添加的那条数据。之后无论再有其他事务commit都没有关系，因为快照已经生成了，后面的select都是根据快照来的。

当前读

对于会对数据修改的操作(update、insert、delete)都是采用当前读的模式。在执行这几个操作时会读取最新的版本号记录，写操作后把版本号改为了当前事务的版本号，所以即使是别的事务提交的数据也可以查询到。假设要update一条记录，但是在另一个事务中已经delete掉这条数据并且commit了，如果update就会产生冲突，所以在update的时候需要知道最新的数据。也正是因为这样所以才导致幻读。

在快照读读情况下，mysql通过mvcc来避免幻读。
在当前读读情况下，mysql通过next-key来避免幻读

四、如何解决幻读

很明显可重复读的隔离级别没有办法彻底的解决幻读的问题，如果我们的项目中需要解决幻读的话也有两个办法：

使用串行化读的隔离级别
MVCC+next-key locks：next-key locks由record locks(索引加锁/行锁) 和 gap locks(间隙锁，每次锁住的不光是需要使用的数据，还会锁住这些数据附近的数据)的结合，next-key lock 会锁定范围和自身行，比如select...where id<6，锁定的是小于6的行和等于6的行
Next-Key Lock即在事务中select时使用如下方法加锁，这样在另一个事务对范围内的数据进行修改时就会阻塞：

select * from table where id<6 lock in share mode;--共享锁
select * from table where id<6 for update;--排他锁
实际上很多的项目中是不会使用到上面的两种方法的，串行化读的性能太差，而且其实幻读很多时候是我们完全可以接受的。

五、带来的问题

next-key lock会导致死锁。

这个例子是我在网上看到的，我分析了很久才弄明白锁产生的具体过程。

数据库的事务隔离级别是RR。

建测试表：
CREATE TABLE `LockTest` (
   `order_id` varchar(20) NOT NULL,
   `id` bigint(20) NOT NULL AUTO_INCREMENT,
   PRIMARY KEY (`id`),
   KEY `idx_order_id` (`order_id`)
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8;

测试步骤：

事务1	事务2
begin delete from LockTest where order_id = 'D20'
	begin delete from LockTest where order_id = 'D19'
insert into LockTest (order_id) values ('D20')
	insert into LockTest (order_id) values ('D19')
commit	commit

测试结果：
事务1 执行到insert语句会block住，事务2执行insert语句会提示死锁错误。

原因分析：
1、首先看测试表的建表语句，id是主键索引，同时该主键是自增主键。order_id是普通索引。
2、事务1执行delete from LockTest where order_id = 'D20';语句时，由于数据库的隔离级别是RR，因此此时事务1在主键id上获得了一个next-key lock，这个锁的范围是[16, +∞)。
这个16就来自于AUTO_INCREMENT=16，因为LockTest目前是张空表。
3、同理，事务2执行delete from LockTest where order_id = 'D19';语句时，由于数据库的隔离级别是RR，事务2在主键id上也获得了一个next-key lock，这个锁的范围是[16, +∞)。
也就是说此时，事务1和事务2获得的锁是一样的。
4、事务1继续执行insert into LockTest (order_id) values ('D20');语句，这个时候由于该语句企图往LockTest表insert一行id=16，order_id=D20的数据，
但是由于在事务2的delete语句中，主键id上已经有了一个范围为[16, +∞)的锁，导致事务1此时想插入数据插不进去，被阻塞了。
5、继续事务2的插入语句insert into LockTest (order_id) values ('D19'); 该插入语句同样也想往LockTest表insert一行id=16，order_id=D19的数据，
但是由于由于在事务1的delete语句中，主键id上已经有了一个范围为[16, +∞)的锁，导致事务2此时想插入数据插不进去，被阻塞了。
此时，可以发现，事务1和事务2的锁是互相持有，互相等待的。所以innodb判断该事务遇到了死锁，直接将事务2进行了回滚。然后回头去看事务1，insert into LockTest (order_id) values ('D20')；被成功执行。

如果你将数据库的事务隔离级别修改为RC，上述事务会各自成功运行，不会互相影响。因为间隙锁是在可重复读隔离级别下才会生效，所以把隔离级别设置为读提交的话，就没有间隙锁了。但同时，你要解决可能出现的数据和日志不一致问题，需要把 binlog 格式设置为 row。这，也是现在不少公司使用的配置组合。

参考文档：https://blog.csdn.net/sanyuesan0000/article/details/90235335