咦～MySQL怎么出现幻读啦！

见贤思齐

咦～ MySQL怎么出现幻读啦！ 孔德志 转转技术 转转技术 转转研发中心及业界小伙伴们的技术学习交流平台，定期分享一线的实战经验及业界前沿的技术话题。 各种干货实践，欢迎交流分享，如有问题可随时联系 waterystone ~ 149篇内容 2024年08月30日 18:40 北京 1 背景2 验证2.1 验证准备2.2 当前读场景验证2.3.快照读场景验证3 分析4 总结1 背景 InnoDB默认的事务隔离级别是REPEATABLE-READ，它为了解决该隔离级别并发情况下的幻读问题，使用了LBCC（基于锁的并发控制）和MVCC（多版本的并发控制）两种方案。其中LBCC解决的是当前读情况下的幻读问题，MVCC解决的是快照读情况下的幻读问题，那既然如此，该隔离级别下是否仍然还存在幻读的问题呢？幻读问题到底有没有完全解决呢？基于这样的疑问，下面我们来进行验证下吧。2 验证 2.1 验证准备2.1.1 环境信息MySQL版本：5.6.36存储引擎：InnoDB隔离级别：REPEATABLE-READ2.1.2 数据准备为了进行验证，在测试库建立了一张测试使用的用户信息表，并且插入了3条初始数据。CREATETABLE`user_info`(`id`BIGINT(20)UNSIGNEDNOTNULLAUTO_INCREMENTCOMMENT'主键id',`name`VARCHAR(32)NOTNULLDEFAULT''COMMENT'姓名',`gender`VARCHAR(32)NOTNULLDEFAULT''COMMENT'性别',`email`VARCHAR(32)NOTNULLDEFAULT''COMMENT'邮箱',PRIMARYKEY(`id`))ENGINE=INNODBDEFAULTCHARSET=utf8mb4COMMENT='用户信息表';INSERTINTO`user_info`(`id`,`name`,`gender`,`email`)VALUES(1,'Curry','男','curry@163.com');INSERTINTO`user_info`(`id`,`name`,`gender`,`email`)VALUES(2,'Wade','男','wade@163.com');INSERTINTO`user_info`(`id`,`name`,`gender`,`email`)VALUES(3,'James','男','james@163.com');commit;2.2 当前读场景验证首先我们先来看看当前读的场景下会不会出现幻读的问题。2.2.1 前言什么是当前读当前读（Locking Read）也称为锁定读，读取的是数据当前的最新版本，而且读取到这个数据之后会对这个数据加锁，防止别的事务进行更改，即通过next-key锁（唯一索引next-key锁会退化为记录锁）来解决当前读中的脏读，幻读，不可重复读问题，也就是LBCC的方式。在进行写操作的时候也需要进行“当前读”，读取数据记录的最新版本。当前读包含以下SQL类型：select ... lock in share mode 、select ... for update、update 、delete 、insert。什么是临键锁我们将数据库中存储的每一行数据称为记录。如上图中1、5、9、12分别代表id为当前数的记录。对于键值在条件范围内但不存在的记录，叫做间隙（GAP）。则上图中的（-∞，1）、（1，5）...（12，+∞）为数据库中存在的间隙。而（-∞，1]、（1，5]...（12，+∞）我们称之为临键，即左开右闭的集合。当我们对上面的记录和间隙共同加锁时，添加的便是临键锁。2.2.2 场景验证触发当前读的方式有很多种，这里仅使用select lock in share mode这种方式来进行当前读幻读问题验证。场景：开启事务1在事务1中通过select lock in share mode进行当前读查询用户信息开启事务2在事务2中插入一条新数据提交事务2在事务1中再次查询用户信息提交事务1mysql>STARTTRANSACTION;--1.开启事务1QueryOK,0rowsaffected(0.00sec)mysql>select*fromuser_infolockinsharemode;--2.读锁方式查询用户信息+----+-------+--------+---------------+|id|name|gender|email|+----+-------+--------+---------------+|1|Curry|男|curry@163.com||2|Wade|男|wade@163.com||3|James|男|james@163.com|+----+-------+--------+---------------+3rowsinset(0.00sec)mysql>STARTTRANSACTION;--3.开启事务2QueryOK,0rowsaffected(0.00sec)mysql>INSERTINTO`user_info`(`id`,`name`,`gender`,`email`)VALUES(4,'White','男','white@163.com');--4.在事务2中插入一条新数据ERROR1205(HY000)ockwaittimeoutexceeded;tryrestartingtransaction--因锁等待插入未成功，最终等待超时，事务回滚终止场景验证结果可以看到在事务1中开始事务执行了当前读后，事务2在进行插入新数据时进入了锁等待，最后发生了锁等待超时，导致事务终止回滚。插入数据因锁的原因是不会成功的，因此事务1第二次查询时也不会查询到新记录，所以此场景下不会产生幻读的问题。2.2.3 小结由场景验证结果可以看到，由于临键锁的存在，会阻塞其他事务对加锁间隙的数据插入，所以当前读场景下通过LBCC是可以完全解决幻读的问题。2.3.快照读场景验证那接下来我们再看看快照读场景下是怎么样的。2.3.1 前言什么是快照读由于当前读是通过LBCC基于锁的方式来进行并发控制，是悲观锁的实现，同时也会因为锁的原因，造成锁冲突的概率变大，也会导致性能的下降，因此基于提高并发性能的考虑，引入了快照读，快照读顾名思义即读取的是数据的快照版本，快照读的实现是基于MVCC多版本并发控制，它在很多情况下，避免了加锁操作，降低了性能开销。2.3.2 场景验证场景一开启事务1在事务1中查询用户信息开启事务2在事务2中插入一条新数据提交事务2在事务1中再次查询用户信息提交事务1mysql>STARTTRANSACTION;--1.开启事务1QueryOK,0rowsaffected(0.00sec)mysql>select*fromuser_info;--2.在事务1中查询用户信息+----+-------+--------+---------------+|id|name|gender|email|+----+-------+--------+---------------+|1|Curry|男|curry@163.com||2|Wade|男|wade@163.com||3|James|男|james@163.com|+----+-------+--------+---------------+3rowsinset(0.00sec)mysql>select*fromuser_info;6.在事务1中再次查询用户信息+----+-------+--------+---------------+|id|name|gender|email|+----+-------+--------+---------------+|1|Curry|男|curry@163.com||2|Wade|男|wade@163.com||3|James|男|james@163.com|+----+-------+--------+---------------+3rowsinset(0.00sec)mysql>commit;--7.提交事务1QueryOK,0rowsaffected(0.00sec)mysql>STARTTRANSACTION;--3.开启事务2QueryOK,0rowsaffected(0.00sec)mysql>INSERTINTO`user_info`(`id`,`name`,`gender`,`email`)VALUES(4,'White','男','white@163.com');--4.在事务2中插入一条新数据QueryOK,1rowaffected(0.00sec)mysql>commit;--5.提交事务2QueryOK,0rowsaffected(0.00sec)mysql>select*fromuser_info;+----+-------+--------+---------------+|id|name|gender|email|+----+-------+--------+---------------+|1|Curry|男|curry@163.com||2|Wade|男|wade@163.com||3|James|男|james@163.com||4|White|男|white@163.com|+----+-------+--------+---------------+4rowsinset(0.00sec)场景验证结果从场景一来看RR级别下是可以避免幻读的问题，在意料之中。那如果我们在事务1中两次查询之间进行了当前读更新操作呢，那会不会出现幻读的问题呢，那接下来我们来看一看场景二。场景二开启事务1在事务1中查询用户信息开启事务2在事务2中插入一条新数据提交事务2在事务1中将ID为1的数据的用户姓名修改为Iversen在事务1中再次查询用户信息提交事务1mysql>STARTTRANSACTION;--1.开启事务1QueryOK,0rowsaffected(0.00sec)mysql>select*fromuser_info;--2.在事务1中查询用户信息+----+-------+--------+---------------+|id|name|gender|email|+----+-------+--------+---------------+|1|Curry|男|curry@163.com||2|Wade|男|wade@163.com||3|James|男|james@163.com|+----+-------+--------+---------------+3rowsinset(0.00sec)mysql>updateuser_infosetname='Iversen'whereid=1;--在事务1中将ID为1的数据的用户姓名修改为IversenQueryOK,1rowaffected(0.00sec)Rowsmatched:1Changed:1Warnings:0mysql>select*fromuser_info;--7.在事务1中再次查询用户信息+----+---------+--------+---------------+|id|name|gender|email|+----+---------+--------+---------------+|1|Iversen|男|curry@163.com||2|Wade|男|wade@163.com||3|James|男|james@163.com|+----+---------+--------+---------------+3rowsinset(0.00sec)mysql>commit;--8.提交事务1QueryOK,0rowsaffected(0.00sec)mysql>STARTTRANSACTION;--3.开启事务2QueryOK,0rowsaffected(0.00sec)mysql>INSERTINTO`user_info`(`id`,`name`,`gender`,`email`)VALUES(4,'White','男','white@163.com');--4.在事务2中插入一条新数据QueryOK,1rowaffected(0.00sec)mysql>commit;--5.提交事务2QueryOK,0rowsaffected(0.00sec)mysql>select*fromuser_info;+----+-------+--------+---------------+|id|name|gender|email|+----+-------+--------+---------------+|1|Curry|男|curry@163.com||2|Wade|男|wade@163.com||3|James|男|james@163.com||4|White|男|white@163.com|+----+-------+--------+---------------+4rowsinset(0.00sec)场景验证结果从场景二来看RR级别下仍然是可以避免幻读的问题，那是不是就可以确定RR级别下已经完全解决了幻读的问题呢。那我们再换一种更新方式来看看吧。场景三开启事务1在事务1中查询用户信息开启事务2在事务2中插入一条新数据提交事务2在事务1中将所有用户的邮箱信息的后缀更换为@gmail.com在事务1中再次查询用户信息提交事务1mysql>STARTTRANSACTION;--1.开启事务1QueryOK,0rowsaffected(0.00sec)mysql>select*fromuser_info;--2.在事务1中查询用户信息+----+-------+--------+---------------+|id|name|gender|email|+----+-------+--------+---------------+|1|Curry|男|curry@163.com||2|Wade|男|wade@163.com||3|James|男|james@163.com|+----+-------+--------+---------------+3rowsinset(0.00sec)mysql>updateuser_infosetemail=REPLACE(email,'@163.com','@gmail.com');--6.在事务1中将所有用户的邮箱信息的后缀更换为@gmail.comQueryOK,4rowsaffected(0.00sec)Rowsmatched:4Changed:4Warnings:0mysql>select*fromuser_info;--7.在事务1中再次查询用户信息+----+-------+--------+-----------------+|id|name|gender|email|+----+-------+--------+----------------+|1|Curry|男|curry@gmail.com||2|Wade|男|wade@gmail.com||3|James|男|james@gmail.com||4|White|男|white@gmail.com|+----+-------+--------+-----------------+4rowsinset(0.00sec)mysql>commit;--8.提交事务1QueryOK,0rowsaffected(0.00sec)mysql>STARTTRANSACTION;--3.开启事务2QueryOK,0rowsaffected(0.00sec)mysql>INSERTINTO`user_info`(`id`,`name`,`gender`,`email`)VALUES(4,'White','男','white@163.com');--4.在事务2中插入一条新数据QueryOK,1rowaffected(0.00sec)mysql>commit;--5.提交事务2QueryOK,0rowsaffected(0.00sec)mysql>select*fromuser_info;+----+-------+--------+---------------+|id|name|gender|email|+----+-------+--------+---------------+|1|Curry|男|curry@163.com||2|Wade|男|wade@163.com||3|James|男|james@163.com||4|White|男|white@163.com|+----+-------+--------+---------------+4rowsinset(0.00sec)场景验证结果事务1在进行更新之后再次查询读取到了事务2新插入到数据，出现了幻读。2.3.3 小结看来RR级别的确没有完全解决幻读问题，那为什么还会存在幻读的问题呢，为什么更新的方式不同，会出现不同的结果，什么情况下还会出现幻读问题呢。带着这样的疑问，我们来探索下～3 分析 从验证结果来看，当前读是可以完全避免幻读的问题，而对于快照读如果在两次读取之间进行了当前读，在某些情况下是会触发幻读的问题。那么下面我们可以从当前读的实现(MVCC)的角度来分析幻读问题的产生原因。我们应该知道MVCC实现原理主要是依赖记录中的3个隐式字段，undo日志，Read View来实现的，好，那么我们基于产生幻读的场景结合MVCC的实现原理来一步步进行分析。1.产生幻读的场景初始三条测试数据开启事务1在事务1中查询用户信息开启事务2在事务2中插入一条新数据提交事务2在事务1中将所有用户的邮箱信息更换为@gmail.com在事务1中再次查询用户信息提交事务11.1 执行步骤0：初始三条测试数据在初始化三条数据后三条初始数据分别会有三个隐式字段值，DB_TRX_ID(事务id),DB_ROLL_PTR(回滚指针),DB_ROW_ID(隐式主键)。如下：因为是新插入的数据,回滚指针字段的值均为NULL。+----+-------+--------+--------------+-----------+-----------+---------+|id|name|gender|email|DB_TRX_ID|DB_ROLL_PTR|DB_ROW_ID|+----+-------+--------+--------------+-----------+-----------+---------+|1|Curry|男|curry@163.com|2334|NULL|1||2|Wade|男|wade@163.com|2334|NULL|2||3|James|男|james@163.com|2334|NULL|3|+----+-------+--------+--------------+-----------+-----------+---------+1.2 执行步骤1：开启事务1在开启事务1后会为事务1分配一个唯一的事务idmysql>SELECTtrx_id,trx_state,trx_startedFROMINFORMATION_SCHEMA.INNODB_TRX;+-----------------+-----------+---------------------+|trx_id|trx_state|trx_started|+-----------------+-----------+---------------------+|2335|RUNNING|2024-07-2821:31:52|+-----------------+-----------+---------------------+1rowinset(0.00sec)1.3 执行步骤2：在事务1中查询用户信息因为是开启事务后的首次查询，所以此时会生成一张Read Veaw读视图，此时trx_list，up_limit_id，low_limit_id的值分别为：trx_list：因为是测试验证，无其他并发事务参与，所以活跃事务列表中只有当前的事务id[2335];up_limit_id：活跃事务列表中最小的事务id，即当前事务id:2335;low_limit_id：下一个未开始的事务id,即当前事务id+1为:2336;此时查询数据会使用当前生成的Read View并依据可见性算法来进行查询，因为数据库中数据的事务id均小于up_limit_id所以对当前事务均是可见的，所以三条初始数据会全部被查询出来。注: 可见性算法首先比较 DB_TRX_ID = low_limit_id , 如果大于等于则代表 DB_TRX_ID 所在的记录在 Read View 生成后才出现的，那对当前事务肯定不可见，如果小于则进入下一个判断判断 DB_TRX_ID 是否在活跃事务之中，trx_list.contains (DB_TRX_ID)，如果在，则代表Read View 生成时刻，这个事务仍处于活跃中，还没有commit，如果DB_TRX_ID=creator_trx_id，则说明是当前事务自己产生的数据，是可见的，如果不等于，则为其他事务修改的数据，当前事务也是看不见的；如果不在活跃事务之中，则说明，你这个事务在Read View生成之前就已经commit了，修改的结果，当前事务是能够看见的。1.4 执行步骤3：开启事务2在开启事务2后会为事务2分配一个唯一的事务id。事务id的分配是递增的，因此事务2的事务id一定是大于事务1。mysql>SELECTtrx_id,trx_state,trx_startedFROMINFORMATION_SCHEMA.INNODB_TRX;+-----------------+-----------+---------------------+|trx_id|trx_state|trx_started|+-----------------+-----------+---------------------+|2336|RUNNING|2024-07-2821:35:52|+-----------------+-----------+---------------------+1rowinset(0.00sec)1.5 执行步骤4：在事务2中插入一条新数据此时会产生一条新插入数据的insert undolog日志1.6 执行步骤5：提交事务2由于事务提交插入的数据会实际生效，insert undolog日志会被删除,此时表的数据情况如下：+----+-------+--------+--------------+-----------+-----------+---------+|id|name|gender|email|DB_TRX_ID|DB_ROLL_PTR|DB_ROW_ID|+----+-------+--------+--------------+-----------+-----------+---------+|1|Curry|男|curry@163.com|2334|NULL|1||2|Wade|男|wade@163.com|2334|NULL|2||3|James|男|james@163.com|2334|NULL|3||4|White|男|white@163.com|2336|NULL|4|+----+-------+--------+--------------+-----------+-----------+---------+1.7 执行步骤6：在事务1中将所有用户的邮箱信息的后缀更换为@gmail.com因为是更新操作，所以是当前读会将所有的符合条件的数据都读取出来，进行更新。更新后的数据表中的数据如下：+----+-------+--------+----------------+-----------+-----------+---------+|id|name|gender|email|DB_TRX_ID|DB_ROLL_PTR|DB_ROW_ID|+----+-------+--------+----------------+-----------+-----------+---------+|1|Curry|男|curry@gmail.com|2335|0x123825|1||2|Wade|男|wade@gmail.com|2335|0x153125|2||3|James|男|james@gmail.com|2335|0x115725|3||4|White|男|white@gmail.com|2335|0x163225|4|+----+-------+--------+----------------+-----------+-----------+---------+undolog情况如下：1.8 执行步骤7：在事务1中再次查询用户信息当前是RR的隔离级别，所以此时使用的Read View读视图仍然是首次查询生成的读视图。依据Read View的可见性算法分析，分别对四条数据的undolog版本链从尾部至头部逐一进行可见性判断是否可见进行追溯，会看到四条数据的尾部版本就可对当前事务可见。所以四条数据是会在此次查询中全部被查询得到。由此可以推断产生幻读的原因啦，因为事务1中的更新操作，对事务2中的新插入的数据也进行了更新，更新后新数据的undolog日志中会追加此次更新的回滚日志，并指向新插入数据的undolog记录，此时根据MVCC的可见性算法，事务2新插入的数据此时对于事务1也变成了可见的，因此产生了幻读的问题。那同样是更新场景二为什么没有产生幻读的问题呢？在场景二中，更新语句更新的是事务1第一次查询可见的数据，而对事务2中新插入的数据没有进行任何操作，新插入数据的版本链中是不存在当前事务产生的版本数据的，因此新插入的数据对与事务1仍然不可见，所以没有产生幻读问题。4 总结 当前读可以通过锁机制完全避免幻读问题，快照读如果中间对其他事务已提交的插入或更新的数据进行了更新，则会出现幻读的问题。如何进行避免呢？采用串行化的隔离级别（不建议）；开发时注意考虑这种产生幻读的场景，尽量通过调整代码逻辑规避幻读问题的发生（建议）；若不能通过调整代码逻辑规避，可以考虑采用当前读的方式避免（建议）；关于作者孔德志 采货侠JAVA开发工程师想了解更多转转公司的业务实践，欢迎点击关注下方公众号：