死锁案例九
文 | 杨一 on 运维
转 | 来源:公众号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 测试用例
文字版贴出来,方便大家测试。
T1
Sess2:
INSERT IGNORE INTO tc (c2, c1, c3, created_at, c4, c5) VALUES
(95529, 4083702165, 3549694, now(), 1, 5),
(95529, 4083702165, 3544063, now(), 1, 5),
(95529, 4083702165, 3078355, now(), 1, 5);
T2
Sess1:
INSERT IGNORE INTO tc (c2, c1, c3, created_at, c4, c5) VALUES
(95529, 4083702165, 3549685, now(), 1, 4),
(95529, 4083702165, 3549694, now(), 1, 4);
T3
Sess2:
INSERT IGNORE INTO tc (c2, c1, c3, created_at, c4, c5) VALUES
(95529, 4083702165, 3549691, now(), 1, 5);
T4 sess1 死锁
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 进行判断.
其次 我们需要了解锁的兼容性矩阵。
从兼容性矩阵我们可以得到如下结论:
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。
扩展阅读
1. 漫谈死锁
2. 如何阅读死锁日志
3. 死锁案例一
4. 死锁案例二
5. 死锁案例三
6. 死锁案例四
7. 死锁案例五
8. 死锁案例六
9. 死锁案例七
10. 死锁案例八
-The End-
Vol.165
有赞技术团队
为 300 万商家,150 个行业,200 亿电商交易额
提供技术支持
微商城|零售|美业
微信公众号:有赞coder 微博:@有赞技术
技术博客:tech.youzan.com
The bigger the dream,
the more important the team.