一 前言

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

二 案例分析

2.1 业务场景

业务上2条update语句，每条更新多行导致死锁。

2.2 环境说明

MySQL 5.7.22 事务隔离级别为RC模式。

2.3 死锁日志

  1. LATEST DETECTED DEADLOCK

  2. ------------------------

  3. 2020-01-10 18:10:18 0x7fc8b6843700

  4. *** (1) TRANSACTION:

  5. TRANSACTION 429649221, ACTIVE 0 sec fetching rows

  6. mysql tables in use 3, locked 3

  7. LOCK WAIT 8 lock struct(s), heap size 1136, 4 row lock(s)

  8. MySQL thread id 8853551, OS thread handle 140496048645888, query id 1105998873 10.210.106.46 test updating

  9. UPDATE

  10. x

  11. SET select_state = 1,

  12. updated = UNIX_TIMESTAMP()

  13. where state = 1

  14. AND iid = 245464472

  15. AND gid=454733404

  16. AND sid=36280812

  17. AND actid=0

  18. *** (1) WAITING FOR THIS LOCK TO BE GRANTED:

  19. 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

  20.  

  21. *** (2) TRANSACTION:

  22. TRANSACTION 429649224, ACTIVE 0 sec fetching rows, thread declared inside InnoDB 4997

  23. mysql tables in use 3, locked 3

  24. 8 lock struct(s), heap size 1136, 3 row lock(s)

  25. MySQL thread id 8844064, OS thread handle 140500032304896, query id 1105998875 10.210.105.14 test updating

  26. UPDATE

  27. x

  28. SET select_state = 1,

  29. updated = UNIX_TIMESTAMP()

  30. where state = 1

  31. AND iid = 245464472

  32. AND gid=454731534

  33. AND sid=36279265

  34. AND actid=0

  35. *** (2) HOLDS THE LOCK(S):

  36. 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

  37. *** (2) WAITING FOR THIS LOCK TO BE GRANTED:

  38. 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

  39. *** WE ROLL BACK TRANSACTION (2)

  40. ------------

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个事务访问的数据集合:

  1. |id st gid | |

  2. ---+---+-------+-------+

  3. |1 | 2 | 47812 | 事务二 |

  4. |2 | 2 | 42870 | 事务一 |

  5. |3 | 2 | 42870 | 事务一 |

  6. |4 | 2 | 47812 | 事务二 |

  7. |5 | 1 | 47812 | 事务二 |

  8. |6 | 1 | 42870 | 事务一 |

通俗一点的来说MySQL是通过辅助索引访问多条数据，逐行加锁，先对辅助索引加锁，然后针对对应的主键记录加锁。

把上面的数据的辅助索引和主键记录抽象出来如下关系图(画的有点丑，不影响核心意义 ^_^).

死锁案例十五_死锁案例_02

通过执行计划推测MySQL 先通过 idx_identify_gid访问数据，然后再通过 idx_identify_state 访问数据。

事务一的加锁记录顺序

  1. 1. 针对辅助索引 gid=42870 三行记录加上行锁。

  2. 2. 通过辅助索引包含的主键，锁定主键为 2 3 6 的记录。

  3. 3. 针对辅助索引 state=1 的两行记录加上行锁。

  4. 4. 针对state=1 对应的主键 5 6 加行锁。

事务二的加锁记录顺序

  1. 1. 针对辅助索引 gid= 47812 三行记录加上行锁。

  2. 2. 通过辅助索引包含的主键，锁定主键为 1 4 5 的记录。

  3. 3. 针对辅助索引 state=1 的两行记录加上行锁。

  4. 4. 针对state=1 对应的主键 5 6 加行锁。

死锁产生时序图

死锁案例十五_死锁案例_03如何解决呢？

1 关闭index_merge 特性。

2 优化索引。

3 或者强制走其中一个索引。

三 小结

MySQL是否会发生死锁，并不在于事务中有多少条SQL语句，而是在于:两个(或以上)的Session加锁的顺序不一致。分析死锁要充分理解死锁日志，遇到比较难的场景，可以根据核心信息多做推测。当然找开发分析业务流程会更有效果。

死锁系列我已经写了差将近20篇文章了，包括死锁日志分析，insert加锁，还有十几篇案例分析，等收集完20篇案例，除非遇到特别有意思的案例,就封笔不写喽。

 

死锁案例十五_死锁案例_04