一次诡异的线上数据库的死锁问题排查过程

标签: dev | 发表时间:2019-04-10 00:00 | 作者:
出处:http://itindex.net/relian

前几天,线上发生了一次数据库死锁问题,这一问题前前后后排查了比较久的时间,这个过程中自己也对数据库的锁机制有了更深的理解。本文总结了这次死锁排查的全过程,并分析了导致死锁的原因及解决方案。希望给大家提供一个死锁的排查及解决思路。

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


1

现象

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

   

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

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


2

背景情况

我们使用的数据库是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


3

死锁日志

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

   

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)

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

1、导致死锁的两条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'))

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),并未加间隙锁。

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,锁定一个范围,并且锁定记录本身。对于行的查询,都是采用该方法,主要目的是解决幻读的问题。

4

问题排查

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

首先,此次死锁一定是和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字段的值:99010015000805619031958363857和99010015000805619031957477256 这两个就是前20位相同的。

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

5

加锁原理

我们就拿本次的案例来看一下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的时候,会先对该非主键索引加锁,然后再查询该非主键索引对应的主键索引都有哪些,再对这些主键索引进行加锁。)


6

解决方法

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

可以从两方面入手,分别是修改索引和修改代码(包含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语句修改同一条记录。


7

总结与思考

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

前前后后做过很多中种推断及假设,又都被自己一次次推翻。最终还是要靠实践来验证自己的想法。于是我自己在本地安装了数据库,实战的做了些测试,并实时查看数据库锁情况。 show engine innodb status ;可以查看锁情况。最终才搞清楚原理。

简单说几点思考:

1、遇到问题,不要猜!!!亲手复现下问题,然后再来分析。

2、不要忽略上下文!!!我刚开始就是只关注死锁日志,一直忽略了代码中的事务其实还执行了另外一条SQL语句(updateFundStreamId)。

3、理论知识再充足,关键时刻不一定想的起来!!!

4、坑都是自己埋的!!!


参考资料: 

http://hedengcheng.com/?p=771

https://dev.mysql.com/doc/refman/5.7/en/innodb-transaction-isolation-levels.html

《MySql实战45讲》

https://www.hollischuang.com/archives/914


转发至朋友圈,是对我最大的支持。

相关 [线上 数据库 死锁] 推荐:

一次诡异的线上数据库的死锁问题排查过程

- - IT瘾-dev
前几天,线上发生了一次数据库死锁问题,这一问题前前后后排查了比较久的时间,这个过程中自己也对数据库的锁机制有了更深的理解. 本文总结了这次死锁排查的全过程,并分析了导致死锁的原因及解决方案. 希望给大家提供一个死锁的排查及解决思路. 本文涉及到MySql执行引擎、数据库隔离级别、Innodb锁机制、索引、数据库事务等多领域知识.

查找原始的MySQL数据库死锁ID

- - searchdatabase
  如果遇到死锁了,怎么解决呢?找到原始的锁ID,然后KILL掉一直持有的那个线程就可以了, 但是众多线程,可怎么找到引起死锁的线程ID呢. MySQL 发展到现在,已经非常强大了,这个问题很好解决.   线程A,我们用来锁定某些记录,假设这个线程一直没提交,或者忘掉提交了. 那么就一直存在,但是数据里面显示的只是SLEEP状态.

如何查看MySQL数据库的死锁日志

- - 数据库 - ITeye博客
如何查看MySQL数据库的死锁日志. 使用终端或命令提示符登录到MySQL,输入命令:mysql -h xxxx.xxx.xxx -P 3306 -u username -p. 解释:xxxx.xxx.xxx是数据库IP地址,username是数据库用户名,输入命令后,会让你输入username对应的密码,就可以登录了.

数据库sharding

- - 数据库 - ITeye博客
当团队决定自行实现sharding的时候,DAO层可能是嵌入sharding逻辑的首选位置,因为在这个层面上,每一个DAO的方法都明确地知道需要访问的数据表以及查询参数,借助这些信息可以直接定位到目标shard上,而不必像框架那样需要对SQL进行解析然后再依据配置的规则进行路由. 另一个优势是不会受ORM框架的制约.

数据库索引

- - CSDN博客推荐文章
索引是由用户创建的、能够被修改和删除的、实际存储于数据库中的物理存在;创建索引的目的是使用户能够从整体内容直接查找到某个特定部分的内容. 一般来说,索引能够提高查询,但是会增加额外的空间消耗,并且降低删除、插入和修改速度. 1.聚集索引:表数据按照索引的顺序来存储的. 2.非聚集索引:表数据存储顺序与索引顺序无关.

数据库事务

- - 数据库 - ITeye博客
事务传播发生在类似以下情形:. 假设methodB的配置是:. 如果methodA在事务里,那么methodB也在这个事务中运行. 如果methodA不在事务里,那么methodB重新建立一个事务运行. 如果methodA在事务里,那么methodB也在这个事务中运行. 如果methodA不在是事务里,那么methodB在非事务中运行.

数据库优化

- - 数据库 - ITeye博客
程序运行效率,优化应用程序,在SP编写过程中应该注意以下几点: . a) SQL的使用规范: .   i.尽量避免大事务操作,慎用holdlock子句,提高系统并发能力.   ii.尽量避免反复访问同一张或几张表,尤其是数据量较大的表,可以考虑先根据条件提取数据到临时表中,然后再做连接.   iii.尽量避免使用游标,因为游标的效率较差,如果游标操作的数据超过1万行,那么就应该改写;如果使用了游标,就要尽量避免在游标循环中再进行表连接的操作.

数据库调优

- - 数据库 - ITeye博客
1、1、调整数据结构的设计. 这一部分在开发信息系统之前完成,程序员需要考虑是否使用ORACLE数据库的分区功能,对于经常访问的数据库表是否需要建立索引等. 这一部分也是在开发信息系统之前完成,程序员在这一步需要考虑应用程序使用什么样的体系结构,是使用传统的Client/Server两层体系结构,还是使用Browser/Web/Database的三层体系结构.

MySQL数据库的修复

- Xin - 博客园-首页原创精华区
找到mysql的安装目录的bin/myisamchk工具,在命令行中输入:. 然后myisamchk 工具会帮助你恢复数据表的索引. 好象也不用重新启动mysql,问题就解决了. 当你试图修复一个被破坏的表的问题时,有三种修复类型. 如果你得到一个错误信息指出一个临时文件不能建立,删除信息所指出的文件并再试一次--这通常是上一次修复操作遗留下来的.