mysql死锁排查过程 – 微医搜索团队

内容纲要

一、问题描述

遇到mysql死锁问题，我们应该怎么排查分析呢?
以前的一个项目中实时消费mq消息，在高并发的情况下对消费的消息处理后保存到数据库中,经常发生死锁的情况。基于死锁问题，了解死锁的排查分析

二、锁冲突分析

2.1 innodb的事务与行锁机制

MySQL的事务支持不是绑定在MySQL服务器本身，而是与存储引擎相关，MyISAM不支持事务、采用的是表级锁，而InnoDB支持ACID事务、行级锁、并发。MySQL默认的行为是在每条SQL语句执行后执行一个COMMIT语句，从而有效的将每条语句作为一个单独的事务来处理。

2.2 锁冲突的产生

由于共享锁与排他锁是互斥的，当一方拥有了某行记录的排他锁后，另一方就不能其拥有共享锁，同样，一方拥有了其共享锁后，另一方也无法得到其排他锁。所以当两个事务会同时申请某相同记录行的锁资源，于是会产生锁冲突。由于两个事务都会申请主键索引，锁冲突只会发生在主键索引上。

三、死锁案发还原

3.1 数据库配置

采用mysql数据库版本5.7.11，事务隔离级别是可重复读，采用innodb引擎。

3.2 表结构：

