《MySQL实战45讲》——学习笔记21 “加锁规则、加锁案例、死锁示例“

àì夳堔傛蜴生んèń 2023-09-23 17:52 72阅读 0赞

紧接着上篇介绍可重复读隔离级别下的幻读问题及解决幻读引入的间隙锁和next-key lock的概念，本篇介绍了更新记录时加锁的规则，并用几个案例来说明规则；

通过学习本文，可以帮助通过加锁规则去判断语句的加锁范围；在业务需要使用可重复读隔离级别的时候，能够更细致地设计操作数据库的语句，解决幻读问题的同时，最大限度地提升系统并行处理事务的能力；

#### \*更新数据时的加锁规则 ####

这个规则有以下**前提**：

1. MySQL后面的版本可能会改变加锁策略，所以这个规则只限于截止到现在的最新版本，即5.x系列<=5.7.24，8.0系列<=8.0.13；

2. 因为间隙锁在可重复读隔离级别下才有效，所以本篇文章接下来的描述，若没有特殊说明，默认是可重复读隔离级别；

加锁规则里面，包含了两个“原则”、两个“优化”和一个“bug”；

> **原则1**：加锁的基本单位是next-key lock；next-key lock是前开后闭区间；  
> **原则2**：查找过程中访问到的对象才会加锁；（对象指的是索引列，也包括主键id，而非整行记录；）  
> **优化1**：索引上的等值查询，给唯一索引加锁的时候，若值存在，next-key lock退化为行锁；  
> **优化2**：索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock退化为间隙锁；  
> **一个bug**：唯一索引上的范围查询会访问到不满足条件的第一个值为止；

为方便讨论加锁规则，这里用一个简单的表结构说明（同上一篇文章），建表和初始化语句如下：

# 3个字段的简单表：主键id、索引列c、非索引列d
    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;
    
    # 表中初始化6条数据
    insert into t values
    (0,0,0),
    (5,5,5),
    (10,10,10),
    (15,15,15),
    (20,20,20),
    (25,25,25);

#### 案例一：索引上等值查询（值不存在）——退化为间隙锁 ####

![3ccd59ba69d447a99c357685bcf22f6c.png][]

**条件**：

1. id为主键，聚簇索引，且唯一索引；  
2. id=7的记录不存在；

**分析**：

1. id=7不存在，根据原则1，加锁单位是next-key lock，sessionA加锁范围就是id-(5,10\]；  
2. 同时根据优化2，这是一个等值查询(id=7)，而最后一个值id=10不满足查询条件，next-key lock退化成间隙锁，因此最终加锁的范围是id-(5,10)；  
3. 间隙锁id-(5,10)，对于id=8的插入会阻塞，对于id=10的更新不会阻塞；

**补充**：由于id天然的也是唯一索引，因此如果sessionA改为where id =5，则最终命中优化1，next-key lock退化为行锁，仅锁id=5这一列；

#### \*案例二：非唯一索引等值查询 in share mode（值存在且覆盖索引） ####

#### ![dc6175de3e9949e4b9cc4ea81bce6e0f.png][] ####

这里sessionA要给索引c上c=5的这一行加上读锁；这个例子的现象看起来，是不是有一种“**该锁的不锁，不该锁的乱锁**”的感觉？

**条件**：

1. c为二级索引，非唯一索引；  
2. c=5这条记录存在；  
3. 这条查询语句会走覆盖索引；

**分析**：

1. 根据原则1，前开后闭，会给c-(0,5\]加上next-key lock；  
2. 要注意c是普通索引非唯一索引，因此仅访问c=5这一条记录是不能马上停下来的，需要向右遍历，查到c=10才放弃；根据原则2，访问到的都要加锁，因此要给c-(5,10\]加next-key lock；  
3. 但是同时这个符合优化2：等值判断，向右遍历，最后一个值不满足c=5这个等值条件，因此退化成间隙锁c-(5,10)；因此最终加锁的范围是：间隙锁c-(0,5)、行锁c=5、间隙锁c-(5,10)；  
4. **注意**：根据原则2，只有访问到的对象才会加锁，**这个查询使用覆盖索引，并不需要访问主键索引，所以主键索引上没有加任何锁**，这就是为什么sessionB的update**更新语句可以执行完成**；但sessionC要**插入**一个(7,7,7)的记录，就会被sessionA的间隙锁c-(5,10)**锁住**；如果这里的sessionB的语句改为 update t SET d = 100 WHERE c = 5; 则会被c=5的行锁锁住，尽管这条SQL更新的记录与id=5的记录时同一条；

