MySQL 30 用动态的观点看加锁

邹家二丫头 發表於 2025-8-31 21:07:00

首先复习一下加锁规则：
<ul>
<li>
原则1：加锁的基本单位是next-key lock，是一个前开后闭区间；
</li>
<li>
原则2：查找过程中访问到的对象才会加锁；
</li>
<li>
优化1：索引上的等值查询，给唯一索引加锁的时候，next-key lock退化为行锁；
</li>
<li>
优化2：索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock退化为间隙锁；
</li>
<li>
一个bug：唯一索引上的范围查询会访问到不满足条件的第一个值为止。
</li>
</ul>
接下来的讨论基于下表t：
<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>
<h3 id="不等号条件里的等值查询">不等号条件里的等值查询</h3>
等值查询和遍历有什么区别？为什么当where条件是不等号，这个过程也有等值查询？
<pre><code class="language-sql">begin;
select * from t where id>9 and id<12 order by id desc for update;
</code></pre>
利用加锁规则，这个语句的加锁范围是主键索引上的(0,5]、(5,10]、(10,15)。<code>id=15</code>没有加上行锁是因为用到了优化2，退化为了间隙锁。
但是查询语句里where条件不是等号，这里的等值查询又是从哪来的呢？
分析索引id的示意图：
<div align="center"><img src="https://img2024.cnblogs.com/blog/3389949/202508/3389949-20250824110152602-914897780.png" width="50%"></div>
<ul>
<li>
由于语义是order by id desc，要拿到满足条件的所有行，优化器必须先找到第一个<code>id<12</code>的值；
</li>
<li>
该过程需要搜索索引树找到<code>id=12</code>的值，但最终没找到，只找到(10,15)的间隙；
</li>
<li>
然后向左遍历，该遍历过程不是等值查询，会扫描到<code>id=5</code>这一行，会加一个(0,5]。
</li>
</ul>
也就是说，在执行过程中，通过树搜索方式定位记录时用的是等值查询的方法。
<h3 id="等值查询的过程">等值查询的过程</h3>
下面这个语句的加锁范围是什么呢？
<pre><code class="language-sql">begin;
select id from t where c in(5,20,10) lock in share mode;
</code></pre>
先看语句的explain结果：
<div align="center"><img src="https://img2024.cnblogs.com/blog/3389949/202508/3389949-20250824110230129-1524498430.png" width="80%"></div>
该语句使用了索引c并且<code>rows=3</code>，说明这三个值都是通过B+树搜索定位的。
在查找<code>c=5</code>时，先锁住了(0,5]，但因为c不是唯一索引，为了确认还有没有其他<code>c=5</code>的记录，需要向右遍历，直到<code>c=10</code>才确认没有，该过程满足优化2，所以加间隙锁(5,10)。
同样的，执行<code>c=10 </code>的时候，加锁的范围是(5,10]和(10,15)；执行<code>c=20</code>的时候，加锁的范围是(15,20]和(20,25)。
这些锁是在执行过程中一个一个加的，而不是一次性加上去的。
假设同时有另外一个语句：
<pre><code class="language-sql">select id from t where c in(5,20,10) order by c desc for update;
</code></pre>
间隙锁不互锁，但这两条语句都会在索引c上的c=5、10、20三行记录上加记录锁。
由于两条语句要加锁相同的资源，但加锁顺序相反，当这两条语句并发执行的时候，就可能出现死锁。
关于死锁的信息，MySQL只保留了最后一个死锁的现场，但这个现场还是不完备的。接下来就分析上面例子的死锁现场。
<h3 id="怎么看死锁">怎么看死锁？</h3>
出现死锁后，执行<code>show engine innodb status</code>命令能输出很多信息，其中有一节LATESTDETECTED DEADLOCK，就是记录的最后一次死锁信息。
<div align="center"><img src="https://img2024.cnblogs.com/blog/3389949/202508/3389949-20250824110300206-1170692942.png" width="70%"></div>
该结果分为三部分：
<ul>
<li>
(1) TRANSACTION：是第一个事务的信息；
</li>
<li>
(2) TRANSACTION：是第二个事务的信息；
</li>
<li>
WE ROLL BACK TRANSACTION (1)：是最终处理结果，表示回滚了第一个事务。
</li>
</ul>
第一个事务的信息中：
<ul>
<li>
WAITING FOR THIS LOCK TO BE GRANTED：表示这个事务在等待的锁信息；
</li>
<li>
index c of table <code>test</code>.<code>t</code>：说明在等的是表t的索引c上面的锁；
</li>
<li>
lock mode S waiting：表示这个语句要自己加一个读锁，当前的状态是等待中；
</li>
<li>
Record lock：说明这是一个记录锁；
</li>
<li>
n_fields 2：表示这个记录是两列，也就是字段c和主键字段id；
</li>
<li>
0: len 4; hex 0000000a; asc ;;：是第一个字段c。值是十六进制a，也就是10；
</li>
<li>
1: len 4; hex 0000000a; asc ;;：是第二个字段，也就是主键id，值也是10；
</li>
<li>
这两行里面的asc表示的是，接下来要打印出值里面的“可打印字符”，但10不是可打印字符，因此就显示空格；
</li>
<li>
第一个事务信息就只显示出了等锁的状态，在等待(c=10,id=10)这一行的锁；
</li>
</ul>
第二个事务的信息中：
<ul>
<li>
“ HOLDS THE LOCK(S)”用来显示这个事务持有哪些锁；
</li>
<li>
index c of table <code>test</code>.<code>t</code> 表示锁是在表t的索引c上；
</li>
<li>
hex 0000000a和hex 00000014表示这个事务持有c=10和c=20这两个记录锁；
</li>
<li>
WAITING FOR THIS LOCK TO BE GRANTED，表示在等(c=5,id=5)这个记录锁。
</li>
</ul>
从上面这些信息中能知道：
<ul>
<li>
lock in share mode这条语句，持有<code>c=5</code>的记录锁，在等<code>c=10</code>的锁；
</li>
<li>
for update这个语句，持有<code>c=20</code>和<code>c=10</code>的记录锁，在等<code>c=5</code>的记录锁。
</li>
</ul>
因此导致死锁，由此得到结论：
<ul>
<li>
由于锁是一个个加的，要避免死锁，对同一组资源要按照尽量相同的顺序访问；
</li>
<li>
在发生死锁的时刻，for update语句占用的资源更多，回滚成本更大，因此InnoDB选择了回滚成本更小的lock in share mode语句来回滚。
</li>
</ul>
<h3 id="怎么看锁等待">怎么看锁等待？</h3>
看完死锁，再看一个锁等待的例子。
<div align="center"><img src="https://img2024.cnblogs.com/blog/3389949/202508/3389949-20250824110401936-1771224801.png" width="50%"></div>
由于session A并没有锁住<code>c=10</code>，所以session B删除这一行是可以的，但之后再想insert这一行回去就不行了。
此时执行<code>show engine innodb status</code>，锁信息是在TRANSACTIONS这一节：
<div align="center"><img src="https://img2024.cnblogs.com/blog/3389949/202508/3389949-20250824110422092-1626693298.png" width="80%"></div>
<ul>
<li>
index PRIMARY of table <code>test</code>.<code>t</code> ：表示这个语句被锁住是因为表t主键上的某个锁；
</li>
<li>
lock_mode X locks gap before rec insert intention waiting：
<ul>
<li>
insert intention：表示当前线程准备插入一个记录，这是一个插入意向锁。可以认为它就是这个插入动作本身；
</li>
<li>
gap before rec：表示这是一个间隙锁，而不是记录锁。这个gap是在哪个记录之前的呢？接下来的0~4这5行的内容就是这个记录的信息；
</li>
</ul>
</li>
<li>
n_fields 5表示这一个记录有5列：
<ul>
<li>
0: len 4; hex 0000000f; asc ;; 第一列是主键id字段，这个间隙是<code>id=15</code>之前的，因为<code>id=10</code>已经不存在了，它表示的就是(5,15)；
</li>
<li>
1: len 6; hex 000000000513; asc ;; 第二列是长度为6字节的事务id，表示最后修改这一行的是trx id为1299的事务；
</li>
<li>
2: len 7; hex b0000001250134; asc % 4;; 第三列长度为7字节的回滚段信息。acs后面有显示内容 (% 和 4)，这是因为刚好这个字节是可打印字符。后面两列是c和d的值，都是15。
</li>
</ul>
</li>
</ul>
由此可知，delete操作删除了<code>id=10</code>的行，原来的间隙(5,10)、(10,15)变成了(5,15)。
有个结论：所谓间隙，其实是由“这个间隙右边的记录”定义的。
<h3 id="update的例子">update的例子</h3>
再看一个update语句的案例：
<div align="center"><img src="https://img2024.cnblogs.com/blog/3389949/202508/3389949-20250824110505033-1964551023.png" width="50%"></div>
session A的加锁范围是索引c上的(5,10]、(10,15]、(15,20]、(20,25]、(25,supremum]。
session B第一个语句要把<code>c=5</code>改为<code>c=1</code>，可以理解为两步：
<ul>
<li>
插入(c=1,id=5)；
</li>
<li>
删除(c=5,id=5)。
</li>
</ul>
根据上面的结论，间隙是由间隙右边的记录定义，此时session A加锁范围变为：
<div align="center"><img src="https://img2024.cnblogs.com/blog/3389949/202508/3389949-20250824110534799-1099498106.png" width="40%"></div>
session B的第二个语句拆成两步：
<ul>
<li>
插入(c=5,id=5)；
</li>
<li>
删除(c=1,id=5)。
</li>
</ul>
第一步试图在间隙锁(1,10)插入数据，被堵住。 
来源：https://www.cnblogs.com/san-mu/p/19055220

頁: [1]

琼殿技术论坛's Archiver

MySQL 30 用动态的观点看加锁