死锁案例之八

Dear 丶 2022-12-17 08:41 143阅读 0赞

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

### 一 前言 ###

死锁其实是一个很有意思也很有挑战的技术问题，大概每个DBA和部分开发朋友都会在工作过程中遇见。关于死锁我会持续写一个系列的案例分析，希望能够对想了解死锁的朋友有所帮助。

### 二 案例分析 ###

#### 2.1 业务场景 ####

业务上的主要逻辑:

> 首先执行插入数据，如果插入成功，则提交。如果插入的时候报唯一键冲突，则执行更新。如果同时出现三个并发在执行数据初始化动作，sess1 插入成功，sess2 和 sess3插入遇到唯一键冲突，插入失败，则都执行执行更新，于是出现死锁。

#### 2.2 环境准备 ####

MySQL 5.6.24 事务隔离级别为RR

create table ty (
      id int not null primary key auto_increment ,
      c1 int not null default 0,
      c2 int not null default 0,
      c3 int not null default 0,
      unique key uc1(c1),
      unique key uc2(c2)
    ) engine=innodb ;
    
    insert into ty(c1,c2,c3) values(1,3,4),(6,6,10),(9,9,14);

#### 2.3 测试用例 ####

为了方便分析死锁日志，三个会话插入的c3的值分别为1 2 3 ，生产上其实是相同的值。

<table> 
 <tbody> 
 <tr> 
 <td> </td> 
 <td>sess1</td> 
 <td>sess2</td> 
 <td>sess3</td> 
 </tr> 
 <tr> 
 <td> </td> 
 <td>begin;</td> 
 <td>begin;</td> 
 <td>begin;</td> 
 </tr> 
 <tr> 
 <td>T1</td> 
 <td>insert into ty (c1,c2,c3) values(4,4,1);</td> 
 <td> </td> 
 <td> </td> 
 </tr> 
 <tr> 
 <td>T2</td> 
 <td> </td> 
 <td>insert into ty (c1,c2,c3) values(4,4,2);</td> 
 <td> </td> 
 </tr> 
 <tr> 
 <td>T3</td> 
 <td> </td> 
 <td> </td> 
 <td>insert into ty (c1,c2,c3) values(4,4,3);</td> 
 </tr> 
 <tr> 
 <td>T4</td> 
 <td>commit</td> 
 <td> </td> 
 <td> </td> 
 </tr> 
 <tr> 
 <td>T5</td> 
 <td> </td> 
 <td>update ty set c3=5 where c1=4;</td> 
 <td> </td> 
 </tr> 
 <tr> 
 <td>T6</td> 
 <td> </td> 
 <td> </td> 
 <td>update ty set c3=5 where c1=4;ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction</td> 
 </tr> 
 </tbody> 
</table>

#### 2.4 死锁日志 ####

2018-03-28 10:04:52 0x7f75bf2d9700
    *** (1) TRANSACTION:
    TRANSACTION 1870, ACTIVE 76 sec starting index read
    mysql tables in use 1, locked 1
    LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s)
    MySQL thread id 399265, OS thread handle 12, query id 9 root updating
    update ty set c3=5 where c1=4
    *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
    RECORD LOCKS space id 28 page no 4 n bits 72 index uc1 of table 
    `test`.`ty` trx id 1870 lock_mode X locks rec but not gap waiting
    *** (2) TRANSACTION:
    TRANSACTION 1871, ACTIVE 32 sec starting index read, 
    thread declared inside InnoDB 5000
    mysql tables in use 1, locked 1
    3 lock struct(s), heap size 1136, 2 row lock(s)
    MySQL thread id 399937, OS thread handle 16, query id 3 root updating
    update ty set c3=5 where c1=4
    *** (2) HOLDS THE LOCK(S):
    RECORD LOCKS space id 28 page no 4 n bits 72 index uc1 of table 
    `test`.`ty` trx id 1871 lock mode S
    *** (2) WAITING FOR THIS LOCK TO BE GRANTED:
    RECORD LOCKS space id 28 page no 4 n bits 72 index uc1 of table 
    `test`.`ty` trx id 1871 lock_mode X locks rec but not gap waiting
    *** WE ROLL BACK TRANSACTION (2)

**其实单单从日志上查看只看到两个事务的update相互竞争，在缺乏业务逻辑场景的情况下，因为不知道还有insert操作，很难得到分析死锁的有效思路。**

#### 2.5 分析死锁日志 ####

T1 s1 执行insert操作，检查唯一性且插入成功,持有c1=4记录行的行锁。

T2 s2 insert遇到唯一键冲突，申请加锁Lock S Next-key Lock 日志显示为**index uc1 of table test.ty trx id 1870 lock mode S waiting**

![format_png][]

T3 与s2相同,s3 insert遇到唯一键冲突，申请加锁Lock S Next-key Lock 日志显示为**index uc1 of table test.ty trx id 1870 lock mode S waiting**

![format_png 1][]

T4 sess1 执行commit操作, 此时sess2 和sess3 同时获取Lock S Next-key Lock。

T5 应用收到唯一键冲突，sess2执行update 操作需要申请c=4的行锁，与sess3的持有的Lock S Next-key Lock不兼容，等待sess3释放Lock S Next-key Lock。

![format_png 2][]

T6 与sess2 类似 sess3执行update 操作需要申请c=4的行锁，与sess2的持有的Lock S Next-key Lock不兼容，等待sess2释放Lock S Next-key Lock。出现循环等待，发生死锁。

