死锁案例七

「爱情、让人受尽委屈。」 2022-12-13 01:29 168阅读 0赞

## 一、前言 ##

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

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

2.1 业务场景

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

2.2 环境说明

MySQL 5.6.24 事务隔离级别为 RR

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

2.3 测试用例

![93f5126ddb515199b1f45dbb259f5d6f.png][]

2.4 死锁日志

1.  `2018-03-27 17:59:23 0x7f75bf39d700`
2.  `*** (1) TRANSACTION:`
3.  `TRANSACTION 1863, ACTIVE 76 sec inserting`
4.  `mysql tables in use 1, locked 1`
5.  `LOCK WAIT 4 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 1`
6.  `MySQL thread id 382150, OS thread handle 56640, query id 28 localhost root update`
7.  `insert into ty (c1,c2,c3) values(3,4,2)`
8.  `*** (1) WAITING FOR THIS LOCK TO BE GRANTED:`
9.  ``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``
10. `*** (2) TRANSACTION:`
11. `TRANSACTION 1864, ACTIVE 65 sec inserting, thread declared inside InnoDB 5000`
12. `mysql tables in use 1, locked 1`
13. `3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1`
14. `MySQL thread id 382125, OS thread handle 40032, query id 62 localhost root update`
15. `insert into ty (c1,c2,c3) values(3,4,2)`
16. `*** (2) HOLDS THE LOCK(S):`
17. ``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``
18. `*** (2) WAITING FOR THIS LOCK TO BE GRANTED:`
19. ``RECORD LOCKS space id 28 page no 4 n bits 72 index uc1 of table `test`.`ty` trx id 1864 lock mode S waiting``
20. `*** 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 进行判断.

**其次** 我们需要了解锁的兼容性矩阵。

![910d0db670ded08a517fe0217bf8ab94.png][]

从兼容性矩阵我们可以得到如下结论:

> 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 的逻辑，然后去模拟测试。

[93f5126ddb515199b1f45dbb259f5d6f.png]: /images/20221123/e991c6ad4fe74f3bbf004e33ab21659c.png
[910d0db670ded08a517fe0217bf8ab94.png]: /images/20221123/615e7992abcd43a4979768cf5d4fac8c.png