一次诡异的死锁 —— 认识mysql间隙锁机制

末蓝、 2023-06-24 10:26 98阅读 0赞

### 文章目录 ###

*   *  情景模拟
     *  问题复现
     *  问题分析
     *  间隙锁
     *  问题解决
     *  小结

相信所有学习过计算机操作系统课程的同学对死锁都不陌生，每逢考试&面试，死锁都是作为重点考核的基础知识，“死锁的四大条件”已被无数IT人员烂熟于心，说倒背如流也不为过。不过理论归理论，实际开发过程中遇到死锁时，更让人头大的是如何定位造成死锁的原因。这里笔者分享自己一次分析死锁的过程，顺便聊聊MySQL的间隙锁机制。

## 情景模拟 ##

由于原始场景设计到航空方面的专业术语，为方便叙述这里用虚构的场景进行问题还原。  
假设现在需要开发如下的菜单配置功能：  
![功能示例][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3ZpcHNob3BfZmluX2Rldg_size_16_color_FFFFFF_t_70]  
并已经设计好了存储菜单配置的关联表menu\_option\_tbl以及表索引如下：  
![在这里插入图片描述][20191222233150875.png]  
id字段为自增主键，menu\_id与option\_id为外键，同时在menu\_id上建了普通索引。每个选项带有序号属性用于控制前端排序。

## 问题复现 ##

假设开发同学写了这么一段菜单配置保存逻辑：

delete from menu_option_tbl where menu_id=menu.id;
    insert into menu_option_tbl values (null, menu.id, option1.id, 1);
    insert into menu_option_tbl values (null, menu.id, option2.id, 2);
    insert into menu_option_tbl values (null, menu.id, option3.id, 3);

上述代码实现效果是先尝试根据菜单ID删除已有的选项（若已配置），然后用新增的代码逻辑来插入用户最终保存的结果。  
从功能效果上看，这段逻辑没有太大问题，无论之前有没有菜单配置，每次保存前都先执行数据清理，然后统一按照新增方式插入数据，不需要考虑哪些选项是新增、哪些选项被删除、哪些选项序号发生了变化，在低并发场景下这段代码几乎不会出现异常。但我们这里稍微加点限制，假设我们做的是通用门户菜单配置，同一时间并发量在30左右，这时我们再看看会发生什么。  
![在这里插入图片描述][20191222235403157.png]  
Bingo，死锁出现了。

## 问题分析 ##

要解决问题首先就要知道为什么会出现问题，上面这段简单的 删除 - 插入 代码逻辑为什么会出现死锁？  
先给出mysql中记录到的死锁信息：

