死锁案例七

缺乏、安全感 2022-12-12 02:11 93阅读 0赞

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

## 一、前言    ##

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

### 二、案例分析 ###

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

业务开发同学想同步数据，他们的逻辑是**通过 update 更新操作，如果更新记录返回的 affect\_rows为0，然后就调用 insert 语句进行插入初始化**。如果插入失败则再进行更新操作，多个会话并发操作的情况下就出现死锁。

#### 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 测试用例 ####

![format_png][]

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

2018-03-27 17:59:23 0x7f75bf39d700
    *** (1) TRANSACTION:
    TRANSACTION 1863, ACTIVE 76 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 1
    MySQL thread id 382150, OS thread handle 56640, query id 28 localhost root update
    insert into ty (c1,c2,c3) values(3,4,2)
    *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
    RECORD LOCKS space id 28 page no 5 n bits 72 index uc2 of table `test`.`ty` trx id 1863 lock_mode X locks gap before rec insert intention waiting
    *** (2) TRANSACTION:
    TRANSACTION 1864, ACTIVE 65 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 382125, OS thread handle 40032, query id 62 localhost root update
    insert into ty (c1,c2,c3) values(3,4,2)
    *** (2) HOLDS THE LOCK(S):
    RECORD LOCKS space id 28 page no 5 n bits 72 index uc2 of table `test`.`ty` trx id 1864 lock_mode X locks gap before rec
    *** (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 1864 lock mode S waiting
    *** WE ROLL BACK TRANSACTION (2)

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

**首先**我们要再次强调 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 操作之间不会有冲突。
> 
> GAP，Next-Key 会阻止 Insert。
> 
> GAP 和 Record，Next-Key 不会冲突。
> 
> Record 和 Record、Next-Key 之间相互冲突。
> 
> 已有的 Insert 锁不阻止任何准备加的锁。
> 
> 已经持有的 GAP 锁会阻塞插入意向锁 INSERT\_INTENTION。

**另外** 对于通过唯一索引更新或者删除不存在的记录，会申请加上 GAP 锁。

#### 分析 ####

了解上面的基础知识，我们开始对死锁日志进行分析:

T1: sess1 通过唯一键更新数据，由于 c2=4 不存在，返回 affect row 为 0，MySQL 会申请(3,6)之间的 GAP 锁。

T2: sess2 的情况和 sess1 类似，也会申请(3,6)之间的 GAP 锁，从上面的兼容性矩阵来看两个 GAP 锁并不会冲突。

T3: sess1 根据 update 语句返回 affect row 为 0，执行 insert 操作，此时需要申请插入意向锁，sess2 会话持有的 GAP 锁和 sess1 申请的插入意向锁冲突，出现等待。

> index uc2 of table test.ty trx id 1863 lock\_mode X locks gap before rec insert intention waiting

T4:sess2 与 sess1类似，根据 update 语句返回 affect row 为 0，执行 insert 操作。申请的插入意向锁与sess1 的 update 语句持有的 GAP 锁冲突。**sess1（持有 GAP 锁），sess2（持有 GAP 锁），sess1（插入意向锁等待 sess2 的 GAP 锁释放） sess2（插入意向锁等待 sess1 的 GAP 锁释放）** 构成循环等待，进而导致死锁。

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

从业务场景的处理逻辑上看，业务需要发送两次请求一次 update，一次 insert 才能完成业务逻辑，不够友好和优化。

其实我们可以和开发同学沟通好，确认业务的幂等性，使用 insert on duplicate key的方式，没有就插入，存在就更新，一次调用即可完成之前 2 次操作的功能，提高性能。

### 三、小结 ###

最后想说关于解决死锁问题的思路:

1. 具备扎实的锁相关的基础知识。

2. 单单根据死锁日志其实比较难以判断具体的 sql 执行情况，需要和开发同学沟通好，理清业务执行 sql 的逻辑，然后去模拟测试。

**扩展阅读**

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

全文完。

Enjoy MySQL :)

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

![format_png 2][]

[format_png]: /images/20221123/62a061e0c00a4aed95db729afacee323.png
[format_png 1]: /images/20221123/82ffdcb3b47b45bcbe205cb40e9e6b10.png
[Link 1]: http://mp.weixin.qq.com/s?__biz=MjM5NzAzMTY4NQ%3D%3D&chksm=bd3b49938a4cc085c3502c853206bb5cd13f9c6f498e9b8d394d2c3b9802693bf47ec8726ec5&idx=1&mid=2653934585&scene=21&sn=271f56d58d1d189e13a82a28a3980857#wechat_redirect
[Link 2]: http://mp.weixin.qq.com/s?__biz=MjM5NzAzMTY4NQ%3D%3D&chksm=bd3b49a28a4cc0b486f1d43e03d8637b3d38126204ddb4aeebea9a2080478f11a015917d8aaa&idx=1&mid=2653934536&scene=21&sn=dad17ee35e45b34049e3e4ec6dc1d015#wechat_redirect
[Link 3]: http://mp.weixin.qq.com/s?__biz=MjM5NzAzMTY4NQ%3D%3D&chksm=bd3b49018a4cc017643fed70ce3316e4dd07042fe286f51a9bbbc906aedfa2d48b50870aedb3&idx=1&mid=2653934443&scene=21&sn=b60c700cbf90d84a48b060b9ed1fcaa5#wechat_redirect
[Link 4]: http://mp.weixin.qq.com/s?__biz=MjM5NzAzMTY4NQ%3D%3D&chksm=bd3b49468a4cc050c699592a4970a33c61db386589f6199b8d9d6a0b1ac4eb3aabc5d52aa3dc&idx=1&mid=2653934380&scene=21&sn=3266e158add079c382329644e65d5318#wechat_redirect
[Link 5]: http://mp.weixin.qq.com/s?__biz=MjM5NzAzMTY4NQ%3D%3D&chksm=bd3b49628a4cc0742336c9684c704012198da94e28aff48be179b553e2557cd2a125fd13232d&idx=1&mid=2653934344&scene=21&sn=9b1a4ec019b19dd8a8367e34e3b11ee6#wechat_redirect
[Link 6]: http://mp.weixin.qq.com/s?__biz=MjM5NzAzMTY4NQ%3D%3D&chksm=bd3b48b38a4cc1a57f5e3bdbacb09c3ced1bf70484aca7de764c651e2ce40688ad7aa90704b3&idx=1&mid=2653934297&scene=21&sn=1b4415f14d1350a00cf4866817d2395e#wechat_redirect
[Link 7]: http://mp.weixin.qq.com/s?__biz=MjM5NzAzMTY4NQ%3D%3D&chksm=bd3b542f8a4cdd39f6195aceb447b7dda3c043af8a00e4048abc66f1150d122762ba93220940&idx=1&mid=2653933125&scene=21&sn=54e4c1a12223c45e4232e2227d7d19da#wechat_redirect
[Link 8]: http://mp.weixin.qq.com/s?__biz=MjM5NzAzMTY4NQ%3D%3D&chksm=bd3b48b38a4cc1a57362dba651734cd5aea259b9ebd770685f54927f7877f98f3279c5e4c072&idx=2&mid=2653934297&scene=21&sn=c5d2afc9fab595801f640ab616f03bde#wechat_redirect
[format_png 2]: /images/20221123/a0d0463f26454bd89e4f97bc491df3a4.png