记一次MySQL死锁排查过程

数据库 MySQL
以前接触到的数据库死锁,都是批量更新时加锁顺序不一致而导致的死锁,但是上周却遇到了一个很难理解的死锁。借着这个机会又重新学习了一下 mysql 的死锁知识以及常见的死锁场景。在多方调研以及和同事们的讨论下终于发现了这个死锁问题的成因,收获颇多。

 

[[213834]]

背景

以前接触到的数据库死锁,都是批量更新时加锁顺序不一致而导致的死锁,但是上周却遇到了一个很难理解的死锁。借着这个机会又重新学习了一下 mysql 的死锁知识以及常见的死锁场景。在多方调研以及和同事们的讨论下终于发现了这个死锁问题的成因,收获颇多。虽然是后端程序员,我们不需要像 DBA 一样深入地去分析与锁相关的源码,但是如果我们能够掌握基本的死锁排查方法,对我们的日常开发还是大有裨益的。

PS:本文不会介绍死锁的基本知识,mysql 的加锁原理可以参考本文的参考资料提供的链接。

死锁起因

先介绍一下数据库和表情况,因为涉及到公司内部真是的数据,所以以下都做了模拟,不会影响具体的分析。

我们采用的是 5.5 版本的 mysql 数据库,事务隔离级别是默认的 RR(Repeatable-Read),采用 innodb 引擎。假设存在 test 表:

CREATE TABLE `test` ( 
 `id` int(11) unsigned NOT NULL AUTO_INCREMENT, 
 `a` int(11) unsigned DEFAULT NULL
 PRIMARY KEY (`id`), 
 UNIQUE KEY `a` (`a`) 
) ENGINE=InnoDB AUTO_INCREMENT=100 DEFAULT CHARSET=utf8;    
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.

表的结构很简单,一个主键 id,另一个***索引 a。表里的数据如下:

mysql> select * from test; 
 
+----+------+ 
 
| id | a    | 
 
+----+------+ 
 
|  1 |    1 | 
 
|  2 |    2 | 
 
|  4 |    4 | 
 
+----+------+ 
 
rows in set (0.00 sec)    
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.
  • 9.
  • 10.
  • 11.
  • 12.
  • 13.
  • 14.
  • 15.
  • 16.
  • 17.

出现死锁的操作如下:

步骤 事务 1 事务 2
1   begin
2   delete from test where a = 2;
3 begin  
4 delete from test where a = 2; (事务 1 卡住)  
5 提示出现死锁:ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction insert into test (id, a) values (10, 2);


然后我们可以通过 SHOW ENGINE INNODB STATUS; 来查看死锁日志:

------------------------ 
 
LATEST DETECTED DEADLOCK 
 
------------------------ 
 
170219 13:31:31 
 
*** (1) TRANSACTION
 
TRANSACTION 2A8BD, ACTIVE 11 sec starting index read 
 
mysql tables in use 1, locked 1 
 
LOCK WAIT 2 lock struct(s), heap size 376, 1 row lock(s) 
 
MySQL thread id 448218, OS thread handle 0x2abe5fb5d700, query id 18923238 renjun.fangcloud.net 121.41.41.92 root updating 
 
delete from test where a = 2 
 
*** (1) WAITING FOR THIS LOCK TO BE GRANTED: 
 
RECORD LOCKS space id 0 page no 923 n bits 80 index `a` of table `oauthdemo`.`test` trx id 2A8BD lock_mode X waiting 
 
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32 
 
0: len 4; hex 00000002; asc     ;; 
 
1: len 4; hex 00000002; asc     ;; 
 
*** (2) TRANSACTION
 
TRANSACTION 2A8BC, ACTIVE 18 sec inserting 
 
mysql tables in use 1, locked 1 
 
4 lock struct(s), heap size 1248, 3 row lock(s), undo log entries 2 
 
MySQL thread id 448217, OS thread handle 0x2abe5fd65700, query id 18923239 renjun.fangcloud.net 121.41.41.92 root update 
 
insert into test (id,a) values (10,2) 
 
*** (2) HOLDS THE LOCK(S): 
 
RECORD LOCKS space id 0 page no 923 n bits 80 index `a` of table `oauthdemo`.`test` trx id 2A8BC lock_mode X locks rec but not gap 
 
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32 
 
0: len 4; hex 00000002; asc     ;; 
 
1: len 4; hex 00000002; asc     ;; 
 
*** (2) WAITING FOR THIS LOCK TO BE GRANTED: 
 
RECORD LOCKS space id 0 page no 923 n bits 80 index `a` of table `oauthdemo`.`test` trx id 2A8BC lock mode S waiting 
 
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32 
 
