实战案例：解决线上数据库死锁，就是这么简单！

作者：Hollis

连接：https://mp.weixin.qq.com/s/K7wh609YJaYQ64QI-URqLQ

前几天，线上发生了一次数据库死锁问题，这一问题前前后后排查了比较久的时间，这个过程中自己也对数据库的锁机制有了更深的理解。

本文总结了这次死锁排查的全过程，并分析了导致死锁的原因及解决方案。希望给大家提供一个死锁的排查及解决思路。

本文涉及到 MySQL 执行引擎、数据库隔离级别、InnoDB 锁机制、索引、数据库事务等多领域知识。前车之鉴，后事之师，希望读者们都可以有所收获。

现象

某天晚上，同事正在发布，突然线上大量报警，很多是关于数据库死锁的，报警提示信息如下：

{"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
### SQL: 
update fund_transfer_stream set gmt_modified=now(),state = ? where fund_transfer_order_no = ? and seller_id = ? and state = 'NEW'

通过报警，我们基本可以定位到发生死锁的数据库以及数据库表。先来介绍下本文案例中涉及到的数据库相关信息。

背景情况

我们使用的数据库是 MySQL 5.7，引擎是 InnoDB，事务隔离级别是 READ-COMMITED。

数据库版本查询方法：

select version();

引擎查询方法：

show create table fund_transfer_stream;

建表语句中会显示存储引擎信息，形如：ENGINE=InnoDB。

事务隔离级别查询方法：

select @@tx_isolation;

事务隔离级别设置方法（只对当前 Session 生效）：

set session transaction isolation level read committed;

PS：注意，如果数据库是分库的，以上几条 SQL 语句需要在单库上执行，不要在逻辑库执行。

发生死锁的表结构及索引情况（隐去了部分无关字段和索引）：

CREATE TABLE `fund_transfer_stream` ( 
 `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
 `gmt_create` datetime NOT NULL COMMENT '创建时间',
 `gmt_modified` datetime NOT NULL COMMENT '修改时间', 
 `pay_scene_name` varchar(256) NOT NULL COMMENT '支付场景名称', 
 `pay_scene_version` varchar(256) DEFAULT NULL COMMENT '支付场景版本',
 `identifier` varchar(256) NOT NULL COMMENT '唯一性标识',
 `seller_id` varchar(64) NOT NULL COMMENT '卖家Id',
 `state` varchar(64) DEFAULT NULL COMMENT '状态', `fund_transfer_order_no` varchar(256) 
 DEFAULT NULL COMMENT '资金平台返回的状态', 
 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 COMMENT='资金流水';

该数据库共有三个索引，1 个聚簇索引（主键索引），2 个非聚簇索引（非主键索引）。

聚簇索引：

PRIMARY KEY (`id`)

非聚簇索引：

KEY `idx_seller` (`seller_id`),
KEY `idx_seller_transNo` (`seller_id`,`fund_transfer_order_no`(20))

以上两个索引，其实 idx_seller_transNo 已经覆盖到了 idx_seller，由于历史原因，该表以 seller_id 分表，所以是先有的 idx_seller，后有的 idx_seller_transNo。

死锁日志

当数据库发生死锁时，可以通过以下命令获取死锁日志：

show engine innodb status

发生死锁，第一时间查看死锁日志，得到死锁日志内容如下：

Transactions deadlock detected, dumping detailed information.
2019-03-19T21:44:23.516263+08:00 5877341 [Note] InnoDB: 
*** (1) TRANSACTION:
TRANSACTION 173268495, ACTIVE 0 sec fetching rows
mysql tables in use 1, locked 1
LOCK WAIT 304 lock struct(s), heap size 41168, 6 row lock(s), undo log entries 1
MySQL thread id 5877358, OS thread handle 47356539049728, query id 557970181 11.183.244.150 fin_instant_app updating
update `fund_transfer_stream` set `gmt_modified` = NOW(), `state` = 'PROCESSING' where ((`state` = 'NEW') AND (`seller_id` = '38921111') AND (`fund_transfer_order_no` = '99010015000805619031958363857'))
2019-03-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 0
2019-03-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 0
2019-03-19T21:44:23.517793+08:00 5877341 [Note] InnoDB: 
*** (2) TRANSACTION:
TRANSACTION 173268500, ACTIVE 0 sec fetching rows, thread declared inside InnoDB 81
mysql tables in use 1, locked 1
302 lock struct(s), heap size 41168, 2 row lock(s), undo log entries 1
MySQL thread id 5877341, OS thread handle 47362313119488, query id 557970189 11.131.81.107 fin_instant_app updating
update `fund_transfer_stream_0056` set `gmt_modified` = NOW(), `state` = 'PROCESSING' where ((`state` = 'NEW') AND (`seller_id` = '38921111') AND (`fund_transfer_order_no` = '99010015000805619031957477256'))
2019-03-19T21:44:23.517855+08:00 5877341 [Note] InnoDB: 
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 173 page no 12416 n bits 128 index PRIMARY of table `fin_instant_0003`.`fund_transfer_stream_0056` trx id 173268500 lock_mode X locks rec but not gap
Record lock, heap no 56 PHYSICAL RECORD: n_fields 17; compact format; info bits 0
2019-03-19T21:44:23.519053+08:00 5877341 [Note] InnoDB: 
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 173 page no 13726 n bits 248 index idx_seller_transNo of table `fin_instant_0003`.`fund_transfer_stream_0056` trx id 173268500 lock_mode X locks rec but not gap waiting
Record lock, heap no 168 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
2019-03-19T21:44:23.519297+08:00 5877341 [Note] InnoDB: *** WE ROLL BACK TRANSACTION (2)

简单解读一下死锁日志，可以得到以下信息：

①导致死锁的两条 SQL 语句分别是：

update `fund_transfer_stream_0056` 
set `gmt_modified` = NOW(), `state` = 'PROCESSING' 
where ((`state` = 'NEW') AND (`seller_id` = '38921111') AND (`fund_transfer_order_no` = '99010015000805619031957477256'))

update `fund_transfer_stream_0056` 
set `gmt_modified` = NOW(), `state` = 'PROCESSING' 
where ((`state` = 'NEW') AND (`seller_id` = '38921111') AND (`fund_transfer_order_no` = '99010015000805619031958363857'))

②事务 1，持有索引 idx_seller_transNo 的锁，在等待获取 PRIMARY 的锁。

③事务 2，持有 PRIMARY 的锁，在等待获取 idx_seller_transNo 的锁。

④因事务 1 和事务 2 之间发生循环等待，故发生死锁。

⑤事务 1 和事务 2 当前持有的锁均为：lock_mode X locks rec but not gap。

两个事务对记录加的都是 X 锁，No Gap 锁，即对当行记录加锁(Record Lock)，并未加间隙锁。

X 锁：排他锁、又称写锁。若事务 T 对数据对象 A 加上 X 锁，事务 T 可以读 A 也可以修改 A，其他事务不能再对 A 加任何锁，直到 T 释放 A 上的锁。这保证了其他事务在 T 释放 A 上的锁之前不能再读取和修改 A。

与之对应的是 S 锁：共享锁，又称读锁，若事务 T 对数据对象 A 加上 S 锁，则事务 T 可以读 A 但不能修改 A，其他事务只能再对 A 加 S 锁，而不能加 X 锁，直到 T 释放 A 上的 S 锁。

这保证了其他事务可以读 A，但在 T 释放 A 上的 S 锁之前不能对 A 做任何修改。

Gap Lock：间隙锁，锁定一个范围，但不包括记录本身。Gap 锁的目的，是为了防止同一事务的两次当前读，出现幻读的情况。

Next-Key Lock：1+2，锁定一个范围，并且锁定记录本身。对于行的查询，都是采用该方法，主要目的是解决幻读的问题。

问题排查

根据我们目前已知的数据库相关信息，以及死锁的日志，我们基本可以做一些简单的判定。

首先，此次死锁一定是和 Gap 锁以及 Next-Key Lock 没有关系的。因为我们的数据库隔离级别是 RC（READ-COMMITED）的，这种隔离级别是不会添加 Gap 锁的。前面的死锁日志也提到这一点。

然后，就要翻代码了，看看我们的代码中事务到底是怎么做的。核心代码及 SQL 如下：

@Transactional(rollbackFor = Exception.class)
public int doProcessing(String sellerId, Long id, String fundTransferOrderNo) {
 fundTreansferStreamDAO.updateFundStreamId(sellerId, id, fundTransferOrderNo);
 return fundTreansferStreamDAO.updateStatus(sellerId, fundTransferOrderNo,"PROCESSING");
}

该代码的目的是先后修改同一条记录的两个不同字段，updateFundStreamId SQL：

update fund_transfer_stream
 set gmt_modified=now(),fund_transfer_order_no = #{fundTransferOrderNo}
 where id = #{id} and seller_id = #{sellerId}

updateStatus SQL：

update fund_transfer_stream
 set gmt_modified=now(),state = #{state}
 where fund_transfer_order_no = #{fundTransferOrderNo} and seller_id = #{sellerId}
 and state = 'NEW'

可以看到，我们的同一个事务中执行了两条 Update 语句，这里分别查看下两条 SQL 的执行计划：

实战案例：解决线上数据库死锁，就是这么简单！

updateFundStreamId 执行的时候使用到的是 PRIMARY 索引。

实战案例：解决线上数据库死锁，就是这么简单！

updateStatus 执行的时候使用到的是 idx_seller_transNo 索引。

通过执行计划，我们发现 updateStatus 其实是有两个索引可以用的，执行的时候真正使用的是 idx_seller_transNo 索引。这是因为 MySQL 查询优化器是基于代价（cost-based）的查询方式。

因此，在查询过程中，最重要的一部分是根据查询的 SQL 语句，依据多种索引，计算查询需要的代价，从而选择最优的索引方式生成查询计划。

我们查询执行计划是在死锁发生之后做的，事后查询的执行计划和发生死锁那一刻的索引使用情况并不一定是相同的。

但是，我们结合死锁日志，也可以定位到以上两条 SQL 语句执行的时候使用到的索引。

即 updateFundStreamId 执行的时候使用到的是 PRIMARY 索引，updateStatus 执行的时候使用到的是 idx_seller_transNo 索引。

有了以上这些已知信息，我们就可以开始排查死锁原因及其背后的原理了。

通过分析死锁日志，再结合我们的代码以及数据库建表语句，我们发现主要问题出在我们的 idx_seller_transNo 索引上面：

KEY `idx_seller_transNo` (`seller_id`,`fund_transfer_order_no`(20))

索引创建语句中，我们使用了前缀索引，为了节约索引空间，提高索引效率，我们只选择了 fund_transfer_order_no 字段的前 20 位作为索引值。

因为 fund_transfer_order_no 只是普通索引，而非唯一性索引。又因为在一种特殊情况下，会有同一个用户的两个 fund_transfer_order_no 的前 20 位相同。

这就导致两条不同的记录的索引值一样（因为 seller_id 和 fund_transfer_order_no(20) 都相同）。

就如本文中的例子，发生死锁的两条记录的 fund_transfer_order_no 字段的这两个值就是前 20 位是相同的：

99010015000805619031958363857
99010015000805619031957477256

实战案例：解决线上数据库死锁，就是这么简单！

那么为什么 fund_transfer_order_no 的前 20 位相同会导致死锁呢？

加锁原理

我们就拿本次的案例来看一下 MySQL 数据库加锁的原理是怎样的，本文的死锁背后又发生了什么。

我们在数据库上模拟死锁场景，执行顺序如下：

实战案例：解决线上数据库死锁，就是这么简单！

我们知道，在 MySQL 中，行级锁并不是直接锁记录，而是锁索引。索引分为主键索引和非主键索引两种：

如果一条 SQL 语句操作了主键索引，MySQL 就会锁定这条主键索引。
如果一条语句操作了非主键索引，MySQL 会先锁定该非主键索引，再锁定相关的主键索引。

主键索引的叶子节点存的是整行数据。在 InnoDB 中，主键索引也被称为聚簇索引（Clustered Index）。

非主键索引的叶子节点的内容是主键的值，在 InnoDB 中，非主键索引也被称为非聚簇索引（Secondary Index）。

所以，本文的示例中涉及到的索引结构（索引是 B+ 树，简化成表格了）如图：

实战案例：解决线上数据库死锁，就是这么简单！

死锁的发生与否，并不在于事务中有多少条 SQL 语句，死锁的关键在于：两个(或以上)的 Session 加锁的顺序不一致。

那么接下来就看下上面的例子中两个事务的加锁顺序是怎样的：

实战案例：解决线上数据库死锁，就是这么简单！

下图是分解图，每一条 SQL 执行的时候加锁情况：

实战案例：解决线上数据库死锁，就是这么简单！

结合以上两张图，我们发现了导致死锁的原因：

事务 1 执行 update1 占用 PRIMARY = 1 的锁；事务 2 执行 update1 占有 PRIMARY = 2 的锁；。
事务 1 执行 update2 占有 idx_seller_transNo = (3111095611，99010015000805619031)的锁，尝试占有 PRIMARY = 2 锁失败（阻塞）。
事务 2 执行 update2 尝试占有 idx_seller_transNo = (3111095611，99010015000805619031)的锁失败（死锁）。

事务在以非主键索引为 Where 条件进行 Update 的时候，会先对该非主键索引加锁，然后再查询该非主键索引对应的主键索引都有哪些，再对这些主键索引进行加锁。）

解决方法

至此，我们分析清楚了导致死锁的根本原理以及其背后的原理。那么这个问题解决起来就不难了。

可以从两方面入手，分别是：

修改索引
修改代码（包含 SQL 语句）

修改索引：只要我们把前缀索引 idx_seller_transNo 中 fund_transfer_order_no 的前缀长度修改下就可以了。

比如改成 50，即可避免死锁。但是，改了 idx_seller_transNo 的前缀长度后，可以解决死锁的前提条件是 Update 语句真正执行的时候，会用到 fund_transfer_order_no 索引。

如果 MySQL 查询优化器在代价分析之后，决定使用索引 KEY idx_seller(seller_id)，那么还是会存在死锁问题。原理和本文类似。

所以，根本解决办法就是改代码：

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

总结与思考

在死锁发生之后的一周内，我几乎每天都会抽空研究一会，问题早早的就定位到了，修改方案也有了，但是其中原理一直没搞清楚。

前前后后做过很多种推断及假设，又都被自己一次次推翻。最终还是要靠实践来验证自己的想法。

于是我自己在本地安装了数据库，实战的做了些测试，并实时查看数据库锁情况。show engine innodb status ;可以查看锁情况。最终才搞清楚原理。

简单说几点思考：

遇到问题，不要猜！！！亲手复现下问题，然后再来分析。
不要忽略上下文！！！我刚开始就是只关注死锁日志，一直忽略了代码中的事务其实还执行了另外一条 SQL 语句（updateFundStreamId）。
理论知识再充足，关键时刻不一定想的起来！！！
坑都是自己埋的！！！

作者：Hollis

实战案例：解决线上数据库死锁，就是这么简单！

相关推荐