需要注意，在这个例子中，lock in share mode只锁覆盖索引，但是如果是for update就不一样了，sessionB也会被阻塞；因为执行for update时，系统会认为你接下来要更新数据，因此**会顺便给主键索引上满足条件的行加上行锁**；这个场景下，如果要用lock in share mode来给行加读锁避免数据被更新的话，就必须得**绕过覆盖索引的优化**，**回一下表**，**在查询字段中加入索引中不存在的字段**；比如，将sessionA的查询语句改成select d from t where c=5 lock in share mode；**这个例子说明，锁是加在索引列上的，也就是说这里的间隙锁(5,10)实际是c-(5,10)**；

####  ####

#### \*间隙锁实际上锁的是什么？ ####

另外一个需要注意的点，**判断某个值能否插入的时候，不能光看区间的两端的值是否在区间内，还需要结合索引树**；例如，对于间隙锁c-(5,10)，这里如果插入一条(9,10,11)，即c=10的记录，从区间上看，开区间(5,10)不包括10，但是依然无法插入；因为(9,10,11)这条记录，从c索引树上看，(c=10,id=9)会插在(c=10,id=10)的左边，因此会被c-(5,10)间隙锁锁住；而如果插入(11,10,11)这条记录，可以成功，因为从c索引树上看，(c=10,id=11)会插在(c=10,id=10)的右边，不会被c-(5,10)间隙锁锁住；

#### 案例三：主键索引范围查询 ####

先看2条SQL语句，他们的查询逻辑等价，但是加锁情况**不一样**；

(1) select * from t where id=10 for update;
    (2) select * from t where id>=10 and id<11 for update;

现在让sessionA执行第2个查询语句，来看看加锁效果；

![ce0878ecebef4c73aa9c54c884eaeb2e.png][]

**条件**：

1. 查询条件使用id，id是主键索引，且为唯一索引；  
2. 执行id>=10条件时，会执行id=10的等值判断，且为访问到的第一条数据；  
3. 会执行id上的范围查询；

**分析**：

1. 先执行id=10的等值查询，找到的第一个行id=10，因此next-key lock(5,10\]；根据优化1，主键（唯一索引）id上的等值条件，退化成行锁，只加了id=10这一行的行锁；  
2. 接下来通过范围查找，根据bug1，唯一索引的范围查询会找到第一条不满足条件的行id=15，因此需要加next-key lock(10,15\]；因此最终加锁的范围是：行锁id=10和next-key lock id-(10,15\]；而next-key lock id-(10,15\]包含间隙锁id-(10,15)和行锁id=15；

因此，插入id=13和更新id=15，都会被阻塞；

这里需要注意一点，首次sessionA定位查找id>=10的行的时候，是先当做id=10的等值查询来判断的，而向右扫描到id=15的时候，用的是范围查询判断；

#### 案例四：非唯一索引范围查询 ####

下面的案例与案例三对比，查询条件有唯一索引id换成了普通二级索引c；

![4507b1100f4045ec9fa42ea13bac8405.png][]

**条件**：

1. c是非唯一索引；  
2. 使用了c的范围查询；  
3. 使用了for update；  
4. 使用select \* ，因此查询会回表；

**分析**：

1. 由于c是非唯一索引，并且这里使用了范围查询，因此优化1和优化2在这里都用不上；先查c=10，再往后找不满足条件的c=15的记录，即一共扫描了2条记录，产生的next-key lock分别是c-(5,10\]和c-(10,15\]；  
2. 由于回表，因此主键id也有间隙锁；  
3. 使用了for update，因此扫描到的记录都会加上行锁；

因此，插入(8,8,8)被间隙锁阻塞，更新c=15的记录被next-key lock c-(10,15\]中的c=15行锁阻塞；

#### 案例五：唯一索引范围锁的"bug" ####

接下来再看一个关于加锁规则中bug的案例；

![847599c5228f47bd94bc0a8bc4c3cbe7.png][]

**条件**：

1. id是主键，也是唯一索引；  
2. 使用了id的范围查询；  
3. 使用了for update；

**分析**：

1. 由于id是唯一索引，按照条件查询，线找到id=15这条记录，对应next-key lock id-(10,15\]；  
2. 根据bug1，唯一索引的范围查询会找到下一个不满足条件的记录，因此还会往后找到id=20这条记录，对应next-key lock id-(15,20\]；