0: len 4; hex 00000002; asc     ;; 
 
1: len 4; hex 00000002; asc     ;; 
 
*** WE ROLL BACK TRANSACTION (1)  
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.
  • 9.
  • 10.
  • 11.
  • 12.
  • 13.
  • 14.
  • 15.
  • 16.
  • 17.
  • 18.
  • 19.
  • 20.
  • 21.
  • 22.
  • 23.
  • 24.
  • 25.
  • 26.
  • 27.
  • 28.
  • 29.
  • 30.
  • 31.
  • 32.
  • 33.
  • 34.
  • 35.
  • 36.
  • 37.
  • 38.
  • 39.
  • 40.
  • 41.
  • 42.
  • 43.
  • 44.
  • 45.
  • 46.
  • 47.
  • 48.
  • 49.
  • 50.
  • 51.
  • 52.
  • 53.
  • 54.
  • 55.
  • 56.
  • 57.
  • 58.
  • 59.
  • 60.
  • 61.
  • 62.
  • 63.

分析

阅读死锁日志

遇到死锁,***步就是阅读死锁日志。死锁日志通常分为两部分,上半部分说明了事务 1 在等待什么锁:

170219 13:31:31 
 
*** (1) TRANSACTION
 
TRANSACTION 2A8BD, ACTIVE 11 sec starting index read 
 
mysql tables in use 1, locked 1 
 
LOCK WAIT 2 lock struct(s), heap size 376, 1 row lock(s) 
 
MySQL thread id 448218, OS thread handle 0x2abe5fb5d700, query id 18923238 renjun.fangcloud.net 121.41.41.92 root updating 
 
delete from test where a = 2 
 
*** (1) WAITING FOR THIS LOCK TO BE GRANTED: 
 
RECORD LOCKS space id 0 page no 923 n bits 80 index `a` of table `oauthdemo`.`test` trx id 2A8BD lock_mode X waiting 
 
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32 
 
0: len 4; hex 00000002; asc     ;; 
 
1: len 4; hex 00000002; asc     ;;    
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.
  • 9.
  • 10.
  • 11.
  • 12.
  • 13.
  • 14.
  • 15.
  • 16.
  • 17.
  • 18.
  • 19.
  • 20.
  • 21.
  • 22.
  • 23.

从日志里我们可以看到事务 1 当前正在执行 delete from test where a = 2,该条语句正在申请索引 a 的 X 锁,所以提示 lock_mode X waiting。

然后日志的下半部分说明了事务 2 当前持有的锁以及等待的锁:   

*** (2) TRANSACTION
 
TRANSACTION 2A8BC, ACTIVE 18 sec inserting 
 
mysql tables in use 1, locked 1 
 
4 lock struct(s), heap size 1248, 3 row lock(s), undo log entries 2 
 
MySQL thread id 448217, OS thread handle 0x2abe5fd65700, query id 18923239 renjun.fangcloud.net 121.41.41.92 root update 
 
insert into test (id,a) values (10,2) 
 
*** (2) HOLDS THE LOCK(S): 
 
RECORD LOCKS space id 0 page no 923 n bits 80 index `a` of table `oauthdemo`.`test` trx id 2A8BC lock_mode X locks rec but not gap 
 
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32 
 
0: len 4; hex 00000002; asc     ;; 
 
1: len 4; hex 00000002; asc     ;; 
 
*** (2) WAITING FOR THIS LOCK TO BE GRANTED: 
 
RECORD LOCKS space id 0 page no 923 n bits 80 index `a` of table `oauthdemo`.`test` trx id 2A8BC lock mode S waiting 
 
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32 
 
0: len 4; hex 00000002; asc     ;; 
 
1: len 4; hex 00000002; asc     ;;    
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.
  • 9.
  • 10.
  • 11.
  • 12.
  • 13.
  • 14.
  • 15.
  • 16.
  • 17.
  • 18.
  • 19.
  • 20.
  • 21.
  • 22.
  • 23.
  • 24.
  • 25.
  • 26.
  • 27.
  • 28.
  • 29.
  • 30.
  • 31.

从日志的 HOLDS THE LOCKS(S) 块中我们可以看到事务 2 持有索引 a 的 X 锁,并且是记录锁(Record Lock)。该锁是通过事务 2 在步骤 2 执行的 delete 语句申请的。由于是 RR 隔离模式下的基于***索引的等值查询(Where a = 2),所以会申请一个记录锁,而非 next-key 锁。