CREATE TABLE `student` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',,
  `teach_id` int(11) NOT NULL，
  `name` varchar(10) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `teachId_idx` (`teach_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

3.2 模拟复现：

1.开启事务1

mysql> start transaction;(第一步)
Query OK, 0 rows affected

mysql> select * from student where id = 1 LOCK IN SHARE MODE;(第二步)
+----+----------+-------+
| id | teach_id | name  |
+----+----------+-------+
|  1 |       21 | test1 |
+----+----------+-------+
1 row in set

mysql> delete from student where id = 1;(第四步)
Query OK, 1 row affected

mysql>

2.开启事务2

mysql> start transaction;
Query OK, 0 rows affected

mysql> delete from student where id = 1;(第三步)
1213 - Deadlock found when trying to get lock; try restarting transaction

四、死锁排查分析

遇到死锁问题时，我们应该怎么处理呢？有以下几个步骤

4.1 通过show engine innodb status查看死锁日志

1. 2019-09-15 21:47:53 0x1e74  
2. *** (1) TRANSACTION:  
3. TRANSACTION 175942, ACTIVE 17 sec starting index read
4. mysql tables in use 1, locked 1 
5. LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
6. MySQL thread id 19, OS thread handle 7280, query id 1196 localhost 127.0.0.1 root updating
7. ==delete from student where id = 3==
8. *** ==(1) WAITING FOR THIS LOCK TO BE GRANTED:==
9. RECORD LOCKS space id 120 page no 3 n bits 72 index PRIMARY of table `test1`.`student` trx id  175942 lock_mode X locks rec but not gap waiting
10. Record lock, heap no 5 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
11.  0: len 4; hex 80000003; asc     ;;
12.  1: len 6; hex 00000002af2f; asc      /;;
13.  2: len 7; hex 2f000001e3254c; asc /    %L;;
14.  3: len 1; hex 61; asc a;;

15. *** (2) TRANSACTION:
16. TRANSACTION 175941, ACTIVE 50 sec starting index read, thread declared inside InnoDB 5000
17. mysql tables in use 1, locked 1
18. 4 lock struct(s), heap size 1136, 2 row lock(s)
19. MySQL thread id 18, OS thread handle 7796, query id 1197 localhost 127.0.0.1 root updating
20. delete from student where id = 3
21. *** (2) ==HOLDS THE LOCK(S):==
22. RECORD LOCKS space id 120 page no 3 n bits 72 index PRIMARY of table `test1`.`student` trx id 175941 lock mode S locks rec but not gap
23. Record lock, heap no 5 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
24.  0: len 4; hex 80000003; asc     ;;
25.  1: len 6; hex 00000002af2f; asc      /;;
26.  2: len 7; hex 2f000001e3254c; asc /    %L;;
27.  3: len 1; hex 61; asc a;;

28. *** ==(2) WAITING FOR THIS LOCK TO BE GRANTED:==
29. RECORD LOCKS space id 120 page no 3 n bits 72 index PRIMARY of table `test1`.`student` trx id 175941 lock_mode X locks rec but not gap waiting
30. Record lock, heap no 5 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
31.  0: len 4; hex 80000003; asc     ;;
32.  1: len 6; hex 00000002af2f; asc      /;;
33.  2: len 7; hex 2f000001e3254c; asc /    %L;;
34.  3: len 1; hex 61; asc a;;
35. *** WE ROLL BACK TRANSACTION (1)

4.2 死锁日志主要信息介绍

第1行显示第一个事务发生死锁的时间，通过日志中的死锁发生的时间与mysql中的死锁日志时间进行匹配，查询到相应的死锁日志信息
第3行记录第一个事务的编号，可以通过事务编号搜索以往的日志信息
第5行
第6记下第一个事务线程ID，连接主机和连接用户。可以根据连接的主机和用户确定事务来自哪个应用程序。
第9行显第一个事务示正在等待的锁，S表示共享锁，X表示独占锁。
第22行和23行显示第二个事务所持有的锁，这个是第一个事务正在等待的锁

4.3 分析死锁日志

死锁日志分事务1，事务2拆分
找出发生死锁的SQL
找出事务持有什么锁，都在等待什么锁
SQL加锁分析

（1）事务1日志分析

delete from student where id = 3
(1) WAITING FOR THIS LOCK TO BE GRANTED:
 RECORD LOCKS space id 120 page no 3 n bits 72 index PRIMARY of table `test1`.`student` trx id  175942 lock_mode X locks rec but not gap waiting

从日志中可以看出在执行删除操作时，在等待lock_mode X 排他锁

（2）事务2日志分析

20. delete from student where id = 3
21. *** (2) ==HOLDS THE LOCK(S):==
22. RECORD LOCKS space id 120 page no 3 n bits 72 index PRIMARY of table `test1`.`student` trx id 175941 lock mode S locks rec but not gap

上面日志显示正在持有lock mode S独占锁

28. *** ==(2) WAITING FOR THIS LOCK TO BE GRANTED:==
29. RECORD LOCKS space id 120 page no 3 n bits 72 index PRIMARY of table `test1`.`student` trx id 175941 lock_mode X locks rec but not gap waiting
30. Record lock, heap no 5 PHYSICAL RECORD: n_fields 4; compact format; info bits 0

上面日志显示正在等待lock_mode X排他锁

死锁的原因：

事务1首先进行查询，占有共享锁X，
事务2在进行删除操作时，需要等待事务1释放S锁，
事务1再次进行删除操作，有需要等待事务2释放X锁，导致两个事务互斥，造成死锁

4.4 其他排查方式

除了SHOW ENGINE INNODB STATUS输出，您还可以利用binlog，慢日志分析死锁。使用binlog，如果binlog_format = statement，则每个binlog事件都将具有thread_id。只有已提交的事务会记录到binlog中，因此我们能在binlog中查找。当我们知道何时发生死锁，并且我们知道事务在什么时候启动。我们可以在正确的binlog文件上运行mysqlbinlog并查找thread_id的语句，然后分析日志信息。

4.5 mysql相关锁

InnoDB 锁类型思维导图：

（1）共享锁与排他锁：

InnoDB 实现了标准的行级锁，包括两种：共享锁（简称 s 锁）、排它锁（简称 x 锁）
共享锁（S锁）：允许持锁事务读取一行。
排他锁（X锁）：允许持锁事务更新或者删除一行。
如果事务1持有某行的s锁，那么另一个事务2请求该行的锁时，会做如下处理：
事务2请求s锁立即被允许，结事务1和事务2都持有s锁
事务2请求x锁不能被立即允许
如果事务1持有x锁，那么事务2请求x、s锁都不能被立即允许，事务2必须等待事务1释放x 锁才可以，因为X锁与任何的锁都不兼容。

（2）意向锁

意向共享锁( IS 锁)：事务想要获得一张表中某几行的共享锁
意向排他锁( IX 锁)：事务想要获得一张表中某几行的排他锁
比如：事务1在表1上加了S锁后，事务2想要更改某行记录，需要添加IX锁，由于不兼容，所以需要等待S锁释放；如果事务1在表1上加了IS锁，事务2添加的IX锁与IS锁兼容，就可以操作，这就实现了更细粒度的加锁。

（3）记录锁（Record Locks）

记录锁是最简单的行锁，仅仅锁住一行。
记录锁永远都是加在索引上的，即使一个表没有索引，InnoDB也会隐式的创建一个索引，并使用这个索引实施记录锁。
会阻塞其他事务对其插入、更新、删除

记录锁的事务数据（关键词：lock_mode S locks rec but not gap），记录如下：

22. RECORD LOCKS space id 120 page no 3 n bits 72 index PRIMARY of table `test1`.`student` trx id 175941 lock mode S locks rec but not gap
23. Record lock, heap no 5 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
24.  0: len 4; hex 80000003; asc     ;;
25.  1: len 6; hex 00000002af2f; asc      /;;
26.  2: len 7; hex 2f000001e3254c; asc /    %L;;
27.  3: len 1; hex 61; asc a;;

（4）间隙锁（Gap Locks）

间隙锁是一种加在两个索引之间的锁，或者加在第一个索引之前，或最后一个索引之后的间隙。
使用间隙锁锁住的是一个区间，而不仅仅是这个区间中的每一条数据。
间隙锁只阻止其他事务插入到间隙中，他们不阻止其他事务在同一个间隙上获得间隙锁，所以 gap x lock 和 gap s lock 有相同的作用。

（5） Next-Key Locks

Next-key锁是记录锁和间隙锁的组合，它指的是加在某条记录以及这条记录前面间隙上的锁。

（6）插入意向锁（Insert Intention）

插入意向锁是在插入一行记录操作之前设置的一种间隙锁，这个锁释放了一种插入方式的信号，亦即多个事务在相同的索引间隙插入时如果不是插入间隙中相同的位置就不需要互相等待。
假设有索引值4、7，几个不同的事务准备插入5、6，每个锁都在获得插入行的独占锁之前用插入意向锁各自锁住了4、7之间的间隙，但是不阻塞对方因为插入行不冲突。

五、如何避免MySQL死锁

在某些情况下，您可以通过将长事务拆分为较小的事务来大大减少死锁的频率，因此锁定会更快地释放。
在其他情况下，死锁上升是因为两个事务触发相同的数据，在一个或多个表中，具有不同的顺序。然后更改它们以相同的顺序访问数据。这样当事务同时发生时，你会有锁等待而不是死锁。
更改表模式，例如删除外键约束以分离两个表，或添加索引以最小化扫描和锁定的行。
如果存在间隙锁，您可以将事务隔离级别更改为为会话或事务提交读取以避免它。