死锁案例十五

淡淡的烟草味﹌ 2023-07-03 08:21 18阅读 0赞

### 一 前言 ###

死锁，其实是一个很有意思也很有挑战的技术问题，大概每个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 的执行计划如下:

![format_png][]

从执行计划来 `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是通过辅助索引访问多条数据，逐行加锁，先对辅助索引加锁，然后针对对应的主键记录加锁。

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

![format_png 1][]

通过执行计划推测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 加行锁。

死锁产生时序图

![format_png 2][]如何解决呢？

1 关闭index\_merge 特性。

2 优化索引。

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

### 三 小结 ###

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

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

推荐阅读

[漫谈死锁][Link 1]

[如何阅读死锁日志][Link 2]

[死锁案例十四][Link 3]

[show status和set gtid\_mode 导致线程死锁案例][show status_set gtid_mode]

\-The End-

--------------------

本公众号长期关注于数据库技术以及性能优化，故障案例分析，数据库运维技术知识分享，个人成长和自我管理等主题，欢迎扫码关注。

![format_png 3][]

叶老师新课程《**MySQL性能优化**》已经在腾讯课堂发布，本课程讲解读几个MySQL性能优化的核心要素：**合理利用索引，降低锁影响，提高事务并发度**。下面是报名小程序码，厚着脸皮请求大家推荐给需要的小伙伴们。

![format_png 4][]

下面是本课程内容目录

![format_png 5][]

扫码加入MySQL技术Q群

**（群号：****650149401）**

![format_png 6][]

点“在看”给我一朵小黄花![format_png 7][]

![format_png 8][]

[format_png]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL3N6X21tYml6X3BuZy9iQ29iTlR4YWZXT05qOHNvWFFqS1hHR2lielUyUmliOElkQ1QyTDdPaWFNc3A3RmVZTlE0WmhwOEVuZUlSektUaWMxaWNESmFTU0dtejVSbmljR24zeHgwdnI0dy82NDA?x-oss-process=image/format,png
[format_png 1]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL3N6X21tYml6X3BuZy9iQ29iTlR4YWZXT05qOHNvWFFqS1hHR2lielUyUmliOElkU2ZoYWhIZ3VkVWljdXpMQkN3Vjh4a3RtbGFiQTZYWmZDUzJXSFEzdlpqdUppYVJtbUtuRWhpYWJRLzY0MA?x-oss-process=image/format,png
[format_png 2]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL3N6X21tYml6X3BuZy9iQ29iTlR4YWZXT05qOHNvWFFqS1hHR2lielUyUmliOElkbnJwN29ISExGWThTOG1ETFdRcUw1ZjFaZnBYVmtNb2pwV1VzWUxnTVJyS29YcFZxWGRnVU5BLzY0MA?x-oss-process=image/format,png
[Link 1]: http://mp.weixin.qq.com/s?__biz=MzI4NjExMDA4NQ%3D%3D&chksm=f3c9738bc4befa9deee4c6b52785fdf0e47fd98934228253976c6296a4ef5e5944c780efceb0&idx=1&mid=2648451937&scene=21&sn=7b8eb17d70f754d7c5b0ed9df5ad965e#wechat_redirect
[Link 2]: http://mp.weixin.qq.com/s?__biz=MzI4NjExMDA4NQ%3D%3D&chksm=f3c97c0bc4bef51d6c92e9a79b959c7f7e41367d5429ec80b5881d165978f311a4d8f0452634&idx=1&mid=2648450273&scene=21&sn=2836db9eb1086a9fca91748dd233a234#wechat_redirect
[Link 3]: http://mp.weixin.qq.com/s?__biz=MzI4NjExMDA4NQ%3D%3D&chksm=f3c97117c4bef801e3a77d10c0a02ffdd74d79026e53498e62a90b3a5fec7dbd8618466b0b5a&idx=1&mid=2648451581&scene=21&sn=f25fc68c552b975735ebd39919d6ff31#wechat_redirect
[show status_set gtid_mode]: http://mp.weixin.qq.com/s?__biz=MzI4NjExMDA4NQ%3D%3D&chksm=f3c97028c4bef93e43fac9009dbed70047a2e323d3b30160192ccbaa7183e608544ccd4c6221&idx=1&mid=2648451266&scene=21&sn=cd0b5d40969dd05dab6eea9610f85f36#wechat_redirect
[format_png 3]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X2dpZi9iQ29iTlR4YWZXTjdGcjQ1MEtYdFlTTXk3QUtwQTA3Y29XWmdHUjVLNzdCaWJLdWNOb2VKam1vVHdMNlJ5aWJ4U21NVnZUWXcwekRJT0tVUUo4R2dUdGhBLzY0MA?x-oss-process=image/format,png
[format_png 4]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X2pwZy9udHM1Mm5IaGVUeUE0TWRFaWF1UDBlR1lMdVlXNXhORU9tdzNHeHlkWGxiRGcyS0Rrd2liU2lhS0lZS1Y2aFQ5MDNvcnhERmliOVZSYzFTWkRQVXUxTmJvWUEvNjQw?x-oss-process=image/format,png
[format_png 5]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X2pwZy9udHM1Mm5IaGVUeUE0TWRFaWF1UDBlR1lMdVlXNXhORU9QcmRJZXpLRm1wZm1XNWIwQXNVUmxEdnFIOFJUZWliOWY2YjJYRVg0NXZQaWF2UDAxQmZHOHZoUS82NDA?x-oss-process=image/format,png
[format_png 6]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X2pwZy83aDRpY0o0aWNzSkJqZHlLNEFBWDhDcWhuSkluc0szOGlheGpiaWI5NGF4SlA0Y3NXaWJUdDRwNGJuM2p6SFNLY3RHV3AxaWFpYnIxaWFGVUtiMU9rMUJJbFJ3UmpBLzY0MA?x-oss-process=image/format,png
[format_png 7]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL3N6X21tYml6X2dpZi83aDRpY0o0aWNzSkJnZUQyQ2pNaWJ0VThQRXROcW5wSGFpY3FKZHlIUFE0VWxBa1Y4aWF6b1BsRDJITEFOaWFZMWNMN2pVOUtuekhiNFVPNGszOThLazBUdDViQS82NDA?x-oss-process=image/format,png
[format_png 8]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL3N6X21tYml6X3BuZy83aDRpY0o0aWNzSkJnZUQyQ2pNaWJ0VThQRXROcW5wSGFpY3FqSGtXWHBPcGttZG5HMlFXSWxPbjJVeE9KUkNFdDNTVWdGY1FiVTlvNFdHc1p2OTVvRGhZMVEvNjQw?x-oss-process=image/format,png