死锁案例十五
- 2020 年 2 月 13 日
- 笔记
一 前言
死锁，其实是一个很有意思也很有挑战的技术问题，大概每个DBA和部分开发同学都会在工作过程中遇见 。关于死锁我会持续写一个系列的案例分析，希望能够对想了解死锁的朋友有所帮助。
二 案例分析
2.1 业务场景
业务上2条update语句，每条更新多行导致死锁。
2.2 环境说明
MySQL 5.7.22 事务隔离级别为RC模式。
2.3 死锁日志
LATEST DETECTED DEADLOCK ------------------------ 2020-01-10 18:10:18 0x7fc8b6843700 *** (1) TRANSACTION: TRANSACTION 429649221, ACTIVE 0 sec fetching rows mysql tables in use 3, locked 3 LOCK WAIT 8 lock struct(s), heap size 1136, 4 row lock(s) MySQL thread id 8853551, OS thread handle 140496048645888, query id 1105998873 10.210.106.46 test updating UPDATE x SET select_state = 1, updated = UNIX_TIMESTAMP() where state = 1 AND iid = 245464472 AND gid=454733404 AND sid=36280812 AND actid=0 *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 243 page no 37236 n bits 104 index PRIMARY of table `test`.`x` trx id 429649221 lock_mode X locks rec but not gap waiting *** (2) TRANSACTION: TRANSACTION 429649224, ACTIVE 0 sec fetching rows, thread declared inside InnoDB 4997 mysql tables in use 3, locked 3 8 lock struct(s), heap size 1136, 3 row lock(s) MySQL thread id 8844064, OS thread handle 140500032304896, query id 1105998875 10.210.105.14 test updating UPDATE x SET select_state = 1, updated = UNIX_TIMESTAMP() where state = 1 AND iid = 245464472 AND gid=454731534 AND sid=36279265 AND actid=0 *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 243 page no 37236 n bits 104 index PRIMARY of table `test`.`x` trx id 429649224 lock_mode X locks rec but not gap *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 243 page no 11188 n bits 792 index idx_identify_state of table `test`.`x` trx id 429649224 lock_mode X locks rec but not gap waiting *** WE ROLL BACK TRANSACTION (2) ------------
2.4 分析死锁日志
这个死锁案例比较特别2个事务各只有一条update导致死锁。不像其他案例，一个事务有2条或者2条以上。分析起来有点小困难，比较难模拟。
事务二 持有 主键记录的行锁，位置在 space id243pageno37236 ，并申请二级索引 idx_identify_state的行锁。其中 idx_identify_state 是(iid,state)的组合索引。
事务一 申请 主键记录的行锁 位置在 space id243pageno37236,该主键的行锁被事务二持有，其实事务一还持有辅助索引 idx_identify_state的行锁，只是没有显示出来，否则事务二不会等待申请 idx_identify_state的行锁。但是如果2条sql 都是通过 idx_identify_state 索引访问记录就不会导致死锁了，因为通过相同的索引访问记录，2个事务加锁的顺序一样，串行加锁导致其中一个sql等待而非死锁 ，导致死锁的核心逻辑是2个或者以上的事务访问相同记录加锁的顺序不一致，产生循环等待导致死锁。
分析到这里，根据经验猜测，要么2个sql选择了不同的辅助索引(但是如果是不同的索引，死锁日志里面应该是等待主键的行锁，不应该出现等待辅助索引的行锁)，要么是遇到 index_merge 导致的死锁。经过业务死锁复现 ，确定是 index_merge 导致的死锁。sql 的执行计划如下:
从执行计划来 Usingintersect(idx_identify_gid,idx_identify_state) SQL通过2个索引访问记录然后取交集。
2.5 场景分析
为了更透彻的理解该案例死锁的加锁顺序，接下来我们继续分析，把数据脱敏之后得到的2个事务访问的数据集合:
|id st gid | | ---+---+-------+-------+ |1 | 2 | 47812 | 事务二 | |2 | 2 | 42870 | 事务一 | |3 | 2 | 42870 | 事务一 | |4 | 2 | 47812 | 事务二 | |5 | 1 | 47812 | 事务二 | |6 | 1 | 42870 | 事务一 |
通俗一点的来说MySQL是通过辅助索引访问多条数据，逐行加锁，先对辅助索引加锁，然后针对对应的主键记录加锁。
把上面的数据的辅助索引和主键记录抽象出来如下关系图(画的有点丑，不影响核心意义 ^_^).
通过执行计划推测MySQL 先通过 idx_identify_gid访问数据，然后再通过 idx_identify_state 访问数据。
事务一的加锁记录顺序
1. 针对辅助索引 gid=42870 三行记录加上行锁。 2. 通过辅助索引包含的主键，锁定主键为 2 3 6 的记录。 3. 针对辅助索引 state=1 的两行记录加上行锁。 4. 针对state=1 对应的主键 5 6 加行锁。
事务二的加锁记录顺序
1. 针对辅助索引 gid= 47812 三行记录加上行锁。 2. 通过辅助索引包含的主键，锁定主键为 1 4 5 的记录。 3. 针对辅助索引 state=1 的两行记录加上行锁。 4. 针对state=1 对应的主键 5 6 加行锁。
死锁产生时序图
如何解决呢？
1 关闭index_merge 特性。
2 优化索引。
3 或者强制走其中一个索引。
三 小结
MySQL是否会发生死锁，并不在于事务中有多少条SQL语句，而是在于:两个(或以上)的Session加锁的顺序不一致。分析死锁要充分理解死锁日志，遇到比较难的场景，可以根据核心信息多做推测。当然找开发分析业务流程会更有效果。
死锁系列我已经写了差将近20篇文章了，包括死锁日志分析，insert加锁，还有十几篇案例分析，等收集完20篇案例，除非遇到特别有意思的案例,就封笔不写喽。
