死锁案例之四

左手的ㄟ右手 2022-12-04 10:58 133阅读 0赞

### 来源：公众号yangyidba ###

**一、前言**  
死锁，其实是一个很有意思也很有挑战的技术问题，大概每个DBA和部分开发同学都会在工作过程中遇见 。关于死锁我会持续写一个系列的案例分析，希望能够对想了解死锁的朋友有所帮助。本文介绍一例三个并发insert 导致的死锁，根本原因还是在于insert 唯一键申请插入意向锁这个特殊的GAP锁。其实称呼插入意向锁 为 Insert Intention Gap Lock 更为合理。

**二、案例分析**  
2.1 环境准备   
Percona server 5.6 RR模式

> 1.  CREATE TABLE \`t6\` (  
>     
> 2.    \`id\` int(11) NOT NULL AUTO\_INCREMENT,  
>     
> 3.    \`a\` int(11) DEFAULT NULL,  
>     
> 4.    PRIMARY KEY (\`id\`),  
>     
> 5.    unique KEY \`idx\_a\` (\`a\`)  
>     
> 6.  ) ENGINE=InnoDB AUTO\_INCREMENT=1 DEFAULT CHARSET=utf8mb4;  
>     
> 7.  insert into t6 values(1,2),(2,8),(3,9),(4,11),(5,19)

<table> 
 <tbody> 
 <tr> 
 <td>sess1</td> 
 <td>sess2</td> 
 <td>sess3</td> 
 </tr> 
 <tr> 
 <td>begin;</td> 
 <td> </td> 
 <td> </td> 
 </tr> 
 <tr> 
 <td>insert into t6(id,a) values(6,15);</td> 
 <td>begin;</td> 
 <td> </td> 
 </tr> 
 <tr> 
 <td> </td> 
 <td>insert into t6(id,a) values(7,15);</td> 
 <td>begin;</td> 
 </tr> 
 <tr> 
 <td> </td> 
 <td> </td> 
 <td>insert into t6(id,a) values(8,15);</td> 
 </tr> 
 <tr> 
 <td>rollback;&nbsp;</td> 
 <td> </td> 
 <td>ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction</td> 
 </tr> 
 </tbody> 
</table>

2.2 死锁日志

> 1.  \------------------------  
>     
> 2.  LATEST DETECTED DEADLOCK  
>     
> 3.  \------------------------  
>     
> 4.  2017-09-18 10:03:50 7f78eae30700  
>     
> 5.  \*\*\* (1) TRANSACTION:  
>     
> 6.  TRANSACTION 462308725, ACTIVE 18 sec inserting, thread declared inside InnoDB 1  
>     
> 7.  mysql tables in use 1, locked 1  
>     
> 8.  LOCK WAIT 4 lock struct(s), heap size 1184, 2 row lock(s), undo log entries 1  
>     
> 9.  MySQL thread id 3825465, OS thread handle 0x7f78eaef4700, query id 781148519 localhost root update  
>     
> 10. insert into t6(id,a) values(7,15)  
>     
> 11. \*\*\* (1) WAITING FOR THIS LOCK TO BE GRANTED:  
>     
> 12. RECORD LOCKS space id 227 page no 4 n bits 80 index \`idx\_a\` of table \`test\`.\`t6\` trx id 462308725 lock\_mode X insert intention waiting  
>     
> 13. \*\*\* (2) TRANSACTION:  
>     
> 14. TRANSACTION 462308726, ACTIVE 10 sec inserting, thread declared inside InnoDB 1  
>     
> 15. mysql tables in use 1, locked 1  
>     
> 16. 4 lock struct(s), heap size 1184, 2 row lock(s), undo log entries 1  
>     
> 17. MySQL thread id 3825581, OS thread handle 0x7f78eae30700, query id 781148528 localhost root update  
>     
> 18. insert into t6(id,a) values(8,15)  
>     
> 19. \*\*\* (2) HOLDS THE LOCK(S):  
>     
> 20. RECORD LOCKS space id 227 page no 4 n bits 80 index \`idx\_a\` of table \`test\`.\`t6\` trx id 462308726 lock mode S  
>     
> 21. \*\*\* (2) WAITING FOR THIS LOCK TO BE GRANTED:  
>     
> 22. RECORD LOCKS space id 227 page no 4 n bits 80 index \`idx\_a\` of table \`test\`.\`t6\` trx id 462308726 lock\_mode X insert intention waiting  
>     
> 23. \*\*\* WE ROLL BACK TRANSACTION (2)

2.3 死锁分析  
首先依然要再次强调insert 插入操作的加锁逻辑。

**第一阶段: 唯一性约束检查，先申请LOCK\_S + LOCK\_ORDINARY**  
**第二阶段:**** 获取阶段一的锁并且insert成功之后,****插入的位置有Gap锁:LOCK\_INSERT\_INTENTION,为了防止其他insert唯一键冲突。**  
**新数据插入:LOCK\_X + LOCK\_REC\_NOT\_GAP**  
**对于insert操作来说，若发生唯一约束冲突，则需要对冲突的唯一索引加上S Next-key Lock。从这里会发现，即使是RC事务隔离级别，也同样会存在Next-Key Lock锁，从而阻塞并发。然而，文档没有说明的是，对于检测到冲突的唯一索引，等待线程在获得S Lock之后，还需要对下一个记录进行加锁，在源码中由函数row\_ins\_scan\_sec\_index\_for\_duplicate进行判断.**

其次 我们需要了解锁的兼容性矩阵。  
![format_png][]  
从兼容性矩阵我们可以得到如下结论:

> 1.  INSERT操作之间不会有冲突。  
>     
> 2.  GAP,Next-Key会阻止Insert。  
>     
> 3.  GAP和Record,Next-Key不会冲突  
>     
> 4.  Record和Record、Next-Key之间相互冲突。  
>     
> 5.  已有的Insert锁不阻止任何准备加的锁。

这个案例是三个会话并发执行的，我打算一步一步来分析每个步骤执行完之后的事务日志。

**第一步、sess1 执行插入操作**

insert into t6(id,a) values(6,15);

> 1.  \---TRANSACTION 462308737, ACTIVE 5 sec  
>     
> 2.  1 lock struct(s), heap size 360, 0 row lock(s), undo log entries 1  
>     
> 3.  MySQL thread id 3825779, OS thread handle 0x7f78eacd9700, query id 781149440 localhost root init  
>     
> 4.  show engine innodb status  
>     
> 5.  TABLE LOCK table \`test\`.\`t6\` trx id 462308737 lock mode IX

因为第一个插入的语句，所以唯一性冲突检查通过，成功插入(6,15). 此时sess1 会话持有(6,15)的LOCK\_X|LOCK\_REC\_NOT\_GAP锁。参考"INSERT sets an exclusive lock on the inserted row. This lock is an index-record lock, not a next-key lock (that is, there is no gap lock) and does not prevent other sessions from inserting into the gap before the inserted row."  
  
**第二步、sess2 执行插入操作**  
insert into t6(id,a) values(7,15);

1.  \---TRANSACTION 462308738, ACTIVE 4 sec inserting  
    
2.  mysql tables in use 1, locked 1  
    
3.  LOCK WAIT 2 lock struct(s), heap size 360, 1 row lock(s), undo log entries 1  
    
4.  MySQL thread id 3825768, OS thread handle 0x7f78ea9c9700, query id 781149521 localhost root update  
    
5.  insert into t6(id,a) values(7,15)  
    
6.  \------- TRX HAS BEEN WAITING 4 SEC FOR THIS LOCK TO BE GRANTED:  
    
7.  RECORD LOCKS space id 227 page no 4 n bits 80 index \`idx\_a\` of table \`test\`.\`t6\` trx id 462308738 lock mode S waiting  
    
8.  \------------------  
    
9.  TABLE LOCK table \`test\`.\`t6\` trx id 462308738 lock mode IX  
    
10. RECORD LOCKS space id 227 page no 4 n bits 80 index \`idx\_a\` of table \`test\`.\`t6\` trx id 462308738 lock mode S waiting  
    
11. \---TRANSACTION 462308737, ACTIVE 66 sec  
    
12. 2 lock struct(s), heap size 360, 1 row lock(s), undo log entries 1  
    
13. MySQL thread id 3825779, OS thread handle 0x7f78eacd9700, query id 781149526 localhost root init  
    
14. show engine innodb status  
    
15. TABLE LOCK table \`test\`.\`t6\` trx id 462308737 lock mode IX  
    
16. RECORD LOCKS space id 227 page no 4 n bits 80 index \`idx\_a\` of table \`test\`.\`t6\` trx id 462308737 lock\_mode X locks rec but not gap

首先sess2的insert 申请了IX锁，因为sess1 会话已经插入成功并且持有唯一键 a=15的X 行锁 ，故而sess2 insert 进行唯一性检查，先申请LOCK\_S + LOCK\_ORDINARY ,事务日志列表中提示lock mode S waiting

**第三步、sess3 执行插入操作**  
insert into t6(id,a) values(8,15);

> 1.  \---TRANSACTION 462308739, ACTIVE 3 sec inserting  
>     
> 2.  mysql tables in use 1, locked 1  
>     
> 3.  LOCK WAIT 2 lock struct(s), heap size 360, 1 row lock(s), undo log entries 1  
>     
> 4.  MySQL thread id 3825764, OS thread handle 0x7f78ea593700, query id 781149555 localhost root update  
>     
> 5.  insert into t6(id,a) values(8,15)  
>     
> 6.  \------- TRX HAS BEEN WAITING 3 SEC FOR THIS LOCK TO BE GRANTED:  
>     
> 7.  RECORD LOCKS space id 227 page no 4 n bits 80 index \`idx\_a\` of table \`test\`.\`t6\` trx id 462308739 lock mode S waiting  
>     
> 8.  \------------------  
>     
> 9.  TABLE LOCK table \`test\`.\`t6\` trx id 462308739 lock mode IX  
>     
> 10. RECORD LOCKS space id 227 page no 4 n bits 80 index \`idx\_a\` of table \`test\`.\`t6\` trx id 462308739 lock mode S waiting  
>     
> 11. \---TRANSACTION 462308738, ACTIVE 35 sec inserting  
>     
> 12. mysql tables in use 1, locked 1  
>     
> 13. LOCK WAIT 2 lock struct(s), heap size 360, 1 row lock(s), undo log entries 1  
>     
> 14. MySQL thread id 3825768, OS thread handle 0x7f78ea9c9700, query id 781149521 localhost root update  
>     
> 15. insert into t6(id,a) values(7,15)  
>     
> 16. \------- TRX HAS BEEN WAITING 35 SEC FOR THIS LOCK TO BE GRANTED:  
>     
> 17. RECORD LOCKS space id 227 page no 4 n bits 80 index \`idx\_a\` of table \`test\`.\`t6\` trx id 462308738 lock mode S waiting  
>     
> 18. \------------------  
>     
> 19. TABLE LOCK table \`test\`.\`t6\` trx id 462308738 lock mode IX  
>     
> 20. RECORD LOCKS space id 227 page no 4 n bits 80 index \`idx\_a\` of table \`test\`.\`t6\` trx id 462308738 lock mode S waiting  
>     
> 21. \---TRANSACTION 462308737, ACTIVE 97 sec  
>     
> 22. 2 lock struct(s), heap size 360, 1 row lock(s), undo log entries 1  
>     
> 23. MySQL thread id 3825779, OS thread handle 0x7f78eacd9700, query id 781149560 localhost root init  
>     
> 24. show engine innodb status  
>     
> 25. TABLE LOCK table \`test\`.\`t6\` trx id 462308737 lock mode IX  
>     
> 26. RECORD LOCKS space id 227 page no 4 n bits 80 index \`idx\_a\` of table \`test\`.\`t6\` trx id 462308737 lock\_mode X locks rec but not gap

与会话sess2 的加锁申请流程一致，都在等待sess1释放锁资源。

**第四步 sess1 执行回滚操作，sess2 不提交**

sess1 rollback;  
此时sess2 插入成功，sess3出现死锁，此时sess2 insert插入成功,还未提交,事务列表如下:

> 1. \------------ 
> 
> 2. TRANSACTIONS 
> 
> 3. \------------ 
> 
> 4. Trx id counter 462308744 
> 
> 5. Purge done for trx s n:o < 462308744 undo n:o < 0 state: running but idle 
> 
> 6. History list length 1866 
> 
> 7. LIST OF TRANSACTIONS FOR EACH SESSION: 
> 
> 8. \---TRANSACTION 462308737, not started 
> 
> 9. MySQL thread id 3825779, OS thread handle 0x7f78eacd9700, query id 781149626 localhost root init 
> 
> 10. show engine innodb status 
> 
> 11. \---TRANSACTION 462308739, not started 
> 
> 12. MySQL thread id 3825764, OS thread handle 0x7f78ea593700, query id 781149555 localhost root cleaning up 
> 
> 13. \---TRANSACTION 462308738, ACTIVE 75 sec 
> 
> 14. 5 lock struct(s), heap size 1184, 3 row lock(s), undo log entries 1 
> 
> 15. MySQL thread id 3825768, OS thread handle 0x7f78eadce700, query id 781149608 localhost root cleaning up 
> 
> 16. TABLE LOCK table \`test\`.\`t6\` trx id 462308738 lock mode IX 
> 
> 17. RECORD LOCKS space id 227 page no 4 n bits 80 index \`idx\_a\` of table \`test\`.\`t6\` trx id 462308738 lock mode S 
> 
> 18. RECORD LOCKS space id 227 page no 4 n bits 80 index \`idx\_a\` of table \`test\`.\`t6\` trx id 462308738 lock mode S 
> 
> 19. RECORD LOCKS space id 227 page no 4 n bits 80 index \`idx\_a\` of table \`test\`.\`t6\` trx id 462308738 lock\_mode X insert intention 
> 
> 20. RECORD LOCKS space id 227 page no 4 n bits 80 index \`idx\_a\` of table \`test\`.\`t6\` trx id 462308738 lock mode S locks gap before rec

**三、死锁的原因**

*  sess1 insert成功并针对a=15的唯一键加上X锁。
 *  sess2 执行insert 插入(6,15), 在插入之前进行唯一性检查发现和sess1的已经插入的记录重复键需要申请LOCK\_S|LOCK\_ORDINARY, 但与sess1 的(LOCK\_X | LOCK\_REC\_NOT\_GAP)冲突,加入等待队列,等待sess1 释放锁。
 *  sess3 执行insert 插入(7,15), 在插入之前进行唯一性检查发现和sess1的已经插入的记录重复键需要申请LOCK\_S|LOCK\_ORDINARY, 但与sess1 的(LOCK\_X | LOCK\_REC\_NOT\_GAP)冲突,加入等待队列,等待sess1 释放锁。
 *  sess1 执行rollback, sess1 释放索引a=15 上的排他记录锁(LOCK\_X | LOCK\_REC\_NOT\_GAP),此后 sess2和sess3 获得S锁(LOCK\_S|LOCK\_ORDINARY)成功,sess2和sess3都要请求索引a=15上的排他记录锁(LOCK\_X | LOCK\_REC\_NOT\_GAP),日志中提示 lock\_mode X insert intention。由于X锁与S锁互斥，sess2和sess3都等待对方释放S锁，于是出现死锁，MySQL 选择回滚其中之一。

**四、总结**

死锁分析是已经很有挑战的事情，尤其对于insert 唯一键冲突,要分多个阶段去申请，也要理解锁的兼容矩阵。对于这块我还有需要在学习了解的知识点，本文算是抛砖引玉，如有分析理解不正确的地方，望大家指正。

**扩展阅读**

*  [死锁案例之三][Link 1]  
    
 *  [死锁案例之二][Link 2]  
    
 *  [死锁案例之一][Link 3]
 *  [漫谈死锁][Link 4]
 *  [如何阅读死锁日志][Link 5]

全文完。

Enjoy MySQL :)

--------------------

叶老师的「MySQL核心优化」大课已升级到MySQL 8.0，扫码开启MySQL 8.0修行之旅吧

![format_png 1][]

[format_png]: /images/20221123/75f19ead315a4ba68c25fb0b66b91ec0.png
[Link 1]: http://mp.weixin.qq.com/s?__biz=MjM5NzAzMTY4NQ%3D%3D&chksm=bd3b49468a4cc050c699592a4970a33c61db386589f6199b8d9d6a0b1ac4eb3aabc5d52aa3dc&idx=1&mid=2653934380&scene=21&sn=3266e158add079c382329644e65d5318#wechat_redirect
[Link 2]: http://mp.weixin.qq.com/s?__biz=MjM5NzAzMTY4NQ%3D%3D&chksm=bd3b49628a4cc0742336c9684c704012198da94e28aff48be179b553e2557cd2a125fd13232d&idx=1&mid=2653934344&scene=21&sn=9b1a4ec019b19dd8a8367e34e3b11ee6#wechat_redirect
[Link 3]: http://mp.weixin.qq.com/s?__biz=MjM5NzAzMTY4NQ%3D%3D&chksm=bd3b48b38a4cc1a57f5e3bdbacb09c3ced1bf70484aca7de764c651e2ce40688ad7aa90704b3&idx=1&mid=2653934297&scene=21&sn=1b4415f14d1350a00cf4866817d2395e#wechat_redirect
[Link 4]: http://mp.weixin.qq.com/s?__biz=MjM5NzAzMTY4NQ%3D%3D&chksm=bd3b542f8a4cdd39f6195aceb447b7dda3c043af8a00e4048abc66f1150d122762ba93220940&idx=1&mid=2653933125&scene=21&sn=54e4c1a12223c45e4232e2227d7d19da#wechat_redirect
[Link 5]: http://mp.weixin.qq.com/s?__biz=MjM5NzAzMTY4NQ%3D%3D&chksm=bd3b48b38a4cc1a57362dba651734cd5aea259b9ebd770685f54927f7877f98f3279c5e4c072&idx=2&mid=2653934297&scene=21&sn=c5d2afc9fab595801f640ab616f03bde#wechat_redirect
[format_png 1]: /images/20221123/3c70f1ed5cee4e62bb9f99e2767865a9.png