#### 2.6 解决方法 ####

本案例的解决方式其实和前文 [死锁案例之七 ][Link 1]一致，使用insert on duplicate key。案例七与本文导致死锁的业务逻辑极为相似，为什么呢？因为都是同一组开发哥哥写的。

![format_png 3][]

### 三 小结 ###

**导致死锁的根本原因是不同事务申请锁的顺序不一样出现循环等待，开发同学在设计高并发的业务场景时**，需要着重思考这一点，并且尽量规避业务场景设计不合理导致死锁。

另外就是insert 的加锁机制相对update其实比较复杂，需要多动手实践，理清加锁流程。

扫码关注作者微信公众号

![format_png 4][]

**扩展阅读**

*  [死锁案例之七][Link 1]
 *  [死锁案例之六][Link 2]
 *  [死锁案例之五][Link 3]
 *  [死锁案例之四][Link 4]  
    
 *  [死锁案例之三][Link 5]  
    
 *  [死锁案例之二][Link 6]  
    
 *  [死锁案例之一][Link 7]
 *  [漫谈死锁][Link 8]
 *  [如何阅读死锁日志][Link 9]

全文完。

Enjoy MySQL :)

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

![format_png 5][]

[format_png]: /images/20221122/bd91d4dd4a1f4e9cbc9729e76233efc7.png
[format_png 1]: /images/20221122/2badbc7fd5b44cc0bb62639837c52010.png
[format_png 2]: /images/20221122/bc57fe21227d4be0a308e1dac81d380b.png
[Link 1]: http://mp.weixin.qq.com/s?__biz=MjM5NzAzMTY4NQ%3D%3D&chksm=bd3b4e738a4cc7659668d864a5148a116fb4bf7ffa9a2da620d21600d86a814fe359ce1bcd91&idx=1&mid=2653934617&scene=21&sn=e5c538301b81b814f8e9362118454d5e#wechat_redirect
[format_png 3]: /images/20221122/04f8b5bba0c44508bb4a0a35cc0ca649.png
[format_png 4]: /images/20221122/7cf346bd333a441da4e0a0270fc05af1.png
[Link 2]: http://mp.weixin.qq.com/s?__biz=MjM5NzAzMTY4NQ%3D%3D&chksm=bd3b49938a4cc085c3502c853206bb5cd13f9c6f498e9b8d394d2c3b9802693bf47ec8726ec5&idx=1&mid=2653934585&scene=21&sn=271f56d58d1d189e13a82a28a3980857#wechat_redirect
[Link 3]: http://mp.weixin.qq.com/s?__biz=MjM5NzAzMTY4NQ%3D%3D&chksm=bd3b49a28a4cc0b486f1d43e03d8637b3d38126204ddb4aeebea9a2080478f11a015917d8aaa&idx=1&mid=2653934536&scene=21&sn=dad17ee35e45b34049e3e4ec6dc1d015#wechat_redirect
[Link 4]: http://mp.weixin.qq.com/s?__biz=MjM5NzAzMTY4NQ%3D%3D&chksm=bd3b49018a4cc017643fed70ce3316e4dd07042fe286f51a9bbbc906aedfa2d48b50870aedb3&idx=1&mid=2653934443&scene=21&sn=b60c700cbf90d84a48b060b9ed1fcaa5#wechat_redirect
[Link 5]: http://mp.weixin.qq.com/s?__biz=MjM5NzAzMTY4NQ%3D%3D&chksm=bd3b49468a4cc050c699592a4970a33c61db386589f6199b8d9d6a0b1ac4eb3aabc5d52aa3dc&idx=1&mid=2653934380&scene=21&sn=3266e158add079c382329644e65d5318#wechat_redirect
[Link 6]: http://mp.weixin.qq.com/s?__biz=MjM5NzAzMTY4NQ%3D%3D&chksm=bd3b49628a4cc0742336c9684c704012198da94e28aff48be179b553e2557cd2a125fd13232d&idx=1&mid=2653934344&scene=21&sn=9b1a4ec019b19dd8a8367e34e3b11ee6#wechat_redirect
[Link 7]: http://mp.weixin.qq.com/s?__biz=MjM5NzAzMTY4NQ%3D%3D&chksm=bd3b48b38a4cc1a57f5e3bdbacb09c3ced1bf70484aca7de764c651e2ce40688ad7aa90704b3&idx=1&mid=2653934297&scene=21&sn=1b4415f14d1350a00cf4866817d2395e#wechat_redirect
[Link 8]: http://mp.weixin.qq.com/s?__biz=MjM5NzAzMTY4NQ%3D%3D&chksm=bd3b542f8a4cdd39f6195aceb447b7dda3c043af8a00e4048abc66f1150d122762ba93220940&idx=1&mid=2653933125&scene=21&sn=54e4c1a12223c45e4232e2227d7d19da#wechat_redirect
[Link 9]: http://mp.weixin.qq.com/s?__biz=MjM5NzAzMTY4NQ%3D%3D&chksm=bd3b48b38a4cc1a57362dba651734cd5aea259b9ebd770685f54927f7877f98f3279c5e4c072&idx=2&mid=2653934297&scene=21&sn=c5d2afc9fab595801f640ab616f03bde#wechat_redirect
[format_png 5]: /images/20221122/9117fc577d1b47c9b2e356c1636db55f.png