Mysql的行级锁到底是怎么加的？
<h2 id="开篇结论">开篇结论</h2><p>加锁的对象是<strong>索引</strong>，加锁的基本单位是 next-key lock，它是由记录锁和间隙锁组合而成的，next-key lock 是<strong>左开右闭</strong>区间，而间隙锁是<strong>左开右开</strong>区间。</p>
<p>在<strong>只使用记录锁或者间隙锁</strong>就能避免幻读现象的场景下， next-key lock 就会退化成记录锁或间隙锁。</p>
<p>假设这个表，id 是主键索引（唯一索引），age 是普通索引（非唯一索引），name 是普通的列。数据如下：<br>
<img src="https://seven97-blog.oss-cn-hangzhou.aliyuncs.com/imgs/202404261918340.png" alt="image-20240426191021264" loading="lazy"></p>
<h2 id="唯一索引等值查询">唯一索引等值查询</h2>
<ul>
<li>当查询的记录是「存在」的，在索引树上定位到这一条记录后，将该记录的索引中的 next-key lock 会退化成「记录锁」。</li>
<li>当查询的记录是「不存在」的，在索引树找到第一条大于该查询记录的记录后，将该记录的索引中的 next-key lock 会退化成「间隙锁」。</li>
</ul>
<h3 id="记录存在的情况">记录存在的情况</h3>
<p>假设事务 A 执行了这条等值查询语句，查询的记录是<strong>存在</strong>于表中的。</p>
<pre><code class="language-sql">select * from user where id = 1 for update;
</code></pre>
<p>那么，事务 A 会为 id 为 1 的这条记录就会加上 X 型的记录锁。<br>
<img src="https://seven97-blog.oss-cn-hangzhou.aliyuncs.com/imgs/202404261918052.png" alt="" loading="lazy"></p>
<p>接下来，如果有其他事务，对 id 为 1 的记录进行更新或者删除操作的话，这些操作都会被阻塞</p>
<blockquote>
<p>为什么这里退化为了记录锁？<br>
原因在于这里<strong>仅靠记录锁</strong>也能避免幻读的问题。</p>
</blockquote>
<ul>
<li>当其他事务，对 id 为 1 的记录进行更新或者删除或插入id为1的数据的操作时，由于记录锁，这些操作都会被阻塞，也就不会出现前后两次查询的结果集不同，也就避免了幻读的问题。</li>
</ul>
<h3 id="记录不存在的情况">记录不存在的情况</h3>
<p>假设事务 A 执行了这条等值查询语句，查询的记录是<strong>不存在</strong>于表中的。</p>
<pre><code class="language-sql">select * from user where id = 2 for update;
</code></pre>
<p>此时事务 A 在 id = 5 记录的主键索引上加的是间隙锁，锁住的范围是 (1, 5)。<br>
<img src="https://seven97-blog.oss-cn-hangzhou.aliyuncs.com/imgs/202404261918668.png" alt="" loading="lazy"></p>
<p>接下来，如果有其他事务插入 id 值为 2、3、4 这一些记录的话，这些插入语句都会发生阻塞。</p>
<blockquote>
<p>为什么这里退化为了间隙锁？<br>
原因在于这里<strong>仅靠间隙锁</strong>也能避免幻读的问题。</p>
</blockquote>
<ul>
<li>如果这里加的是next-key lock，那就意味着其他事务无法更新/删除 id = 5 这条记录，但实际上即使更新/删除 id = 5 这条记录，也不会出现前后两次查询的结果集不同，查不到的还是查不到。</li>
</ul>
<h2 id="唯一索引范围查询">唯一索引范围查询</h2>
<p>当唯一索引进行范围查询时，会对每一个扫描到的索引加 next-key 锁，然后如果遇到下面这些情况，会退化成记录锁或者间隙锁：</p>
<ul>
<li>针对<strong>大于等于</strong>的范围查询，因为存在等值查询的条件，那么如果等值查询的记录是存在于表中，那么该记录的索引中的 next-key 锁会退化成记录锁。</li>
<li>针对<strong>小于或者小于等于</strong>的范围查询，要看条件值的记录是否存在于表中：
<ul>
<li>当条件值的记录不在表中，那么不管是<strong>小于</strong>还是<strong>小于等于</strong>条件的范围查询，扫描到终止范围查询的记录时，该记录的索引的 next-key 锁会退化成间隙锁，其他扫描到的记录，都是在这些记录的索引上加 next-key 锁。</li>
<li>当条件值的记录在表中，如果是<strong>小于</strong>条件的范围查询，扫描到终止范围查询的记录时，该记录的索引的 next-key 锁会退化成间隙锁，其他扫描到的记录，都是在这些记录的索引上加 next-key 锁；如果<strong>小于等于</strong>条件的范围查询，扫描到终止范围查询的记录时，该记录的索引 next-key 锁不会退化成间隙锁。其他扫描到的记录，都是在这些记录的索引上加 next-key 锁。</li>
</ul>
</li>
</ul>
<h3 id="针对-大于或者大于等于-的范围查询">针对 大于或者大于等于 的范围查询</h3>
<h4 id="针对-大于-的范围查询的情况">针对 大于 的范围查询的情况</h4>
<p>假设事务 A 执行了这条范围查询语句：</p>
<pre><code class="language-sql">select * from user where id > 15 for update;
</code></pre>
<p>此时，事务 A 在主键索引上加了两个next-key 锁：<br>
<img src="https://seven97-blog.oss-cn-hangzhou.aliyuncs.com/imgs/202404261918506.png" alt="" loading="lazy"></p>
<ul>
<li>在 id = 20 这条记录的主键索引上，加了范围为 (15, 20] 的 next-key 锁，意味着其他事务即无法更新或者删除 id = 20 的记录，同时无法插入 id 值为 16、17、18、19 的这一些新记录。</li>
<li>在特殊记录（ supremum pseudo-record）的主键索引上，加了范围为 (20, +∞] 的 next-key 锁，意味着其他事务无法插入 id 值大于 20 的这一些新记录。</li>
</ul>
<blockquote>
<p>这里没有next-key锁都没有退化。<br>
原因在于需要保证 前后两次查询 id>15 的结果集相同。即需要保证 id>15不会出现新纪录（间隙锁），并且已存在的记录不改变（记录锁）。</p>
</blockquote>
<h4 id="针对-大于等于-的范围查询的情况">针对 大于等于 的范围查询的情况。</h4>
<p>假设事务 A 执行了这条范围查询语句：</p>
<pre><code class="language-sql"> select * from user where id >= 15 for update;
</code></pre>
<p>此时事务 A 在主键索引上加了三个 X 型 的锁，分别是：<br>
<img src="https://seven97-blog.oss-cn-hangzhou.aliyuncs.com/imgs/202404261918375.png" alt="" loading="lazy"></p>
<ul>
<li>在 id = 15 这条记录的主键索引上，加了记录锁，范围是 id = 15 这一行记录；意味着其他事务无法更新或者删除 id = 15 的这一条记录；</li>
<li>在 id = 20 这条记录的主键索引上，加了 next-key 锁，范围是 (15, 20] 。意味着其他事务即无法更新或者删除 id = 20 的记录，同时无法插入 id 值为 16、17、18、19 的这一些新记录。</li>
<li>在特殊记录（ supremum pseudo-record）的主键索引上，加了 next-key 锁，范围是 (20, +∞] 。意味着其他事务无法插入 id 值大于 20 的这一些新记录。</li>
</ul>
<blockquote>
<p>这里包含了一个等值查询操作，退化为了记录锁。原因同 唯一索引等值查询</p>
</blockquote>
<h3 id="针对-小于或者小于等于-的范围查询">针对 小于或者小于等于 的范围查询</h3>
<h4 id="针对-小于小于等于-的范围查询时查询条件值的记录-不存在-表中的情况">针对 小于/小于等于 的范围查询时，查询条件值的记录 不存在 表中的情况</h4>
<p>假设事务 A 执行了这条范围查询语句，注意查询条件值的记录（id 为 6）并不存在于表中。</p>
<pre><code class="language-sql">select * from user where id < 6 for update;
</code></pre>
<p>此时，事务 A 在主键索引上加了三个 X 型的锁：<br>
<img src="https://seven97-blog.oss-cn-hangzhou.aliyuncs.com/imgs/202404261918401.png" alt="" loading="lazy"></p>
<ul>
<li>在 id = 1 这条记录的主键索引上，加了范围为 (-∞, 1] 的 next-key 锁，意味着其他事务无法更新或者删除 id = 1 的这一条记录，同时也无法插入 id 小于 1 的这一些新记录。</li>
<li>在 id = 5 这条记录的主键索引上，加了范围为 (1, 5] 的 next-key 锁，意味着其他事务无法更新或者删除 id = 5 的这一条记录，同时也无法插入 id 值为 2、3、4 的这一些新记录。</li>
<li>在 id = 10 这条记录的主键索引上，加了范围为 (5, 10) 的间隙锁，意味着其他事务无法插入 id 值为 6、7、8、9 的这一些新记录。</li>
</ul>
<blockquote>
<p>针对 <strong>小于或者小于等于</strong> 的唯一索引范围查询，如果条件值的记录不在表中，那么不管是「小于」还是「小于等于」的范围查询，扫描到终止范围查询的记录时，该记录中索引的 next-key 锁会退化成间隙锁，其他扫描的记录，则是在这些记录的索引上加 next-key 锁。</p>
</blockquote>
<h4 id="针对-小于等于-的范围查询时查询条件值的记录-存在-表中的情况">针对 小于等于 的范围查询时，查询条件值的记录 存在 表中的情况。</h4>
<p>假设事务 A 执行了这条范围查询语句，注意查询条件值的记录（id 为 5）存在于表中。</p>
<pre><code class="language-sql">select * from user where id <= 5 for update;
</code></pre>
<p>此时，事务 A 在主键索引上加了 2 个 X 型的锁：<br>
<img src="https://seven97-blog.oss-cn-hangzhou.aliyuncs.com/imgs/202404261918129.png" alt="" loading="lazy"></p>
<ul>
<li>在 id = 1 这条记录的主键索引上，加了范围为 (-∞, 1] 的 next-key 锁。意味着其他事务即无法更新或者删除 id = 1 的这一条记录，同时也无法插入 id 小于 1 的这一些新记录。</li>
<li>在 id = 5 这条记录的主键索引上，加了范围为 (1, 5] 的 next-key 锁。意味着其他事务即无法更新或者删除 id = 5 的这一条记录，同时也无法插入 id 值为 2、3、4 的这一些新记录。</li>
</ul>
<blockquote>
<p>为了避免幻读，这里的锁都不会退化</p>
</blockquote>
<h4 id="针对-小于-的范围查询时查询条件值的记录-存在-表中的情况">针对 小于 的范围查询时，查询条件值的记录 存在 表中的情况。</h4>
<p>假设事务 A 执行了这条范围查询语句，注意查询条件值的记录（id 为 10）并不存在于表中。</p>
<pre><code class="language-sql">select * from user where id < 10 for update;
</code></pre>
<p>此时，事务 A 在主键索引上加了三个 X 型的锁：<br>
<img src="https://seven97-blog.oss-cn-hangzhou.aliyuncs.com/imgs/202404261918550.png" alt="" loading="lazy"></p>
<ul>
<li>在 id = 1 这条记录的主键索引上，加了范围为 (-∞, 1] 的 next-key 锁，意味着其他事务无法更新或者删除 id = 1 的这一条记录，同时也无法插入 id 小于 1 的这一些新记录。</li>
<li>在 id = 5 这条记录的主键索引上，加了范围为 (1, 5] 的 next-key 锁，意味着其他事务无法更新或者删除 id = 5 的这一条记录，同时也无法插入 id 值为 2、3、4 的这一些新记录。</li>
<li>在 id = 10 这条记录的主键索引上，加了范围为 (5, 10) 的间隙锁，意味着其他事务无法插入 id 值为 6、7、8、9 的这一些新记录。</li>
</ul>
<blockquote>
<p>这里加锁与 “针对 小于/小于等于 的范围查询时，查询条件值的记录 不存在 表中的情况” 一致</p>
</blockquote>
<h2 id="非唯一索引等值查询">非唯一索引等值查询</h2>
<p>当使用非唯一索引进行等值查询的时候，因为存在两个索引，一个是主键索引，一个是非唯一索引（二级索引），所以在加锁时，<strong>同时会对这两个索引都加锁</strong>，但是对主键索引加锁的时候，只有满足查询条件的记录才会对它们的主键索引加锁。</p>
<p>针对非唯一索引等值查询时，查询的记录存不存在，加锁的规则也会不同：</p>
<ul>
<li>当查询的记录「存在」时，由于不是唯一索引，所以肯定存在索引值相同的记录，于是非唯一索引等值查询的过程是一个扫描的过程，直到扫描到第一个不符合条件的二级索引记录就停止扫描，然后在扫描的过程中，对扫描到的二级索引记录加的是 next-key 锁，而对于第一个不符合条件的二级索引记录，该二级索引的 next-key 锁会退化成间隙锁。同时，在符合查询条件的记录的主键索引上加记录锁。</li>
<li>当查询的记录「不存在」时，扫描到第一条不符合条件的二级索引记录，该二级索引的 next-key 锁会退化成间隙锁。因为不存在满足查询条件的记录，所以不会对主键索引加锁。</li>
</ul>
<h3 id="记录不存在的情况-1">记录不存在的情况</h3>
<p>假设事务 A 对非唯一索引（age）进行了等值查询，且表中不存在 age = 25 的记录。</p>
<pre><code class="language-sql">select * from user where age = 25 for update;
</code></pre>
<p>定位到第一条不符合查询条件的二级索引记录，即扫描到 age = 39，于是该二级索引的 next-key 锁会退化成间隙锁，范围是 (22, 39)。</p>
<p>此时事务 A 在 age = 39 记录的二级索引上（INDEX_NAME: index_age ），加了范围为 (22, 39) 的 X 型间隙锁。<br>
<img src="https://seven97-blog.oss-cn-hangzhou.aliyuncs.com/imgs/202404261919384.png" alt="" loading="lazy"></p>
<p>此时，如果有其他事务插入了 age 值为 23、24、25、26、....、38 这些新记录，那么这些插入语句都会发生阻塞。</p>
<blockquote>
<p>原因在于需要避免出现幻读，所以需要加这个间隙锁</p>
</blockquote>
<p>但是对于插入 age = 39 记录的语句，在一些情况是可以成功插入的，而一些情况则无法成功插入</p>
<p>当有一个事务持有二级索引的间隙锁 (22, 39) 时，什么情况下，可以让其他事务的插入 age = 22 或者 age = 39 记录的语句成功？又是什么情况下，插入 age = 22 或者 age = 39 记录时的语句会被阻塞？</p>
<h4 id="插入-age--22的记录">插入 age = 22的记录</h4>
<ul>
<li>当其他事务插入一条 age = 22，id = 3 的记录的时候，在二级索引树上定位到插入的位置，而该位置的下一条是 id = 10、age = 22 的记录，该记录的二级索引上<strong>没有间隙锁</strong>，所以这条插入语句可以执行成功。</li>
<li>当其他事务插入一条 age = 22，id = 12 的记录的时候，在二级索引树上定位到插入的位置，而该位置的下一条是 id = 20、age = 39 的记录，正好该记录的二级索引上<strong>有间隙锁</strong>，所以这条插入语句会被阻塞，无法插入成功。</li>
</ul>
<h4 id="插入age--39的记录">插入age = 39的记录</h4>
<ul>
<li>当其他事务插入一条 age = 39，id = 3 的记录的时候，在二级索引树上定位到插入的位置，而该位置的下一条是 id = 20、age = 39 的记录，正好该记录的二级索引上<strong>有间隙锁</strong>，所以这条插入语句会被阻塞，无法插入成功。</li>
<li>当其他事务插入一条 age = 39，id = 21 的记录的时候，在二级索引树上定位到插入的位置，而该位置的下一条记录不存在，也就<strong>没有间隙锁</strong>了，所以这条插入语句可以插入成功。</li>
</ul>
<blockquote>
<p>当有一个事务持有二级索引的间隙锁 (22, 39) 时，插入 age = 22 或者 age = 39 记录的语句是否可以执行成功，关键还要考虑插入记录的主键值，因为「二级索引值（age列）+主键值（id列）」才可以确定插入的位置，确定了插入位置后，就要看插入的位置的下一条记录是否有间隙锁，如果有间隙锁，就会发生阻塞，如果没有间隙锁，则可以插入成功。</p>
</blockquote>
<h3 id="记录存在的情况-1">记录存在的情况</h3>
<p>假设事务 A 对非唯一索引（age）进行了等值查询，且表中存在 age = 22 的记录。</p>
<pre><code class="language-sql">select * from user where age = 22 for update;
</code></pre>
<ul>
<li>由于不是唯一索引，所以肯定存在值相同的记录，于是非唯一索引等值查询的过程是一个扫描的过程，最开始要找的第一行是 age = 22，于是对该二级索引记录加上范围为 (21, 22] 的 next-key 锁。同时，因为 age = 22 符合查询条件，于是对 age = 22 的记录的主键索引加上记录锁，即对 id = 10 这一行加记录锁。</li>
<li>接着继续扫描，扫描到的第二行是 age = 39，该记录是第一个不符合条件的二级索引记录，所以该二级索引的 next-key 锁会退化成间隙锁，范围是 (22, 39)。</li>
<li>停止查询。</li>
</ul>
<p>此时，事务 A 对主键索引和二级索引都加了 X 型的锁：<br>
<img src="https://seven97-blog.oss-cn-hangzhou.aliyuncs.com/imgs/202404261919021.png" alt="" loading="lazy"></p>
<ul>
<li>主键索引：
<ul>
<li>在 id = 10 这条记录的主键索引上，加了记录锁，意味着其他事务无法更新或者删除 id = 10 的这一行记录。</li>
</ul>
</li>
<li>二级索引（非唯一索引）：
<ul>
<li>在 age = 22 这条记录的二级索引上，加了范围为 (21, 22] 的 next-key 锁，意味着其他事务无法更新或者删除 age = 22 的这一些新记录，不过对于插入 age = 20 和 age = 21 新记录的语句，在一些情况是可以成功插入的，而一些情况则无法成功插入。</li>
<li>在 age = 39 这条记录的二级索引上，加了范围 (22, 39) 的间隙锁。意味着其他事务无法插入 age 值为 23、24、..... 、38 的这一些新记录。不过对于插入 age = 22 和 age = 39 记录的语句，在一些情况是可以成功插入的，而一些情况则无法成功插入。</li>
</ul>
</li>
</ul>
<h4 id="插入age--22-和-age--21的情况">插入age = 22 和 age = 21的情况</h4>
<p>在 age = 22 这条记录的二级索引上，加了范围为 (21, 22] 的 next-key 锁，意味着其他事务<strong>无法更新或者删除</strong> age = 22 的记录，针对是否可以插入 age = 21 和 age = 22 的新记录，分析如下：</p>
<ul>
<li>是否可以插入 age = 21 的新记录，还要看插入的新记录的 id 值，如果插入 age = 21 新记录的 id 值小于 5，那么就可以插入成功，因为此时插入的位置的下一条记录是 id = 5，age = 21 的记录，该记录的二级索引上没有间隙锁。如果插入 age = 21 新记录的 id 值大于 5，那么就无法插入成功，因为此时插入的位置的下一条记录是 id = 10，age = 22 的记录，该记录的二级索引上有间隙锁。</li>
<li>是否可以插入 age = 22 的新记录，还要看插入的新记录的 id 值，其他事务插入 age 值为 22 的新记录时，如果插入的新记录的 id 值小于 10，那么无法插入成功；如果插入的新记录的 id 大于 10，还要看该新记录插入的位置的下一条记录是否有间隙锁，如果没有间隙锁则可以插入成功，如果有间隙锁，则无法插入成功（这里即使由于有间隙锁，无法插入成功）。</li>
</ul>
<h4 id="插入age--39-的情况">插入age = 39 的情况</h4>
<p>在 age = 39 这条记录的二级索引上，加了范围 (22, 39) 的间隙锁。意味着其他事务<strong>无法插入</strong> age 值为 23、24、..... 、38 的这一些新记录，针对是否可以插入age = 39 的新记录，分析如下：</p>
<ul>
<li>是否可以插入 age = 39 的新记录，还要看插入的新记录的 id 值，其他事务插入 age 值为 39 的新记录时，如果插入的新记录的 id 值小于 20，那么插入语句会发生阻塞，如果插入的新记录的 id 大于 20，则可以插入成功。</li>
</ul>
<blockquote>
<p>这里加的三个锁，都是为了避免幻读现象的发生。记录锁，避免了age = 22,id = 10这条记录被更新或删除；另外两个二级索引锁，都避免了被新插入age = 22的记录</p>
</blockquote>
<h2 id="非唯一索引范围查询">非唯一索引范围查询</h2>
<p>非唯一索引范围查询，索引的 next-key lock 不会有退化为间隙锁和记录锁的情况，也就是非唯一索引进行范围查询时，对二级索引记录加锁都是加 next-key 锁。</p>
<p>事务 A 的这条范围查询语句：</p>
<pre><code class="language-sql">select * from user where age >= 22for update;
</code></pre>
<p>此时，事务 A 对主键索引和二级索引都加了 X 型的锁：<br>
<img src="https://seven97-blog.oss-cn-hangzhou.aliyuncs.com/imgs/202404261919849.png" alt="" loading="lazy"></p>
<ul>
<li>主键索引（id 列）：
<ul>
<li>在 id = 10 这条记录的主键索引上，加了记录锁，意味着其他事务无法更新或者删除 id = 10 的这一行记录。</li>
<li>在 id = 20 这条记录的主键索引上，加了记录锁，意味着其他事务无法更新或者删除 id = 20 的这一行记录。</li>
</ul>
</li>
<li>二级索引（age 列）：
<ul>
<li>在 age = 22 这条记录的二级索引上，加了范围为 (21, 22] 的 next-key 锁，意味着其他事务无法更新或者删除 age = 22 的这一些新记录，不过对于是否可以插入 age = 21 和 age = 22 的新记录，还需要看新记录的 id 值，与前面一致，不再赘述。</li>
<li>在 age = 39 这条记录的二级索引上，加了范围为 (22, 39] 的 next-key 锁，意味着其他事务无法更新或者删除 age = 39 的这一些记录，也无法插入 age 值为 23、24、25、...、38 的这一些新记录。不过对于是否可以插入age = 39 的新记录，还需要看新记录的 id 值，与前面一致，不再赘述。</li>
<li>在特殊的记录（supremum pseudo-record）的二级索引上，加了范围为 (39, +∞] 的 next-key 锁，意味着其他事务无法插入 age 值大于 39 的这些新记录。</li>
</ul>
</li>
</ul>
<blockquote>
<p>在 age >= 22 的范围查询中，明明查询 age = 22 的记录存在并且属于等值查询，为什么不会像唯一索引那样，将 age = 22 记录的二级索引上的 next-key 锁退化为记录锁？<br>
这是因为 age 字段是非唯一索引，不具有唯一性，所以如果只加记录锁（记录锁无法防止插入，只能防止删除或者修改），就会导致其他事务插入一条 age = 22 的记录，这样前后两次查询的结果集就不相同了，出现了幻读现象。</p>
</blockquote>
<h2 id="没有加索引的查询">没有加索引的查询</h2>
<p>如果锁定读查询语句，没有使用索引列作为查询条件，或者查询语句没有走索引查询，导致扫描是全表扫描。那么，<strong>每一条记录的索引上都会加 next-key 锁</strong>，这样就相当于锁住的全表，这时如果其他事务对该表进行增、删、改操作的时候，都会被阻塞。</p>
<p>不只是锁定读查询语句不加索引才会导致这种情况，<strong>update 和 delete 语句</strong>如果查询条件不加索引，那么由于扫描的方式是全表扫描，于是就会对每一条记录的索引上都会加 next-key 锁，这样就相当于锁住的全表。</p>
<p>因此，在线上在执行 update、delete、select ... for update 等具有加锁性质的语句，一定要检查语句是否走了索引，如果是全表扫描的话，会对每一个索引加 next-key 锁，相当于把整个表锁住了，这是挺严重的问题。当然，带了索引可能也是走的全表扫描，但是不带索引肯定是全表扫描。</p>
</div>
<div id="MySignature" role="contentinfo">
<p>本文来自在线网站：seven的菜鸟成长之路，作者：seven，转载请注明原文链接：www.seven97.top</p><br><br>
来源：https://www.cnblogs.com/sevencoding/p/19747436
頁:
[1]