> \------------------------ LATEST DETECTED DEADLOCK  
> \------------------------ 2019-12-22 23:49:37 0x2100  
> \*\*\* (1) TRANSACTION: TRANSACTION 110266, ACTIVE 0 sec inserting mysql tables in use 1, locked 1 LOCK WAIT 4 lock struct(s), heap size 1136,  
> 3 row lock(s), undo log entries 2 MySQL thread id 1399, OS thread  
> handle 12900, query id 225347 localhost ::1 root update insert into  
> menu\_option\_tbl(id, menu\_id, option\_id, seq\_no) values (null, 26, 662,  
> 2)  
> \*\*\* (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 119 page no 4 n bits 176 index menu\_id of table  
> `my_test_db`.`menu_option_tbl` trx id 110266 lock\_mode X insert  
> intention waiting Record lock, heap no 1 PHYSICAL RECORD: n\_fields 1;  
> compact format; info bits 0 0: len 8; hex 73757072656d756d; asc  
> supremum;;
> 
> \*\*\* (2) TRANSACTION: TRANSACTION 110267, ACTIVE 0 sec inserting, thread declared inside InnoDB 5000 mysql tables in use 1, locked 1 3  
> lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1  
> MySQL thread id 1400, OS thread handle 8448, query id 225348 localhost  
> ::1 root update insert into menu\_option\_tbl(id, menu\_id, option\_id,  
> seq\_no) values (null, 27, 74, 1)  
> \*\*\* (2) HOLDS THE LOCK(S): RECORD LOCKS space id 119 page no 4 n bits 176 index menu\_id of table `my_test_db`.`menu_option_tbl` trx id  
> 110267 lock\_mode X Record lock, heap no 1 PHYSICAL RECORD: n\_fields 1;  
> compact format; info bits 0 0: len 8; hex 73757072656d756d; asc  
> supremum;;
> 
> \*\*\* (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 119 page no 4 n bits 176 index menu\_id of table  
> `my_test_db`.`menu_option_tbl` trx id 110267 lock\_mode X insert  
> intention waiting Record lock, heap no 1 PHYSICAL RECORD: n\_fields 1;  
> compact format; info bits 0 0: len 8; hex 73757072656d756d; asc  
> supremum;;

仔细观察上面的信息，会发现诡异的现象：出现死锁的是两条insert语句。  
我们在学操作系统的时候知道共享锁（S锁）和排他锁（X锁）的概念，例如读者写者模型就是一个典型例子。按直觉理解，insert语句对应的是X锁，并且menu\_id也不相同，他们之间不应该存在竞争关系，达不到构成死锁的必要条件。  
但事实上死锁确实发生了，并且满足四项条件，究其原因是因为mysql引入了一种介于S锁与X锁之间的锁机制——间隙锁（Gap lock）。

## 间隙锁 ##

间隙锁是加在索引键空隙上的锁，与行锁相互补充。例如数据库当前有id为1，3，5的三条记录，那么行锁将锁定这三条具体的记录，控制同时只允许一个事务进行数据操作；而间隙锁可以锁定表中尚不存在的那些id区间，如\[-∞,1)，(1,3)，(3,5)，(5,+∞\]。  
间隙锁设计的目的是解决幻读的问题，例如事务一执行 `SELECT c1 FROM t WHERE c1 BETWEEN 10 and 20 FOR UPDATE`，将会在C1字段10和20之间的区间加上间隙锁，无论当前是否有对应的记录值，若此时事务二想新增一条C1=15的记录，将会被间隙锁拦截，从而保证事务一不出现幻读。  
不过间隙锁有一个特殊的属性，数据库会自动判断事务是否需要添加间隙锁，并且不同事务间的间隙锁并不互斥，意味着事务一和事务二可以同时对任意区间添加间隙锁，无论锁定的区间是否重叠。

回过头来看模拟场景，为什么间隙锁会对上述逻辑造成影响，明明代码逻辑中并没有范围条件？问题关键在于第一条delete语句。  
按照代码逻辑，无论配置表中是否存在menu\_id对应的记录，都会执行delete操作，不过记录存在与否将导致mysql采取不同的加锁策略：

1.  若menu\_option\_tbl中存在menu\_id对应记录，这时mysql将采用行锁进行并发控制。这个场景下一切都将安好，不会出现死锁。  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3ZpcHNob3BfZmluX2Rldg_size_16_color_FFFFFF_t_70 1]
2.  若menu\_option\_tbl中不存在menu\_id对应记录，此时为避免事务幻读，mysql将主动增加一条间隙锁。与预期不同的是，所添加的间隙锁范围为 (max(menu\_id), +∞\]，并非delete语句所指定的单个menu\_id。  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3ZpcHNob3BfZmluX2Rldg_size_16_color_FFFFFF_t_70 2]

在第二个场景下，并发事务执行delete将分别对(max(menu\_id), +∞\]区间加上间隙锁，由于间隙锁的特性，两个事务都将加锁成功。随后执行insert时，两个事务都必须等待对方释放间隙锁后，才能获得insert操作所需的X锁，从而出现死锁问题。

## 问题解决 ##

知道死锁的原因后，制定处理的方法就非常容易了。

1.  删除记录时使用明确的主键ID，而不是menu\_id；
2.  表中不存在menu\_id记录时，不执行delete操作，避免产生间隙锁；
3.  设置事务隔离级别为READ COMMITED，这样可以隐式禁止间隙锁，不过会带来一些副作用；
4.  在性能可以接受前提下，去除menu\_id的索引；

解释一下方法四，删除索引为什么可以解决间隙锁？因为innodb的行锁以及间隙锁都是针对索引进行操作的，所谓的加锁也只是对索引页进行控制。因此过多的索引不单会降低数据库性能，还会引入更多的死锁风险。  
最优的解决方案是方法一，通过menu\_id查询出记录主键，再根据主键进行数据操作，而不是由mysql自己判断是否需要加间隙锁。各类持久层框架默认都是使用这样的模式，不算优雅但实用。

## 小结 ##

篇幅有限，本文只是简单阐述了一次由于间隙锁所引发的死锁问题分析，更多关于mysql数据库锁机制请参考官方文档[MySQL 5.7用户手册][MySQL 5.7]。以前课文学习的理论总是剥离出了最简化的模型，而实际使用过程中总会有各类丰富的扩展实现需要自己发掘体会。学习的路还很漫长，与各位共勉。

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3ZpcHNob3BfZmluX2Rldg_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20191222223140851.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3ZpcHNob3BfZmluX2Rldg==,size_16,color_FFFFFF,t_70
[20191222233150875.png]: https://img-blog.csdnimg.cn/20191222233150875.png
[20191222235403157.png]: https://img-blog.csdnimg.cn/20191222235403157.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3ZpcHNob3BfZmluX2Rldg_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/201912230133410.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3ZpcHNob3BfZmluX2Rldg==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3ZpcHNob3BfZmluX2Rldg_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20191223013419207.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3ZpcHNob3BfZmluX2Rldg==,size_16,color_FFFFFF,t_70
[MySQL 5.7]: https://dev.mysql.com/doc/refman/5.7/en/innodb-locking.html