MySQL 20 幻读是什么，幻读有什么问题？
<p>首先给出要用到的数据：</p><pre><code class="language-sql">CREATE TABLE `t` (
`id` int(11) NOT NULL,
`c` int(11) DEFAULT NULL,
`d` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `c` (`c`)
) ENGINE=InnoDB;
insert into t values(0,0,0),(5,5,5),
(10,10,10),(15,15,15),(20,20,20),(25,25,25);
</code></pre>
<p>那么下面的语句，是怎么加锁的，加的锁又是什么时候释放的？</p>
<pre><code class="language-sql">begin;
select * from t where d=5 for update;
commit;
</code></pre>
<p>该语句会命中<code>d=5</code>的行，对应的主键<code>id=5</code>，因此在select语句执行完成后，<code>id=5</code>这一行会加一个写锁，且由于两阶段锁协议，该写锁会在执行commit语句时候释放。</p>
<p>由于字段d上没有索引，该语句会做全表扫描，那么其他被扫描到但不符合条件的记录是否会被加锁呢？</p>
<p>本文接下来没有特殊说明的，都是设定在可重复读隔离级别。</p>
<h3 id="幻读是什么">幻读是什么？</h3>
<p>先看如果只在<code>id=5</code>的行加锁会怎么样。</p>
<p>假设有这样一个场景：</p>
<div align="center"><img src="https://img2024.cnblogs.com/blog/3389949/202507/3389949-20250721111338388-1096269899.png" width="50%"></div>
<p>分析session A里的三次执行：</p>
<ul>
<li>
<p>Q1只返回<code>id=5</code>的行；</p>
</li>
<li>
<p>在T2时刻，session B把<code>id=0</code>的d值改成了5，因此T3时刻Q2能返回两行；</p>
</li>
<li>
<p>在T4时刻，session C插入一行，因此Q3时刻查出来3行。</p>
</li>
</ul>
<p>其中，Q3读到<code>id=1</code>这一行的现象，被称为<strong>幻读</strong>。幻读指的是一个事务前后两次查询<strong>同一个范围</strong>的时候，后一次查询看到了前一次查询没有看到的行。</p>
<p>这里对幻读做说明：</p>
<ul>
<li>
<p>在可重复读隔离级别下，普通的查询是快照读，不会看到别的事务插入的数据，因此幻读只有在当前读的情况下才会出现；</p>
</li>
<li>
<p>session B的修改结果，被session A后的select语句用当前读看到，不能称为幻读，幻读专指新插入的行。</p>
</li>
</ul>
<p>这三个查询都是加了for update，因此都是当前读，要读到所有已经提交的记录的最新值。</p>
<p>从事务可见性规则分析的话，这三条SQL语句的返回结果都没有问题。但是由于造成了幻读，是有其他问题的。</p>
<h3 id="幻读有什么问题">幻读有什么问题？</h3>
<p>首先是语义上的问题。session A在T1时刻的语句实际上想要声明，要把所有<code>d=5</code>的行锁住，不允许别的事务进行读写操作。</p>
<p>如果现在看感觉不明显，看看如下情况：</p>
<div align="center"><img src="https://img2024.cnblogs.com/blog/3389949/202507/3389949-20250721111420393-601216731.png" width="50%"></div>
<p>在session B中，先将<code>id=0</code>的行也设置了<code>d=5</code>，之后设置<code>c=5</code>。由于在T1时刻，session A只是给<code>id=5</code>的行加了行锁，并没有给<code>id=0</code>的行加锁，因此session B是可以执行这两条update语句的，这就破坏了session A在T1时刻的加锁声明。</p>
<p>session C也是一样，对<code>id=1</code>的行的修改也破坏了加锁声明。</p>
<p>其次是数据一致性的问题。锁的设计是为了保证数据的一致性，这不止是数据库内部数据状态在此刻的一致性，还包含了数据和日志在逻辑上的一致性。</p>
<p>比如如下的情况：</p>
<div align="center"><img src="https://img2024.cnblogs.com/blog/3389949/202507/3389949-20250721111443413-1933599849.png" width="50%"></div>
<p>分析数据库里的变化：</p>
<ul>
<li>
<p>经过T1时刻，<code>id=5</code>这一行变为(5,5,100)，这个结果在T6正式提交；</p>
</li>
<li>
<p>经过T2时刻，<code>id=0</code>这一行变为(0,5,5)；</p>
</li>
<li>
<p>经过T4时刻，表里多了一行(1,5,5)；</p>
</li>
<li>
<p>其他行与这个执行序列无关，保持不变。</p>
</li>
</ul>
<p>而binlog里的变化：</p>
<ul>
<li>
<p>T2时刻，session B事务提交，写入了两条语句；</p>
</li>
<li>
<p>T4时刻，session C事务提交，写入了两条语句；</p>
</li>
<li>
<p>T6时刻，session A事务提交，写入了<code>update t set d=100 where d=5</code>这条语句。</p>
</li>
</ul>
<p>统一一下，就是：</p>
<pre><code class="language-sql">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*/
</code></pre>
<p>可以看出，这个语句序列，不论是拿到备库执行，还是以后用binlog来克隆，这三行的结果都变成了(0,5,100)、(1,5,100)和(5,5,100)。</p>
<p>即<code>id=0,id=1</code>两行发生了数据不一致。这是假设<code>select * from t where d=5 for update</code>只给<code>id=5</code>的行加锁导致的。</p>
<p>所以我们认为上面的设定不合理，假设改为“扫描过程中碰到的行都加上写锁”：</p>
<div align="center"><img src="https://img2024.cnblogs.com/blog/3389949/202507/3389949-20250721111519108-630441720.png" width="50%"></div>
<p>由于session A把所有的行都加了写锁，所以session B在执行第一个语句时就被锁住，需要等到T6时刻session A提交后，session B才能继续执行。这样binlog里执行序列为：</p>
<pre><code class="language-sql">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*/
update t set d=5 where id=0; /*(0,0,5)*/
update t set c=5 where id=0; /*(0,5,5)*/
</code></pre>
<p>可以看到，<code>id=0</code>的行最终结果为(0,5,5)，解决了不一致，但<code>id=1</code>这一行还是不一致。这是因为在T3时刻，给所有行加锁的时候，<code>id=1</code>这一行还不存在，也就加不上锁。</p>
<p>也就是说，即使把所有的记录都加上锁，还是阻止不了新插入的记录。</p>
<p>到这里，实际上已经说明了幻读的定义以及幻读有什么问题。接下来，看看InnoDB怎么解决幻读。</p>
<h3 id="如何解决幻读">如何解决幻读？</h3>
<p>从上面可以看出，产生幻读的原因是，行锁只能锁住行，但是新插入记录，要更新的是记录之间的“间隙”。因此为了解决幻读，InnoDB引入了间隙锁。</p>
<p>在本文的场景中，初始化插入了6个记录，会产生7个间隙：</p>
<div align="center"><img src="https://img2024.cnblogs.com/blog/3389949/202507/3389949-20250721111552669-2091404342.png" width="50%"></div>
<p>当执行<code>select * from t where d=5 for update</code>，就不止是给数据库中已有的6个记录加上了行锁，还同时加了7个间隙锁，这样就确保无法再插入新记录。</p>
<p>间隙锁不像行锁那样，行锁之间会有冲突，间隙锁之间不存在冲突关系，跟间隙锁存在冲突关系的，是“往这个间隙中插入一个记录”的操作。</p>
<p>比如：</p>
<div align="center"><img src="https://img2024.cnblogs.com/blog/3389949/202507/3389949-20250721111615842-1173325613.png" width="50%"></div>
<p>由于表里没有<code>c=7</code>的记录，因此session A和session B都是想要加间隙锁(5,10)，都是想保护这个间隙，因此它们之间不冲突，session B不会被堵住。</p>
<p>间隙锁和行锁合称临键锁（next-key lock），临键锁是前开后闭的区间。在本文的例子，如果用<code>select * from t for update</code>把整个表所有记录锁起来，会形成7个临键锁，分别是<span class="math inline">\((-\infty,0],(0,5],(5,10],(10,15],(15,20],(20,25],(25,+\text{supremun}]\)</span>。</p>
<p>由于<span class="math inline">\(+\infty\)</span>是开区间，为了做到前开后闭，InnoDB给每个索引加了一个不存在的最大值supremum。</p>
<p>间隙锁和临键锁的引入，能帮助解决幻读的问题，但同时会带来另外的问题。比如下面的场景：</p>
<div align="center"><img src="https://img2024.cnblogs.com/blog/3389949/202507/3389949-20250721111641063-797467228.png" width="50%"></div>
<p>分析该场景：</p>
<ul>
<li>
<p>session A执行<code>select … for update</code>，由于<code>id=9</code>行不存在，会加上间隙锁(5,10)；</p>
</li>
<li>
<p>session B执行<code>select … for update</code>同样会加上间隙锁(5,10)；</p>
</li>
<li>
<p>session B想要插入(9,9,9)，被session A的间隙锁挡住，进入等待；</p>
</li>
<li>
<p>session A想要插入(9,9,9)，被session B的间隙锁挡住。</p>
</li>
</ul>
<p>至此形成了死锁。因此间隙锁的引入，可能导致同样的语句锁住更大的范围，会影响并发度。</p>
<p>为了减少死锁，很多公司实际使用读已提交的隔离级别，同时将binlog格式设置成row，以解决可能出现的数据和日志不一致问题。</p><br><br>
来源：https://www.cnblogs.com/san-mu/p/18995448
頁:
[1]