因此，插入(16,16,16)被next-key lock id-(15,20\]中的间隙锁(15,20)阻塞，更新id=20的记录被next-key lock id-(15,20\]中的行锁id=20阻塞；

**为什么认为是bug？**

作为唯一索引，对于id<=15这个条件，其实已经找到了"唯一的一条"满足条件的记录，但是MySQL还是往后找到了"肯定不满足条件"的id=20这一行记录；

当前环境为MySQL 5.7.X，据说MySQL8.0.21版本已经修复；

#### 案例六：limit 语句加锁 ####

先给表中在插入一条id=10的记录，c索引结构如下；

insert into t values(30,10,30);

![9e8e31c93d06479b8a438aff54c3ff9a.png][]

下面对delete语句是否使用limit语句做一个对比；delete和for update的加锁逻辑类似，如果是走非主键索引的话，除了给当前索引加锁，还会顺便给主键索引加锁；

case（1）不使用limit语句的场景

![8a7ead5e474149a49ef343d95bfd00e8.png][]

分析：非唯一索引的等值查询，会找到满足c=10的两条记录(10,10,10)和(30,10,30)，对应next-key lock c-(5,10\]；接下来要继续沿着索引c往下找，找到第一条不满足条件的记录(15,15,15)，对应next-key lock c-(10,15\]；根据优化2，非唯一索引等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock退化为间隙锁c-(10,15)；由于使用for update，因此主键id也会加锁；

因此，sessionB被间隙锁c-(10,15)阻塞，sessionC更新的id=15不在锁范围，可以正常执行；

case（2）使用limit语句的场景

![09b22cf08f764c1ba01efcd983721cc9.png][]

这个例子里，sessionA的delete语句加了limit 2；表t里c=10的记录其实只有两条，因此加不加limit 2，删除的效果都是一样的，但是加锁的效果却不同；可以看到，sessionB的insert语句执行通过了，跟case（1）的结果不同；

这是因为，**case（2）的delete语句明确加了limit 2的限制，因此在遍历到(c=10,id=30)这一行之后，满足条件的语句已经有两条，循环就结束了**；**不会再往下去找id=15这条记录**；

因此，索引c上的加锁范围就变成了从(c=5,id=5)到(c=10,id=30)这个前开后闭区间，精确地间隙锁区间如下图所示：

![97945f3df3664c6d8e8de197b2621cac.png][]

