死锁案例十五

最新推荐文章于 2023-02-27 09:23:40 发布

老叶茶馆_

最新推荐文章于 2023-02-27 09:23:40 发布

阅读量1.1k

点赞数

一前言

死锁，其实是一个很有意思也很有挑战的技术问题，大概每个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篇案例，除非遇到特别有意思的案例,就封笔不写喽。

死锁案例十五

一 前言

二 案例分析