MySQL 死锁处理过程案例解析

2023-12-2608:06:41数据库教程Comments1,633 views字数 6515阅读模式

MySQL 死锁处理过程案例解析文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/58297.html

记一次MySQL 死锁分析处理过程，聊聊我的思路。前车之鉴，后事之师。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/58297.html

以一个例子为切入点文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/58297.html

一、问题背景文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/58297.html

某业务模块反馈数据库最近出现过几次死锁告警的情况，本文总结了这次死锁排查的全过程，并分析了导致死锁的原因及解决方案。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/58297.html

希望给大家提供一个死锁的排查及解决思路。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/58297.html

基础环境：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/58297.html

    • 主机类型：x3850 X6
    • 操作系统：DB:CentOS Linux release 7.4.1708、APP:CentOS Linux release 7.2.1511 (Core)
    • 存储：IBM存储，2TB,MULTIPATH
    • 内存：64 G
    • CPU型号：E7-4830 v3 @ 2.10GHz ( 4 U * 12 core)
    • CPU核数：32CORE
    • 数据库环境：5.7.27
    • 事务隔离级别：READ-COMMITED

问题现象：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/58297.html

MySQL 死锁告警文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/58297.html

告警日志：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/58297.html

{"errorCode":"SYSTEM_ERROR","errorMsg":"nested exception is org. apache. ibatis.exceptions.PersistenceException: Error updating database. Cause: ERR-CODE: [TDDL-4614 [ERR_EXECUTE_ON_MYSQL] Deadlock found when trying to get lock; The error occurred while setting parameters\n### SQL: update A xxx   文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/58297.html

二、分析说明文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/58297.html

    • 通过分析日志定位、分析死锁原因；
    • 追溯历史数据，分析关键指标的历史波动,这些关键指标可以用来做为数据库健康度参考指标。
    • 用实际数据来验证推断，排除掉其它干扰因素，定位数据库问题的根本原因，帮助快速修复。

三、MySQL加锁机制文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/58297.html

在深入探究问题之前，我们先了解一下 MySQL 的加锁机制。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/58297.html

首先要明确的一点是 MySQL 加锁实际上是给索引加锁，而非给数据加锁。我们先看下MySQL 索引的结构。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/58297.html

MySQL 索引分为主键索引(或聚簇索引)和二级索引(或非主键索引、非聚簇索引、辅助索引，包括各种主键索引外的其他所有索引)。不同存储引擎对于数据的组织方式略有不同。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/58297.html

对InnoDB而言，主键索引和数据是存放在一起的，构成一颗B+树(称为索引组织表)，主键位于非叶子节点，数据存放于叶子节点。示意图如下：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/58297.html

MySQL 死锁处理过程案例解析文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/58297.html

而MyISAM是堆组织表，主键索引和数据分开存放，叶子节点保存的只是数据的物理地址，示意图如下：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/58297.html

MySQL 死锁处理过程案例解析文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/58297.html

二级索引的组织方式对于InnoDB和MyISAM是一样的，保存了二级索引和主键索引的对应关系，二级索引列位于非叶子节点，主键值位于叶子节点，示意图如下：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/58297.html

MySQL 死锁处理过程案例解析文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/58297.html

那么在MySQL 的这种索引结构下，我们怎么找到需要的数据呢？文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/58297.html

以select * from t where name='aaa'为例，MySQL Server对sql进行解析后发现name字段有索引可用，于是先在二级索引(图2-2)上根据name='aaa'找到主键id=17，然后根据主键17到主键索引上(图2-1)上找到需要的记录。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/58297.html

了解 MySQL 利用索引对数据进行组织和检索的原理后，接下来看下MySQL 如何给索引枷锁。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/58297.html

需要了解的是索引如何加锁和索引类型(主键、唯一、非唯一、没有索引)以及隔离级别(RC、RR等)有关。本例中限定隔离级别为RC，RR情况下和RC加锁基本一致，不同的是RC为了防止幻读会额外加上间隙锁。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/58297.html

2.1  根据主键进行更新文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/58297.html

update t set name='xxx' where id=29；只需要将主键上id=29的记录加上X锁即可(X锁称为互斥锁，加锁后本事务可以读和写，其他事务读和写会被阻塞)。如下：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/58297.html

MySQL 死锁处理过程案例解析文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/58297.html

2.2  根据唯一索引进行更新文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/58297.html

update t set name='xxx' where name='ddd';这里假设name是唯一的。InnoDB现在name索引上找到name='ddd'的索引项(id=29)并加上加上X锁，然后根据id=29再到主键索引上找到对应的叶子节点并加上X锁。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/58297.html

一共两把锁，一把加在唯一索引上，一把加在主键索引上。这里需要说明的是加锁是一步步加的，不会同时给唯一索引和主键索引加锁。这种分步加锁的机制实际上也是导致死锁的诱因之一。示意如下：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/58297.html

MySQL 死锁处理过程案例解析文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/58297.html

2.3 根据非唯一索引进行更新文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/58297.html

update t set name='xxx' where name='ddd';这里假设name不唯一，即根据name可以查到多条记录(id不同)。和上面唯一索引加锁类似，不同的是会给所有符合条件的索引项加锁。示意如下：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/58297.html

MySQL 死锁处理过程案例解析文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/58297.html

这里一共四把锁，加锁步骤如下：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/58297.html

1、在非唯一索引(name)上找到(ddd,29)的索引项，加上X锁；文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/58297.html

2、根据(ddd,29)找到主键索引的(29,ddd)记录，加X锁；文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/58297.html

3、在非唯一索引(name)上找到(ddd,37)的索引项，加上X锁；文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/58297.html

4、根据(ddd,29)找到主键索引的(37,ddd)记录，加X锁；文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/58297.html

从上面步骤可以看出，InnoDB对于每个符合条件的记录是分步加锁的，即先加二级索引再加主键索引；其次是按记录逐条加锁的，即加完一条记录后，再加另外一条记录，直到所有符合条件的记录都加完锁。那么锁什么时候释放呢？答案是事务结束时会释放所有的锁。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/58297.html

小结：MySQL 加锁和索引类型有关，加锁是按记录逐条加，另外加锁也和隔离级别有关。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/58297.html

四、疑问点排查及分析思路文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/58297.html

1、发生死锁的表结构及索引情况(隐去了部分无关字段和索引)：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/58297.html

如下：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/58297.html

CREATE TABLE `A` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',   `create_date` datetime NOT NULL ,   `modified_date` datetime NOT NULL ,    `pay_name` varchar(256) NOT NULL ,    `pay_version` varchar(256) DEFAULT NULL , `identifier` varchar(256) NOT NULL ,   `seller_id` varchar(64) NOT NULL , `state` varchar(64) DEFAULT NULL ,   `fund_transfer_ order_ no` varchar(256) DEFAULT NULL,   PRIMARY KEY (`id`),UNIQUE KEY `uk_scene_identifier` (KEY `idx_seller` (`seller_id`), KEY `idx_seller_transNo` (`seller_id`,`fund_transfer_order_no`(20))   ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ; 文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/58297.html

该表共有三个索引，1个主键索引，2个普通索引。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/58297.html

2、分析死锁日志文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/58297.html

发生死锁，第一时间查看死锁日志，内容如下：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/58297.html

Transactions deadlock detected, dumping detailed information. 2021-05-19T21:44:23.516263+08:00 5877341 [Note] InnoDB:  *** (1) TRANSACTION: TRANSACTION 173268495, ACTIVE 0 sec fetching rows mysql tables in use 1, locked 1LOCK WAIT 304 lock struct(s), heap size 41168, 6 row lock(s), undo log entries 1MySQL thread id 5877358, OS thread handle 47356539049728, query id 557970181 11.183.244.150 fin_instant_app updating update 死锁语句2021-05-19T21:44:23.516321+08:00 5877341 [Note] InnoDB:  *** (1) HOLDS THE LOCK(S): RECORD LOCKS space id 173 page no 13726 n bits 248 index idx_seller_transNo of table `xxx`.`fund_transfer_stream` trx id 173268495 lock_mode X locks rec but not gap Record lock, heap no 168 PHYSICAL RECORD: n_fields 3; compact format; info bits 02021-05-19T21:44:23.516565+08:00 5877341 [Note] InnoDB:  *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 173 page no 12416 n bits 128 index PRIMARY of table `xxx`.`fund_transfer_stream` trx id 173268495 lock_mode X locks rec but not gap waiting Record lock, heap no 56 PHYSICAL RECORD: n_fields 17; compact format; info bits 02021-05-19T21:44:23.517793+08:00 5877341 [Note] InnoDB:  *** (2) TRANSACTION: TRANSACTION 173268500, ACTIVE 0 sec fetching rows, thread declared inside InnoDB 81mysql tables in use 1, locked 1302 lock struct(s), heap size 41168, 2 row lock(s), undo log entries 1MySQL thread id 5877341, OS thread handle 47362313119488, query id 557970189 11.131.81.107 fin_instant_app updating update 死锁语句文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/58297.html

分析下死锁日志，可以得到以下信息：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/58297.html

  1. 导致死锁的两条SQL语句。
  2. 事务1，持有索引idx_seller_transNo的锁，在等待获取PRIMARY的锁。
  3. 事务2，持有PRIMARY的锁，在等待获取idx_seller_transNo的锁。
  4. 因事务1和事务2之间发生循环等待，故发生死锁。
  5. 事务1和事务2当前持有的锁均为：lock_mode X locks rec but not gap

两个事务对记录加的都是X 锁，No Gap锁，即对当行记录加锁(Record Lock)，并未加间隙锁。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/58297.html

3、常见锁类型文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/58297.html

X锁：排他锁、又称写锁。若事务T对数据对象A加上X锁，事务T可以读A也可以修改A，其他事务不能再对A加任何锁，直到T释放A上的锁。这保证了其他事务在T释放A上的锁之前不能再读取和修改A。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/58297.html

与之对应的是S锁：共享锁，又称读锁，若事务T对数据对象A加上S锁，则事务T可以读A但不能修改A，其他事务只能再对A加S锁，而不能加X锁，直到T释放A上的S锁。这保证了其他事务可以读A，但在T释放A上的S锁之前不能对A做任何修改。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/58297.html

Gap Lock：间隙锁，锁定一个范围，但不包括记录本身。GAP锁的目的，是为了防止同一事务的两次当前读，出现幻读的情况。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/58297.html

Next-Key Lock：1+2，锁定一个范围，并且锁定记录本身。对于行的查询，都是采用该方法，主要目的是解决幻读的问题。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/58297.html

根据目前掌握的信息，可以做一些简单的推断。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/58297.html

首先，此次死锁一定是和Gap锁以及Next-Key Lock没有关系的。因为数据库隔离级别是RC(READ-COMMITED)的，这种隔离级别是不会添加Gap锁的。前面的死锁日志也提到这一点。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/58297.html

然后，就要翻代码了，看看代码中事务到底是怎么做的。核心代码及SQL如下：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/58297.html

@Transactional(rollbackFor = Exception.class)public int doProcessing(String sellerId, Long id, String fundTransferOrderNo) { fundTreansferStreamDAO.updateFundStreamId(sellerId, id, fundTransferOrderNo); return fundTreansferStreamDAO.updateStatus(sellerId, fundTransferOrderNo,"PROCESSING"); }文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/58297.html

该代码的目的是先后修改同一条记录的两个不同字段，同一个事务中执行了两条Update语句，再分别查看下两条SQL的执行计划：分别用到了PRIMARY索引和idx_seller_transNo索引。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/58297.html

有了以上这些已知信息，就可以开始排查死锁原因及其背后的原理了。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/58297.html

通过分析死锁日志，再结合代码以及建表语句，发现主要问题出在idx_seller_transNo索引上面：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/58297.html

KEY `idx_seller_transNo` (`seller_id`,`fund_transfer_order_no`(20))文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/58297.html

索引创建语句中，使用了前缀索引，为了节约索引空间，提高索引效率，只选择了fund_transfer_order_no字段的前20位作为索引值。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/58297.html

因为fund_transfer_order_no只是普通索引，而非唯一性索引。又因为在一种特殊情况下，会有同一个用户的两个fund_transfer_order_no的前20位相同，这就导致两条不同的记录的索引值一样(因为seller_id 和fund_transfer_order_no(20)都相同 )。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/58297.html

那么为什么fund_transfer_order_no的前20位相同会导致死锁呢?文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/58297.html

我们知道，在MySQL中，行级锁并不是直接锁记录，而是锁索引。索引分为主键索引和非主键索引两种，如果一条sql语句操作了主键索引，MySQL就会锁定这条主键索引;如果一条语句操作了非主键索引，MySQL会先锁定该非主键索引，再锁定相关的主键索引。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/58297.html

    • 主键索引的叶子节点存的是整行数据。在InnoDB中，主键索引也被称为聚簇索引(clustered index)。
    • 非主键索引的叶子节点的内容是主键的值，在InnoDB中，非主键索引也被称为非聚簇索引(secondary index)。

死锁的发生与否，并不在于事务中有多少条SQL语句，死锁的关键在于：两个(或以上)的Session加锁的顺序不一致。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/58297.html

事务在以非主键索引为where条件进行Update的时候，会先对该非主键索引加锁，然后再查询该非主键索引对应的主键索引都有哪些，再对这些主键索引进行加锁。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/58297.html

五、解决方案文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/58297.html

解决方案文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/58297.html

至此，我们分析清楚了导致死锁的根本原理以及其背后的原理。那么这个问题解决起来就不难了。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/58297.html

可以从两方面入手，分别是修改索引和修改代码(包含SQL语句)。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/58297.html

修改索引：只要我们把前缀索引 idx_seller_transNo中fund_transfer_order_no的前缀长度修改下就可以了。比如改成50。即可避免死锁。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/58297.html

但是，改了idx_seller_transNo的前缀长度后，可以解决死锁的前提条件是update语句真正执行的时候，会用到fund_transfer_order_no索引。如果MySQL查询优化器在代价分析之后，决定使用索引 KEY idx_seller(seller_id)，那么还是会存在死锁问题。原理和本文类似。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/58297.html

所以，根本解决办法就是改代码：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/58297.html

      • 所有update都通过主键ID进行。
      • 在同一个事务中，避免出现多条update语句修改同一条记录。

其他思考文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/58297.html

在死锁发生之后的一周内，前前后后做过很多中种推断及假设，最终还是要靠实践来验证自己的想法。遇到问题，不要想当然，亲手复现下问题，然后再来分析。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/58297.html

通过本文，大家可以掌握死锁分析的基本理论和一般方法，希望能为大家工作中快速解决实际出现的死锁问题提供思路。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/58297.html

阿陶学长 数据与人文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/58297.html

  • 本站内容整理自互联网，仅提供信息存储空间服务，以方便学习之用。如对文章、图片、字体等版权有疑问，请在下方留言，管理员看到后，将第一时间进行处理。
  • 转载请务必保留本文链接：https://www.cainiaoxueyuan.com/sjk/58297.html

Comment

匿名网友 填写信息

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定