可以看到，(c=10,id=30）之后的这个间隙并没有在加锁范围里，因此insert语句插入c=12是可以执行成功的；

> 这个例子对我们实践的指导意义就是，**在删除数据的时候尽量加limit**：**这样不仅可以控制删除数据的条数，让操作更安全，还可以减小加锁的范围**；

#### 案例七：一个死锁的例子——加 next-key lock是分2步的 ####

前面的例子中，在分析的时候，是按照next-key lock的逻辑来分析的，因为这样分析比较方便；最后再看一个案例，目的是说明：next-key lock实际上是间隙锁和行锁加起来的结果，是分别加上的；

![f3471bf9d5144404a1603b59a5363bb5.png][]

过程分析：

1. sessionA启动事务后执行查询语句加lock in share mode，在索引c上加了next-key lock c-(5,10\]和间隙锁(10,15)；  
2. 按照规则和优化，sessionB的update语句也要在索引c上加next-key lock c-(5,10\]和间隙锁(10,15)，**但是加锁过程中进入锁等待**；  
3. 然后sessionA要再插入(8,8,8)这一行，被sessionB的间隙锁锁住；由于出现了死锁，InnoDB让sessionB回滚；

可能会有疑问，**间隙锁加锁不是不会互相阻塞吗，阻塞的其实是插入操作？**

确实如此，但是这里第2步，sessionB的"加next-key lock c-(5,10\]"操作，**实际上分成了两步，先是加(5,10)的间隙锁，****加锁成功****；然后加c=10的行锁，这时候才被锁住的**；后面c-(10，15\]的next-key lock还没来得及加，就被前面的id=10的行锁给锁住了；

> 也就是说，我们在分析加锁规则的时候可以用next-key lock来分析；但是要知道，具体执行的时候，是要分成间隙锁和行锁两段来执行的；

#### 小结 ####

1. 更新时的加锁规则及优化/BUG

> **原则1**：加锁的基本单位是next-key lock；next-key lock是前开后闭区间；找到一条记录，再确定它的next-key lock区间；  
> **原则2**：查找过程中访问到的对象才会加锁；如果是覆盖索引，则不会对主键加锁；  
> **优化1**：索引上的等值查询，给唯一索引加锁的时候，**若值存在**，next-key lock退化为行锁；  
> **优化2**：索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock退化为间隙锁；  
> **一个bug**：唯一索引上的范围查询会访问到不满足条件的第一个值为止；也就是说哪怕类似>=的条件找到了这条=的记录，还会接着往下找；据说MySQL8.0.21版本已经修复；

2. 查询回表，例如条件虽然是二级索引，但 select \* 需要回表，则主键id也会加锁；

3. 因为执行for update时，系统会认为你接下来要更新数据，因此会顺便给主键索引上满足条件的行加上行锁；

4. 间隙锁锁的是插入行为，不锁更新，除非还有行锁；

5. 在分析加锁规则的时候可以用next-key lock来分析；但是next-key lock加锁实际上分成了两步，例如next-key lock c-(5,10\]，先是加的间隙锁c-(5,10)，加锁成功；然后加c=10的行锁；加间隙锁不会阻塞，但是加行锁可能阻塞；

6. 删除语句建议加上limit；delete和for update的加锁逻辑类似，明确加了limit N的限制，则仅扫描到指定数量N的记录，不会再接着往下找下一条不满足条件的记录，因此会减小锁的范围；

7. order by desc/asc 会影响查找的顺序，默认是顺序按照索引值从小到大查找，如果使用desc，则是从大往小找，加锁的范围是不同的；

#### 补充：复现文中加锁场景的方法 ####

使用的是HeidiSQL这款client工具，也可以使用MySQL workbench，刚开始尝试执行SQL时发现未提交的事务，另一个查询窗口可以查到；去搜索了下原因，是因为当前是一个连接，所以能读到当前事务未提交的更新；

**正确的测试方法，是开多个客户端而不是一个客户端开多个查询窗口**；执行语句时，逐行执行，即选择"执行选中的SQL语句"，可以观察SQL执行日志来观察执行情况；

![ba4240a270f5404394aea7d72b003733.png][]

看本篇之前建议先看下上一篇文章：[《MySQL实战45讲》——学习笔记20 “幻读、全表扫描的加锁方式、间隙锁、next-key lock“][MySQL_45_20 _next-key lock]

下篇文章：[学习笔记22 “大量短连接/慢查询/QPS突增的临时处理方案“][22 _QPS]

本章参考：[21 | 为什么我只改一行的语句，锁这么多？-极客时间][21 _ _-]

[3ccd59ba69d447a99c357685bcf22f6c.png]: https://img-blog.csdnimg.cn/3ccd59ba69d447a99c357685bcf22f6c.png
[dc6175de3e9949e4b9cc4ea81bce6e0f.png]: https://img-blog.csdnimg.cn/dc6175de3e9949e4b9cc4ea81bce6e0f.png
[ce0878ecebef4c73aa9c54c884eaeb2e.png]: https://img-blog.csdnimg.cn/ce0878ecebef4c73aa9c54c884eaeb2e.png
[4507b1100f4045ec9fa42ea13bac8405.png]: https://img-blog.csdnimg.cn/4507b1100f4045ec9fa42ea13bac8405.png
[847599c5228f47bd94bc0a8bc4c3cbe7.png]: https://img-blog.csdnimg.cn/847599c5228f47bd94bc0a8bc4c3cbe7.png
[9e8e31c93d06479b8a438aff54c3ff9a.png]: https://img-blog.csdnimg.cn/9e8e31c93d06479b8a438aff54c3ff9a.png
[8a7ead5e474149a49ef343d95bfd00e8.png]: https://img-blog.csdnimg.cn/8a7ead5e474149a49ef343d95bfd00e8.png
[09b22cf08f764c1ba01efcd983721cc9.png]: https://img-blog.csdnimg.cn/09b22cf08f764c1ba01efcd983721cc9.png
[97945f3df3664c6d8e8de197b2621cac.png]: https://img-blog.csdnimg.cn/97945f3df3664c6d8e8de197b2621cac.png
[f3471bf9d5144404a1603b59a5363bb5.png]: https://img-blog.csdnimg.cn/f3471bf9d5144404a1603b59a5363bb5.png
[ba4240a270f5404394aea7d72b003733.png]: https://img-blog.csdnimg.cn/ba4240a270f5404394aea7d72b003733.png
[MySQL_45_20 _next-key lock]: https://blog.csdn.net/minghao0508/article/details/128179144
[22 _QPS]: https://blog.csdn.net/minghao0508/article/details/128239185
[21 _ _-]: https://time.geekbang.org/column/article/75659