死锁案例之九

曾经终败给现在 2022-11-21 03:51 188阅读 0赞

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

### 一 前言    ###

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

### 二 案例分析 ###

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

业务开发同学要初始化数据，他们的逻辑是**批量执行insert values(x,x,x),(x,x,x); 该表有唯一键，批量插入的唯一键相邻，出现大量死锁**。

#### 2.2 环境说明 ####

MySQL 5.6.24 事务隔离级别为RR

CREATE TABLE `tc` (
      `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增ID',
      `c1` bigint(20) unsigned NOT NULL DEFAULT '0',
      `c2` bigint(20) unsigned NOT NULL DEFAULT '0',
      `c3` bigint(20) unsigned NOT NULL DEFAULT '0',
      `c4` tinyint(4) NOT NULL DEFAULT '0',
      `c5` tinyint(4) NOT NULL DEFAULT '0',
      `created_at` datetime NOT NULL DEFAULT '1970-01-01 08:00:00',
      `deleted_at` datetime NOT NULL DEFAULT '1970-01-01 08:00:00',
      PRIMARY KEY (`id`),
      UNIQUE KEY `uniq_cid_bid_dt_tid` (`c1`,`c2`,`deleted_at`,`c3`)
    ) ENGINE=InnoDB AUTO_INCREMENT=19 DEFAULT CHARSET=utf8mb4

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

![format_png][]

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

2018-04-01 21:41:34 0x7f75c8bff700
    *** (1) TRANSACTION:
    TRANSACTION 2004, ACTIVE 6 sec inserting
    mysql tables in use 1, locked 1
    LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 2
    MySQL thread id 517219, OS thread handle 40, query id 79 127.0.0.1 root update
    INSERT IGNORE INTO tc (c2, c1, c3, created_at, c4, c5) VALUES
    (95529, 4083702165, 3549685, now(), 1, 4),
    (95529, 4083702165, 3549694, now(), 1, 4)
    *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
    RECORD LOCKS space id 29 page no 4 n bits 72 index uniq_cid_bid_dt_tid of table `test`.`tc` trx id 2004 lock mode S waiting
    *** (2) TRANSACTION:
    TRANSACTION 1999, ACTIVE 16 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 4
    MySQL thread id 517587, OS thread handle 92, query id 84 127.0.0.1 root update
    INSERT IGNORE INTO tc (c2, c1, c3, created_at, c4, c5) VALUES
    (95529, 4083702165, 3549691, now(), 1, 5)
    *** (2) HOLDS THE LOCK(S):
    RECORD LOCKS space id 29 page no 4 n bits 72 index uniq_cid_bid_dt_tid of table `test`.`tc` trx id 1999 lock_mode X locks rec but not gap
    *** (2) WAITING FOR THIS LOCK TO BE GRANTED:
    RECORD LOCKS space id 29 page no 4 n bits 72 index uniq_cid_bid_dt_tid of table `test`.`tc` trx id 1999 lock_mode X locks gap before rec insert intention waiting
    *** WE ROLL BACK TRANSACTION (1)

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

**首先**我们要再次强调insert 插入操作的加锁逻辑。

第一阶段: 唯一性约束检查，先申请**LOCK\_S + LOCK\_ORDINARY**

第二阶段: 获取阶段一的锁并且insert成功之后,插入的位置有Gap锁:**LOCK\_INSERT\_INTENTION**,为了防止其他insert唯一键冲突。

新数据插入完成之后:LOCK\_X + LOCK\_REC\_NOT\_GAP

对于insert操作来说，若发生唯一约束冲突，需要对冲突的唯一索引申请加上S Next-key Lock。如果其他会话中包含已经插入记录的事务没有提交，则申请加锁出现等待，show engine innodb status中的事务列表中会提示 **lock mode S waiting** 。

从这里会发现，即使是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: sess2 执行批量insert 4条记录，先插入的记录构成唯一键(95529, 4083702165,now(),3549694),该记录在插入完成之后获取到的锁:LOCK\_X + LOCK\_REC\_NOT\_GAP。

T2: sess1 insert两条记录 (95529, 4083702165, 3549694, now(), 1, 4),和sess2中的唯一键冲突，于是申请S Next-key Lock,但是和sess2 的LOCK\_REC\_NOT\_GAP 冲突(共享锁和已经持有的排他锁冲突),系统提示**RECORD LOCKS space id 29 page no 4 n bits 72 index uniq*cid*bid*dt*tid of table test.tc trx id 2004 lock mode S waiting**

T3: sess2 insert 记录(95529, 4083702165, 3549691, now(), 1, 5),会申请锁LOCK\_INSERT\_INTENTION，**其中3549691与sess1 中的3549694相邻,sess1申请S Next-key Lock会阻塞记录3549691插入。**

**T1时刻sess2(持有LOCK\_REC\_NOT\_GAP)，T2时刻 sess1(申请S Next-key Lock）被sess2 阻塞，T3时刻sess2(插入意向锁等待sess1的gap锁释放)** 构成循环等待，进而导致死锁。

> 注意,这里对insert 唯一键的加锁逻辑自己可能表述不准确，望读者朋友多讨论。

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

其实针对此类并发insert导致的死锁，并没有好的解决方法，至少在sql层面没有行之有效的方法。之前的还可以调整sql的执行顺序，简化业务sql逻辑。但是对于此类情况 **只能调整唯一索引，或者尽量将初始化的数据打散**，调整唯一索引要调整整体的业务层面的逻辑了，需要开发深度介入。

### 三 小结 ###

本案例的死锁要素是 1 并发insert 2 并发插入的记录唯一键相邻，GAP,Next-Key会阻止Insert。

##     ##

扫码关注作者微信公众号

![format_png 2][]

**扩展阅读**

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

全文完。

Enjoy MySQL :)

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

![format_png 3][]

[format_png]: /images/20221120/a4da7c2aeb2b4a0c9befa137f27ccf82.png
[format_png 1]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X3BuZy9iQ29iTlR4YWZXTkI2dlBFN08wM1F2ZjFtZllTbU5sek1nVjJwTEw2R2c3V2pFSU9vQXM4QVNtRkw1SU9jdkNUa3pQSTBTTnlVWUF5SDVGUkJsS3JLQS82NDA?x-oss-process=image/format,png
[format_png 2]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X2dpZi9iQ29iTlR4YWZXTjdGcjQ1MEtYdFlTTXk3QUtwQTA3Y29XWmdHUjVLNzdCaWJLdWNOb2VKam1vVHdMNlJ5aWJ4U21NVnZUWXcwekRJT0tVUUo4R2dUdGhBLzY0MA?x-oss-process=image/format,png
[Link 1]: http://mp.weixin.qq.com/s?__biz=MjM5NzAzMTY4NQ%3D%3D&chksm=bd3b4ecb8a4cc7dd7dde966b927f1c29d60f5d9790eafef0b1cf27e1d31d4564f6ce6ba5e59f&idx=1&mid=2653934753&scene=21&sn=1690353d72f1d11e026b00b027cfd97b#wechat_redirect
[Link 2]: http://mp.weixin.qq.com/s?__biz=MjM5NzAzMTY4NQ%3D%3D&chksm=bd3b4e738a4cc7659668d864a5148a116fb4bf7ffa9a2da620d21600d86a814fe359ce1bcd91&idx=1&mid=2653934617&scene=21&sn=e5c538301b81b814f8e9362118454d5e#wechat_redirect
[Link 3]: http://mp.weixin.qq.com/s?__biz=MjM5NzAzMTY4NQ%3D%3D&chksm=bd3b49938a4cc085c3502c853206bb5cd13f9c6f498e9b8d394d2c3b9802693bf47ec8726ec5&idx=1&mid=2653934585&scene=21&sn=271f56d58d1d189e13a82a28a3980857#wechat_redirect
[Link 4]: http://mp.weixin.qq.com/s?__biz=MjM5NzAzMTY4NQ%3D%3D&chksm=bd3b49a28a4cc0b486f1d43e03d8637b3d38126204ddb4aeebea9a2080478f11a015917d8aaa&idx=1&mid=2653934536&scene=21&sn=dad17ee35e45b34049e3e4ec6dc1d015#wechat_redirect
[Link 5]: http://mp.weixin.qq.com/s?__biz=MjM5NzAzMTY4NQ%3D%3D&chksm=bd3b49018a4cc017643fed70ce3316e4dd07042fe286f51a9bbbc906aedfa2d48b50870aedb3&idx=1&mid=2653934443&scene=21&sn=b60c700cbf90d84a48b060b9ed1fcaa5#wechat_redirect
[Link 6]: http://mp.weixin.qq.com/s?__biz=MjM5NzAzMTY4NQ%3D%3D&chksm=bd3b49468a4cc050c699592a4970a33c61db386589f6199b8d9d6a0b1ac4eb3aabc5d52aa3dc&idx=1&mid=2653934380&scene=21&sn=3266e158add079c382329644e65d5318#wechat_redirect
[Link 7]: http://mp.weixin.qq.com/s?__biz=MjM5NzAzMTY4NQ%3D%3D&chksm=bd3b49628a4cc0742336c9684c704012198da94e28aff48be179b553e2557cd2a125fd13232d&idx=1&mid=2653934344&scene=21&sn=9b1a4ec019b19dd8a8367e34e3b11ee6#wechat_redirect
[Link 8]: http://mp.weixin.qq.com/s?__biz=MjM5NzAzMTY4NQ%3D%3D&chksm=bd3b48b38a4cc1a57f5e3bdbacb09c3ced1bf70484aca7de764c651e2ce40688ad7aa90704b3&idx=1&mid=2653934297&scene=21&sn=1b4415f14d1350a00cf4866817d2395e#wechat_redirect
[Link 9]: http://mp.weixin.qq.com/s?__biz=MjM5NzAzMTY4NQ%3D%3D&chksm=bd3b542f8a4cdd39f6195aceb447b7dda3c043af8a00e4048abc66f1150d122762ba93220940&idx=1&mid=2653933125&scene=21&sn=54e4c1a12223c45e4232e2227d7d19da#wechat_redirect
[Link 10]: http://mp.weixin.qq.com/s?__biz=MjM5NzAzMTY4NQ%3D%3D&chksm=bd3b48b38a4cc1a57362dba651734cd5aea259b9ebd770685f54927f7877f98f3279c5e4c072&idx=2&mid=2653934297&scene=21&sn=c5d2afc9fab595801f640ab616f03bde#wechat_redirect
[format_png 3]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X3BuZy9udHM1Mm5IaGVUeW1RWUx1aEFnaWJ6cUVTb2ZURE84OGZvcDU4Nmd6TFU4RHhCOEZiUllpYnJaeDQ2NTFyY0FDSlBhY0c0ZGtSU0JMTklmcWZ4UEthRkRBLzY0MA?x-oss-process=image/format,png