Mysql死锁如何排查？insert on duplicate死锁排查过程分析 _Mysql死锁

01 前言遇到MySQL死锁问题，我们应该怎么排查分析呢？之前线上出现一个insert on duplicate死锁问题，本文将基于这个死锁问题，分享排查分析过程，希望对大家有帮助。

文章插图

02 死锁案发还原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 transaction03 死锁破案排查分析遇到死锁问题时，我们应该怎么处理呢？有以下几个步骤
3.1 查看死锁日志
当数据库发生死锁时，可以通过以下命令获取死锁日志：
show engine innodb status;上面例子insert on duplicate死锁问题的日志如下：
*** (1) TRANSACTION:TRANSACTION 27540, ACTIVE 19 sec insertingmysql tables in use 1, locked 1LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1MySQL thread id 23, OS thread handle 14896, query id 582 localhost ::1 root updateinsert 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 waitingRecord 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 1mysql tables in use 1, locked 14 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 1MySQL thread id 22, OS thread handle 6976, query id 580 localhost ::1 root updateinsert 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 recRecord 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 waitingRecord 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 分析死锁日志
如何分析死锁日志呢? 分享一下我的思路