遇到MySQL死锁问题，我们应该怎么排查分析呢？之前线上出现一个insert on duplicate死锁问题，本文将基于这个死锁问题，分享排查分析过程，希望对大家有帮助。
2.1 表结构
CREATE TABLE `song_rank` ( `id` int(11) NOT NULL AUTO_INCREMENT, `songId` int(11) NOT NULL, `weight` int(11) NOT NULL DEFAULT '0', PRIMARY KEY (`id`), UNIQUE KEY `songId_idx` (`songId`) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2.2 隔离级别
mysql> select @@tx_isolation; +-----------------+ | @@tx_isolation | +-----------------+ | REPEATABLE-READ | +-----------------+ 1 row in set, 1 warning (0.00 sec)
2.3 数据库版本
+------------+ | @@version | +------------+ | 5.7.21-log | +------------+ 1 row in set (0.00 sec)
2.4 关闭自动提交
mysql> select @@autocommit; +--------------+ | @@autocommit | +--------------+ | 1 | +--------------+ 1 row in set (0.00 sec) mysql> set autocommit=0; Query OK, 0 rows affected (0.00 sec) mysql> select @@autocommit; +--------------+ | @@autocommit | +--------------+ | 0 | +--------------+ 1 row in set (0.00 sec)
2.5 表中的数据
mysql> select * from song_rank; +----+--------+--------+ | id | songId | weight | +----+--------+--------+ | 1 | 10 | 30 | | 2 | 20 | 30 | +----+--------+--------+ 2 rows in set (0.01 sec)
2.6 死锁案发原因
并发环境下，执行insert into … on duplicate key update…导致死锁
2.7 死锁模拟复现
（1）事务一执行
mysql> begin; //第一步 Query OK, 0 rows affected (0.00 sec) mysql> insert into song_rank(songId,weight) values(15,100) on duplicate key update weight=weight+1; //第二步 Query OK, 1 row affected (0.00 sec) mysql> rollback; //第七步 Query OK, 0 rows affected (0.00 sec)
（2）事务二执行
mysql> begin; //第三步 Query OK, 0 rows affected (0.00 sec) mysql> insert into song_rank(songId,weight) values(16,100) on duplicate key update weight=weight+1; // 第四步 Query OK, 1 row affected (40.83 sec)
（3）事务三执行
mysql> begin; //第五步 Query OK, 0 rows affected (0.00 sec) mysql> insert into song_rank(songId,weight) values(18,100) on duplicate key update weight=weight+1; //第六步
（4）事务一，事务二，事务三执行
死锁浮出水面：
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
遇到死锁问题时，我们应该怎么处理呢？有以下几个步骤
3.1 查看死锁日志
当数据库发生死锁时，可以通过以下命令获取死锁日志：
show engine innodb status;
上面例子insert on duplicate死锁问题的日志如下：
*** (1) TRANSACTION: TRANSACTION 27540, ACTIVE 19 sec inserting mysql tables in use 1, locked 1 LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1 MySQL thread id 23, OS thread handle 14896, query id 582 localhost ::1 root update insert into song_rank(songId,weight) values(18,100) on duplicate key update weight=weight+1 *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 116 page no 4 n bits 72 index songId_idx of table `test2`.`song_rank` trx id 27540 lock_mode X locks gap before rec insert intention waiting Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 4; hex 80000014; asc ;; 1: len 4; hex 80000002; asc ;; *** (2) TRANSACTION: TRANSACTION 27539, ACTIVE 41 sec inserting, thread declared inside InnoDB 1 mysql tables in use 1, locked 1 4 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 1 MySQL thread id 22, OS thread handle 6976, query id 580 localhost ::1 root update insert into song_rank(songId,weight) values(16,100) on duplicate key update weight=weight+1 *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 116 page no 4 n bits 72 index songId_idx of table `test2`.`song_rank` trx id 27539 lock_mode X locks gap before rec Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 4; hex 80000014; asc ;; 1: len 4; hex 80000002; asc ;; *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 116 page no 4 n bits 72 index songId_idx of table `test2`.`song_rank` trx id 27539 lock_mode X locks gap before rec insert intention waiting Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 4; hex 80000014; asc ;; 1: len 4; hex 80000002; asc ;;
3.2 分析死锁日志
如何分析死锁日志呢? 分享一下我的思路
（1）事务1日志分析
从日志我们可以看到事务1正在执行的SQL为：
insert into song_rank(songId,weight) values(18,100) on duplicate key update weight=weight+1
该条语句正在等待索引songId_idx的插入意向排他锁：
lock_mode X locks gap before rec insert intention waiting
（2）事务2日志分析
从日志我们可以看到事务2正在执行的SQL为：
insert into song_rank(songId,weight) values(16,100) on duplicate key update weight=weight+1
该语句持有一个索引songId_idx的间隙锁：
lock_mode X locks gap before rec
该条语句正在等待索引songId_idx的插入意向排他锁：
lock_mode X locks gap before rec insert intention waiting
（3）锁相关概念补充（附）
考虑到有些读者可能对上面insert intention锁等不太熟悉，所以这里这里补一小节锁相关概念。 官方文档
InnoDB 锁类型思维导图：
我们主要介绍一下兼容性以及锁模式类型的锁
① 共享锁与排他锁：
InnoDB 实现了标准的行级锁，包括两种：共享锁（简称 s 锁）、排它锁（简称 x 锁）。
如果事务 T1 持有行 r 的 s 锁，那么另一个事务 T2 请求 r 的锁时，会做如下处理：
如果 T1 持有 r 的 x 锁，那么 T2 请求 r 的 x、s 锁都不能被立即允许，T2 必须等待T1释放 x 锁才可以，因为X锁与任何的锁都不兼容。
② 意向锁
比如：事务1在表1上加了S锁后，事务2想要更改某行记录，需要添加IX锁，由于不兼容，所以需要等待S锁释放；如果事务1在表1上加了IS锁，事务2添加的IX锁与IS锁兼容，就可以操作，这就实现了更细粒度的加锁。
InnoDB存储引擎中锁的兼容性如下表：
③ 记录锁（Record Locks）
记录锁的事务数据（关键词：lock_mode X locks rec but not gap），记录如下：
RECORD LOCKS space id 58 page no 3 n bits 72 index `PRIMARY` of table `test`.`t` trx id 10078 lock_mode X locks rec but not gap Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0 0: len 4; hex 8000000a; asc ;; 1: len 6; hex 00000000274f; asc 'O;; 2: len 7; hex b60000019d0110; asc ;;
④ 间隙锁（Gap Locks）
⑤ Next-Key Locks
⑥ 插入意向锁（Insert Intention）
事务数据类似于下面：
RECORD LOCKS space id 31 page no 3 n bits 72 index `PRIMARY` of table `test`.`child` trx id 8731 lock_mode X locks gap before rec insert intention waiting Record lock, heap no 3 PHYSICAL RECORD: n_fields 3; compact format; info bits 0 0: len 4; hex 80000066; asc f;; 1: len 6; hex 000000002215; asc " ;; 2: len 7; hex 9000000172011c; asc r ;;...
（4）SQL加锁分析
通过分析死锁日志，我们可以找到发生死锁的SQL，以及相关等待的锁，我们再对对应的SQL进行加锁分析，其实问题就迎刃而解了。
OK，我们回到对应的SQL，insert into song_rank(songId,weight) values(16,100) on duplicate key update weight=weight+1 执行过程到底加了什么锁呢？
① insert加锁策略
insert语句会对插入的这条记录加排他记录锁，在加记录锁之前还会加一种 GAP 锁，叫做插入意向(insert intention)锁，如果出现唯一键冲突，还会加一个共享记录(S)锁。
（SQL加锁分析非常重要，在这里给大家推荐一篇文章，讲的非常好，解决死锁之路 - 常见 SQL 语句的加锁分析)
② insert on duplicate key加锁验证
为了验证一下insert on duplicate key加锁情况，我们拿上面demo的事务1和2在走一下流程。
事务1：
mysql> begin; //第一步 Query OK, 0 rows affected (0.00 sec) mysql> insert into song_rank(songId,weight) values(15,100) on duplicate key update weight=weight+1; //第二步 Query OK, 1 row affected (0.00 sec)
事务2（另开窗口）：
mysql> begin; //第三步 Query OK, 0 rows affected (0.00 sec) mysql> insert into song_rank(songId,weight) values(16,100) on duplicate key update weight=weight+1; // 第四步
使用show engine innodb status查看当前锁请求信息，如图：
事务2持有：IX锁（表锁），gap x锁，insert intention lock（在等待事务1的gap锁）
所以，insert on duplicate 执行过程会上这三把锁。
（5）死锁原因分析
回归到本文开头介绍的死锁案发模拟现场(事务1,2,3)以及死锁日志现场,
案发后事务1的锁：
案发后事务2的锁：
案发复原路线：
首先，执行事务1执行: begin; insert into song_rank(songId,weight) values(15,100) on duplicate key update weight=weight+1; 会获得 gap锁（10,20），insert intention lock（插入意向锁）
接着，事务2执行: begin; insert into song_rank(songId,weight) values(16,100) on duplicate key update weight=weight+1; 会获得 gap锁（10,20），同时等待事务1的insert intention lock（插入意向锁）。
再然后，事务3执行： begin; insert into song_rank(songId,weight) values(18,100) on duplicate key update weight=weight+1; 会获得 gap锁（10,20），同时等待事务1的insert intention lock（插入意向锁）。
最后，事务1回滚（rollback），释放插入意向锁，导致事务2,3同时持有gap锁，等待insert intention锁，死锁形成！
锁模式兼容矩阵（横向是已持有锁，纵向是正在请求的锁）：
兼容性 Gap Insert Intention Record Next-Key Gap 兼容 兼容 兼容 兼容 Insert Intention 冲突 兼容 兼容 冲突 Record 兼容 兼容 冲突 冲突 Next-Key 兼容 兼容 冲突 冲突 这是MySql5.7的一个bug
4.1 把insert on duplicate改为insert
try{
insert();
}catch(DuplicateKeyException e){
update();
}
因为insert不会加gap锁，所以可以避免该问题。
4.2 更改MySql版本
既然这是MySql5.7的一个bug，那么可以考虑更改Mysql版本。
4.3 尽量减少使用unique index
gap锁跟索引有关，并且unique key 和foreign key会引起额外的index检查，需要更大的开销，所以我们尽量减少使用不必要的索引。
本文介绍了MySql5.7死锁的一个bug。我们应该怎样去排查死锁问题呢？
京公网安备 11010802035086号