从日志的 WAITING FOR THIS LOCK TO BE GRANTED 块中我们可以看到事务 2 正在申请 S 锁,也就是共享锁。该锁是 insert into test (id,a) values (10,2) 语句申请的。insert 语句在普通情况下是会申请排他锁,也就是 X 锁,但是这里出现了 S 锁。这是因为 a 字段是一个***索引,所以 insert 语句会在插入前进行一次 duplicate key 的检查,为了使这次检查成功,需要申请 S 锁防止其他事务对 a 字段进行修改。

那么为什么该 S 锁会失败呢?这是对同一个字段的锁的申请是需要排队的。S 锁前面还有一个未申请成功的 X 锁,所以 S 锁必须等待,所以形成了循环等待,死锁出现了。

通过阅读死锁日志,我们可以清楚地知道两个事务形成了怎样的循环等待,再加以分析,就可以逆向推断出循环等待的成因,也就是死锁形成的原因。

死锁形成流程图

为了让大家更好地理解死锁形成的原因,我们再通过表格的形式阐述死锁形成的流程:

步骤
事务 1 事务 2
1   begin
2   delete from test where a = 2; 执行成功,事务 2 占有 a=2 下的 X 锁,类型为记录锁。
3 begin  
4 delete from test where a = 2; 事务 1 希望申请 a=2 下的 X 锁,但是由于事务 2 已经申请了一把 X 锁,两把 X 锁互斥,所以 X 锁申请进入锁请求队列。  
5 出现死锁,事务 1 权重较小,所以被选择回滚(成为牺牲品)。 insert into test (id, a) values (10, 2); 由于 a 字段建立了***索引,所以需要申请 S 锁以便检查 duplicate key,由于插入的 a 的值还是 2,所以排在 X 锁后面。但是前面的 X 锁的申请只有在事务 2commit 或者 rollback 之后才能成功,此时形成了循环等待,死锁产生。


拓展

在排查死锁的过程中,有个同事还发现了上述场景会产生另一种死锁,该场景无法通过手工复现,只有高并发场景下才有可能复现。

该死锁对应的日志这里就不贴出了,与上一个死锁的核心差别是事务 2 等待的锁从 S 锁换成了 X 锁,也就是 lock_mode X locks gap before rec insert intention waiting。我们还是通过表格来详细说明该死锁产生的流程:

步骤
事务 1 事务 2
1   begin
2   delete from test where a = 2; 执行成功,事务 2 占有 a=2 下的 X 锁,类型为记录锁。
3 begin  
4   【insert 第 1 阶段】insert into test (id, a) values (10, 2); 事务 2 申请 S 锁进行 duplicate key 进行检查。检查成功。
5 delete from test where a = 2; 事务 1 希望申请 a=2 下的 X 锁,但是由于事务 2 已经申请了一把 X 锁,两把 X 锁互斥,所以 X 锁申请进入锁请求队列。  
6 出现死锁,事务 1 权重较小,所以被选择回滚(成为牺牲品)。 【insert 第 2 阶段】insert into test (id, a) values (10, 2); 事务 2 开始插入数据,S 锁升级为 X 锁,类型为 insert intention。同理,X 锁进入队列排队,形成循环等待,死锁产生。


总结

排查死锁时,首先需要根据死锁日志分析循环等待的场景,然后根据当前各个事务执行的 SQL 分析出加锁类型以及顺序,逆向推断出如何形成循环等待,这样就能找到死锁产生的原因了。

 

PS:上述分析都是基于经验的推断,希望其他小伙伴们能够指出当中的错误以及不足指出,谢谢! 

责任编辑:庞桂玉 来源: ITPUB
相关推荐

2021-11-23 21:21:07

线上排查服务

2019-03-15 16:20:45

MySQL死锁排查命令

2019-04-15 13:15:12

数据库MySQL死锁

2018-07-20 08:44:21

Redis内存排查

2023-04-06 07:53:56

Redis连接问题K8s

2019-06-10 15:20:18

2021-05-13 08:51:20

GC问题排查

2018-11-13 10:44:23

Linux服务器双网卡网络

2021-03-29 12:35:04

Kubernetes环境TCP

2023-01-04 18:32:31

线上服务代码

2023-10-11 22:24:00

DubboRedis服务器

2023-10-10 12:05:45

2024-06-24 09:29:15

2017-09-22 10:16:16

MySQL数据库用户数据

2023-04-06 10:52:18

2019-08-26 09:50:09

2021-04-13 08:54:28

dubbo线程池事故排查

2024-04-10 08:48:31

MySQLSQL语句

2021-02-01 09:00:34

Ceph octopu集群运维

2022-02-08 17:17:27

内存泄漏排查
点赞
收藏

51CTO技术栈公众号