Mysql加锁过程详解（2）-关于mysql 幻读理解

╰半夏微凉° 2022-05-30 04:36 184阅读 0赞

首先，mysql 幻读并非是”一个事务内进行两次相同操作居然得到了不一样的结果”，因为它根本不可能发生在使用了 read view / MVCC 的 RR 隔离级别下，这种幻读的定义更适合给 Oracle，Oracle 的事务隔离只有两级，RC 和 Serializable。然后还有很多人辩解说不可重复读是针对某条记录的，幻读是针对记录集合的，这是在自我安慰么？

这里给出 mysql 幻读的比较形象的场景：

users： id 主键

1、T1：select \* from users where id = 1;

2、T2：insert into \`users\`(\`id\`, \`name\`) values (1, 'big cat');

3、T1：insert into \`users\`(\`id\`, \`name\`) values (1, 'big cat');

T1 ：主事务，检测表中是否有 id 为 1 的记录，没有则插入，这是我们期望的正常业务逻辑。

T2 ：干扰事务，目的在于扰乱 T1 的正常的事务执行。

在 RR 隔离级别下，1、2 是会正常执行的，3 则会报错主键冲突，对于 T1 的业务来说是执行失败的，这里 T1 就是发生了幻读，因为T1读取的数据状态并不能支持他的下一步的业务，见鬼了一样。

在 Serializable 隔离级别下，1 执行时是会隐式的添加 gap 共享锁的，从而 2 会被阻塞，3 会正常执行，对于 T1 来说业务是正确的，成功的扼杀了扰乱业务的T2，对于T1来说他读取的状态是可以拿来支持业务的。

所以 mysql 的幻读并非什么读取两次返回结果集不同，而是事务在插入事先检测不存在的记录时，惊奇的发现这些数据已经存在了，之前的检测读获取到的数据如同鬼影一般。

这里要灵活的理解读取的意思，第一次select是读取，第二次的 insert 其实也属于隐式的读取，只不过是在 mysql 的机制中读取的，插入数据也是要先读取一下有没有主键冲突才能决定是否执行插入。

不可重复读侧重表达 读-读，幻读则是说 读-写，用写来证实读的是鬼影。

下面例子版本

SELECT VERSION();

![464291-20170929181641075-1331573070.png][]

例子1，读提交

例子2：重复读

<table style="margin:0px; padding:0px; border:1px solid #C0C0C0; color:rgb(51,51,51); font-family:verdana,Arial,Helvetica,sans-serif; font-size:14px; background-color:rgb(255,255,255)"> 
 <tbody style="margin:0px; padding:0px"> 
 <tr style="margin:0px; padding:0px"> 
 <td style="margin:0px; padding:3px; border-color:#C0C0C0; border-collapse:collapse"> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; a </td> 
 <td style="margin:0px; padding:3px; border-color:#C0C0C0; border-collapse:collapse"> b </td> 
 </tr> 
 <tr style="margin:0px; padding:0px"> 
 <td colspan="2" style="margin:0px; padding:3px; border-color:#C0C0C0; border-collapse:collapse"> SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; </td> 
 </tr> 
 <tr style="margin:0px; padding:0px"> 
 <td colspan="2" style="margin:0px; padding:3px; border-color:#C0C0C0; border-collapse:collapse"> SET AUTOCOMMIT=0; </td> 
 </tr> 
 <tr style="margin:0px; padding:0px"> 
 <td colspan="2" style="margin:0px; padding:3px; border-color:#C0C0C0; border-collapse:collapse"> 1．可重复读 </td> 
 </tr> 
 <tr style="margin:0px; padding:0px"> 
 <td style="margin:0px; padding:3px; border-color:#C0C0C0; border-collapse:collapse"> begin </td> 
 <td style="margin:0px; padding:3px; border-color:#C0C0C0; border-collapse:collapse"> begin </td> 
 </tr> 
 <tr style="margin:0px; padding:0px"> 
 <td style="margin:0px; padding:3px; border-color:#C0C0C0; border-collapse:collapse"> INSERT test VALUES(1,1); </td> 
 <td style="margin:0px; padding:3px; border-color:#C0C0C0; border-collapse:collapse"> &nbsp; </td> 
 </tr> 
 <tr style="margin:0px; padding:0px"> 
 <td style="margin:0px; padding:3px; border-color:#C0C0C0; border-collapse:collapse"> SELECT * FROM test; &nbsp;<img src="https://images2017.cnblogs.com/blog/464291/201709/464291-20170929181434465-707008140.png" alt="" style="padding:0px; border:0px"> &nbsp; </td> 
 <td style="margin:0px; padding:3px; border-color:#C0C0C0; border-collapse:collapse"> SELECT * FROM test; &nbsp;<img src="https://images2017.cnblogs.com/blog/464291/201709/464291-20170929181438200-380512861.png" alt="" style="padding:0px; border:0px"> &nbsp; &nbsp; </td> 
 </tr> 
 <tr style="margin:0px; padding:0px"> 
 <td style="margin:0px; padding:3px; border-color:#C0C0C0; border-collapse:collapse"> commit </td> 
 <td style="margin:0px; padding:3px; border-color:#C0C0C0; border-collapse:collapse"> &nbsp; </td> 
 </tr> 
 <tr style="margin:0px; padding:0px"> 
 <td style="margin:0px; padding:3px; border-color:#C0C0C0; border-collapse:collapse"> &nbsp; </td> 
 <td style="margin:0px; padding:3px; border-color:#C0C0C0; border-collapse:collapse"> SELECT * FROM test; </td> 
 </tr> 
 <tr style="margin:0px; padding:0px"> 
 <td style="margin:0px; padding:3px; border-color:#C0C0C0; border-collapse:collapse"> &nbsp; </td> 
 <td style="margin:0px; padding:3px; border-color:#C0C0C0; border-collapse:collapse"> &nbsp;<img src="https://images2017.cnblogs.com/blog/464291/201709/464291-20170929181446825-542860738.png" alt="" style="margin-bottom:0px; padding:0px; border:0px"> &nbsp; </td> 
 </tr> 
 <tr style="margin:0px; padding:0px"> 
 <td style="margin:0px; padding:3px; border-color:#C0C0C0; border-collapse:collapse"> &nbsp; </td> 
 <td style="margin:0px; padding:3px; border-color:#C0C0C0; border-collapse:collapse"> COMMIT（这是由于可重复读的隔离级别保证了每次读到的结果都是一致的） </td> 
 </tr> 
 <tr style="margin:0px; padding:0px"> 
 <td style="margin:0px; padding:3px; border-color:#C0C0C0; border-collapse:collapse"> &nbsp; </td> 
 <td style="margin:0px; padding:3px; border-color:#C0C0C0; border-collapse:collapse"> BEGIN </td> 
 </tr> 
 <tr style="margin:0px; padding:0px"> 
 <td style="margin:0px; padding:3px; border-color:#C0C0C0; border-collapse:collapse"> &nbsp; </td> 
 <td style="margin:0px; padding:3px; border-color:#C0C0C0; border-collapse:collapse"> SELECT * FROM test; </td> 
 </tr> 
 <tr style="margin:0px; padding:0px"> 
 <td style="margin:0px; padding:3px; border-color:#C0C0C0; border-collapse:collapse"> &nbsp; </td> 
 <td style="margin:0px; padding:3px; border-color:#C0C0C0; border-collapse:collapse"> &nbsp;<img src="https://images2017.cnblogs.com/blog/464291/201709/464291-20170929181455012-85444060.png" alt="" style="margin-bottom:0px; padding:0px; border:0px"> &nbsp; </td> 
 </tr> 
 <tr style="margin:0px; padding:0px"> 
 <td style="margin:0px; padding:3px; border-color:#C0C0C0; border-collapse:collapse"> &nbsp; </td> 
 <td style="margin:0px; padding:3px; border-color:#C0C0C0; border-collapse:collapse"> COMMIT </td> 
 </tr> 
 <tr style="margin:0px; padding:0px"> 
 <td colspan="2" style="margin:0px; padding:3px; border-color:#C0C0C0; border-collapse:collapse"> B在一个事务中的查询结果没有改变 </td> 
 </tr> 
 <tr style="margin:0px; padding:0px"> 
 <td colspan="2" style="margin:0px; padding:3px; border-color:#C0C0C0; border-collapse:collapse"> 2锁 </td> 
 </tr> 
 <tr style="margin:0px; padding:0px"> 
 <td style="margin:0px; padding:3px; border-color:#C0C0C0; border-collapse:collapse"> begin </td> 
 <td style="margin:0px; padding:3px; border-color:#C0C0C0; border-collapse:collapse"> begin </td> 
 </tr> 
 <tr style="margin:0px; padding:0px"> 
 <td style="margin:0px; padding:3px; border-color:#C0C0C0; border-collapse:collapse"> INSERT test VALUES(2,2); </td> 
 <td style="margin:0px; padding:3px; border-color:#C0C0C0; border-collapse:collapse"> &nbsp; </td> 
 </tr> 
 <tr style="margin:0px; padding:0px"> 
 <td style="margin:0px; padding:3px; border-color:#C0C0C0; border-collapse:collapse"> &nbsp; </td> 
 <td style="margin:0px; padding:3px; border-color:#C0C0C0; border-collapse:collapse"> SELECT * FROM test; &nbsp;<img src="https://images2017.cnblogs.com/blog/464291/201709/464291-20170929181505122-383250492.png" alt="" style="padding:0px; border:0px"> &nbsp; </td> 
 </tr> 
 <tr style="margin:0px; padding:0px"> 
 <td style="margin:0px; padding:3px; border-color:#C0C0C0; border-collapse:collapse"> &nbsp; </td> 
 <td style="margin:0px; padding:3px; border-color:#C0C0C0; border-collapse:collapse"> INSERT test VALUES(2,2); </td> 
 </tr> 
 <tr style="margin:0px; padding:0px"> 
 <td style="margin:0px; padding:3px; border-color:#C0C0C0; border-collapse:collapse"> &nbsp; </td> 
 <td style="margin:0px; padding:3px; border-color:#C0C0C0; border-collapse:collapse"> Lock wait timeout exceeded; try restarting transaction </td> 
 </tr> 
 <tr style="margin:0px; padding:0px"> 
 <td style="margin:0px; padding:3px; border-color:#C0C0C0; border-collapse:collapse"> COMMIT </td> 
 <td style="margin:0px; padding:3px; border-color:#C0C0C0; border-collapse:collapse"> COMMIT（这块造成死锁的原因一定要弄明白？） </td> 
 </tr> 
 <tr style="margin:0px; padding:0px"> 
 <td colspan="2" style="margin:0px; padding:3px; border-color:#C0C0C0; border-collapse:collapse"> 3(幻读) </td> 
 </tr> 
 <tr style="margin:0px; padding:0px"> 
 <td style="margin:0px; padding:3px; border-color:#C0C0C0; border-collapse:collapse"> BEGIN </td> 
 <td style="margin:0px; padding:3px; border-color:#C0C0C0; border-collapse:collapse"> BEGIN </td> 
 </tr> 
 <tr style="margin:0px; padding:0px"> 
 <td style="margin:0px; padding:3px; border-color:#C0C0C0; border-collapse:collapse"> INSERT test VALUES(3,3); </td> 
 <td style="margin:0px; padding:3px; border-color:#C0C0C0; border-collapse:collapse"> &nbsp; </td> 
 </tr> 
 <tr style="margin:0px; padding:0px"> 
 <td style="margin:0px; padding:3px; border-color:#C0C0C0; border-collapse:collapse"> SELECT * FROM test; &nbsp;<img src="https://images2017.cnblogs.com/blog/464291/201709/464291-20170929181521622-2144019310.png" alt="" style="padding:0px; border:0px"> &nbsp; </td> 
 <td style="margin:0px; padding:3px; border-color:#C0C0C0; border-collapse:collapse"> SELECT * FROM test; &nbsp;<img src="https://images2017.cnblogs.com/blog/464291/201709/464291-20170929181528309-584037273.png" alt="" style="padding:0px; border:0px"> &nbsp; </td> 
 </tr> 
 <tr style="margin:0px; padding:0px"> 
 <td style="margin:0px; padding:3px; border-color:#C0C0C0; border-collapse:collapse"> COMMIT </td> 
 <td style="margin:0px; padding:3px; border-color:#C0C0C0; border-collapse:collapse"> &nbsp; </td> 
 </tr> 
 <tr style="margin:0px; padding:0px"> 
 <td style="margin:0px; padding:3px; border-color:#C0C0C0; border-collapse:collapse"> &nbsp; </td> 
 <td style="margin:0px; padding:3px; border-color:#C0C0C0; border-collapse:collapse"> SELECT * FROM test; &nbsp;<img src="https://images2017.cnblogs.com/blog/464291/201709/464291-20170929181533028-1715226359.png" alt="" style="padding:0px; border:0px"> &nbsp; </td> 
 </tr> 
 <tr style="margin:0px; padding:0px"> 
 <td style="margin:0px; padding:3px; border-color:#C0C0C0; border-collapse:collapse"> &nbsp; </td> 
 <td style="margin:0px; padding:3px; border-color:#C0C0C0; border-collapse:collapse"> INSERT test VALUES(3,3); </td> 
 </tr> 
 <tr style="margin:0px; padding:0px"> 
 <td style="margin:0px; padding:3px; border-color:#C0C0C0; border-collapse:collapse"> &nbsp; </td> 
 <td style="margin:0px; padding:3px; border-color:#C0C0C0; border-collapse:collapse"> Duplicate entry '3' for key 'PRIMARY' </td> 
 </tr> 
 <tr style="margin:0px; padding:0px"> 
 <td style="margin:0px; padding:3px; border-color:#C0C0C0; border-collapse:collapse"> &nbsp; </td> 
 <td style="margin:0px; padding:3px; border-color:#C0C0C0; border-collapse:collapse"> COMMIT </td> 
 </tr> 
 <tr style="margin:0px; padding:0px"> 
 <td style="margin:0px; padding:3px; border-color:#C0C0C0; border-collapse:collapse"> &nbsp; </td> 
 <td style="margin:0px; padding:3px; border-color:#C0C0C0; border-collapse:collapse"> BEGIN </td> 
 </tr> 
 <tr style="margin:0px; padding:0px"> 
 <td style="margin:0px; padding:3px; border-color:#C0C0C0; border-collapse:collapse"> &nbsp; </td> 
 <td style="margin:0px; padding:3px; border-color:#C0C0C0; border-collapse:collapse"> SELECT * FROM test; &nbsp;<img src="https://images2017.cnblogs.com/blog/464291/201709/464291-20170929181540762-71659665.png" alt="" style="padding:0px; border:0px"> &nbsp; </td> 
 </tr> 
 <tr style="margin:0px; padding:0px"> 
 <td style="margin:0px; padding:3px; border-color:#C0C0C0; border-collapse:collapse"> &nbsp; </td> 
 <td style="margin:0px; padding:3px; border-color:#C0C0C0; border-collapse:collapse"> COMMIT </td> 
 </tr> 
 <tr style="margin:0px; padding:0px"> 
 <td colspan="2" style="margin:0px; padding:3px; border-color:#C0C0C0; border-collapse:collapse"> 幻读，b明明查到没有，插入时候提示主键冲突，刚刚查询没有，出现幻觉？ </td> 
 </tr> 
 <tr style="margin:0px; padding:0px"> 
 <td colspan="2" style="margin:0px; padding:3px; border-color:#C0C0C0; border-collapse:collapse"> &nbsp; </td> 
 </tr> 
 <tr style="margin:0px; padding:0px"> 
 <td style="margin:0px; padding:3px; border-color:#C0C0C0; border-collapse:collapse"> &nbsp; </td> 
 <td style="margin:0px; padding:3px; border-color:#C0C0C0; border-collapse:collapse"> &nbsp; </td> 
 </tr> 
 <tr style="margin:0px; padding:0px"> 
 <td style="margin:0px; padding:3px; border-color:#C0C0C0; border-collapse:collapse"> &nbsp; </td> 
 <td style="margin:0px; padding:3px; border-color:#C0C0C0; border-collapse:collapse"> begin </td> 
 </tr> 
 <tr style="margin:0px; padding:0px"> 
 <td style="margin:0px; padding:3px; border-color:#C0C0C0; border-collapse:collapse"> &nbsp; </td> 
 <td style="margin:0px; padding:3px; border-color:#C0C0C0; border-collapse:collapse"> INSERT test VALUES(4,4); </td> 
 </tr> 
 <tr style="margin:0px; padding:0px"> 
 <td style="margin:0px; padding:3px; border-color:#C0C0C0; border-collapse:collapse"> &nbsp; </td> 
 <td style="margin:0px; padding:3px; border-color:#C0C0C0; border-collapse:collapse"> COMMIT </td> 
 </tr> 
 <tr style="margin:0px; padding:0px"> 
 <td colspan="2" style="margin:0px; padding:3px; border-color:#C0C0C0; border-collapse:collapse"> 4．可重复读 </td> 
 </tr> 
 <tr style="margin:0px; padding:0px"> 
 <td style="margin:0px; padding:3px; border-color:#C0C0C0; border-collapse:collapse"> BEGIN </td> 
 <td style="margin:0px; padding:3px; border-color:#C0C0C0; border-collapse:collapse"> BEGIN </td> 
 </tr> 
 <tr style="margin:0px; padding:0px"> 
 <td style="margin:0px; padding:3px; border-color:#C0C0C0; border-collapse:collapse"> SELECT COUNT(*) FROM test WHERE a&gt;2;<img src="https://images2017.cnblogs.com/blog/464291/201709/464291-20170929181557512-144452885.png" alt="" style="padding:0px; border:0px"> &nbsp; </td> 
 <td style="margin:0px; padding:3px; border-color:#C0C0C0; border-collapse:collapse"> SELECT COUNT(*) FROM test WHERE a&gt;2;<img src="https://images2017.cnblogs.com/blog/464291/201709/464291-20170929181602075-295796222.png" alt="" style="padding:0px; border:0px"> &nbsp; </td> 
 </tr> 
 <tr style="margin:0px; padding:0px"> 
 <td style="margin:0px; padding:3px; border-color:#C0C0C0; border-collapse:collapse"> </td> 
 <td style="margin:0px; padding:3px; border-color:#C0C0C0; border-collapse:collapse"> </td> 
 </tr> 
 <tr style="margin:0px; padding:0px"> 
 <td style="margin:0px; padding:3px; border-color:#C0C0C0; border-collapse:collapse"> INSERT test VALUES(5,5); </td> 
 <td style="margin:0px; padding:3px; border-color:#C0C0C0; border-collapse:collapse"> &nbsp; </td> 
 </tr> 
 <tr style="margin:0px; padding:0px"> 
 <td style="margin:0px; padding:3px; border-color:#C0C0C0; border-collapse:collapse"> SELECT COUNT(*) FROM test WHERE a&gt;2;<img src="https://images2017.cnblogs.com/blog/464291/201709/464291-20170929181607044-1440714700.png" alt="" style="padding:0px; border:0px"> &nbsp; </td> 
 <td style="margin:0px; padding:3px; border-color:#C0C0C0; border-collapse:collapse"> SELECT COUNT(*) FROM test WHERE a&gt;2;<img src="https://images2017.cnblogs.com/blog/464291/201709/464291-20170929181612106-2088479400.png" alt="" style="padding:0px; border:0px"> &nbsp; </td> 
 </tr> 
 <tr style="margin:0px; padding:0px"> 
 <td style="margin:0px; padding:3px; border-color:#C0C0C0; border-collapse:collapse"> </td> 
 <td style="margin:0px; padding:3px; border-color:#C0C0C0; border-collapse:collapse"> </td> 
 </tr> 
 <tr style="margin:0px; padding:0px"> 
 <td style="margin:0px; padding:3px; border-color:#C0C0C0; border-collapse:collapse"> COMMIT </td> 
 <td style="margin:0px; padding:3px; border-color:#C0C0C0; border-collapse:collapse"> &nbsp; </td> 
 </tr> 
 <tr style="margin:0px; padding:0px"> 
 <td style="margin:0px; padding:3px; border-color:#C0C0C0; border-collapse:collapse"> BEGIN </td> 
 <td style="margin:0px; padding:3px; border-color:#C0C0C0; border-collapse:collapse"> &nbsp; </td> 
 </tr> 
 <tr style="margin:0px; padding:0px"> 
 <td style="margin:0px; padding:3px; border-color:#C0C0C0; border-collapse:collapse"> SELECT COUNT(*) FROM test WHERE a&gt;2; </td> 
 <td style="margin:0px; padding:3px; border-color:#C0C0C0; border-collapse:collapse"> SELECT COUNT(*) FROM test WHERE a&gt;2; </td> 
 </tr> 
 <tr style="margin:0px; padding:0px"> 
 <td style="margin:0px; padding:3px; border-color:#C0C0C0; border-collapse:collapse"> &nbsp;<img src="https://images2017.cnblogs.com/blog/464291/201709/464291-20170929181623731-1638952317.png" alt="" style="margin-bottom:0px; padding:0px; border:0px"> &nbsp; </td> 
 <td style="margin:0px; padding:3px; border-color:#C0C0C0; border-collapse:collapse"> &nbsp;<img src="https://images2017.cnblogs.com/blog/464291/201709/464291-20170929181627950-346414788.png" alt="" style="margin-bottom:0px; padding:0px; border:0px"> &nbsp; </td> 
 </tr> 
 <tr style="margin:0px; padding:0px"> 
 <td style="margin:0px; padding:3px; border-color:#C0C0C0; border-collapse:collapse"> COMMIT </td> 
 <td style="margin:0px; padding:3px; border-color:#C0C0C0; border-collapse:collapse"> COMMIT </td> 
 </tr> 
 <tr style="margin:0px; padding:0px"> 
 <td colspan="2" style="margin:0px; padding:3px; border-color:#C0C0C0; border-collapse:collapse"> &nbsp; 
 <div> 
 
 </div> </td> 
 </tr> 
 </tbody> 
</table>

原文链接： [http://www.cnblogs.com/crazylqy/p/7612230.html][http_www.cnblogs.com_crazylqy_p_7612230.html]

[464291-20170929181641075-1331573070.png]: /images/20220530/b114735f31134f00b9a8f1abced890c0.png
[http_www.cnblogs.com_crazylqy_p_7612230.html]: http://www.cnblogs.com/crazylqy/p/7612230.html