前言

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

死锁案发还原

表结构：
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;
隔离级别：

mysql> select @@tx_isolation; +-----------------+ | @@tx_isolation  | +-----------------+ | REPEATABLE-READ | +-----------------+ 1 row in set, 1 warning (0.00 sec)

数据库版本：

+------------+ | @@version  | +------------+ | 5.7.21-log | +------------+ 1 row in set (0.00 sec)

关闭自动提交：

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)

表中的数据：

mysql> select * from song_rank; +----+--------+--------+ | id | songId | weight | +----+--------+--------+ |  1 |     10 |     30 | |  2 |     20 |     30 | +----+--------+--------+ 2 rows in set (0.01 sec)

死锁案发原因：

并发环境下，执行insert into … on duplicate key update…导致死锁

死锁模拟复现：

事务一执行：

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) 

事务二执行：

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) 

事务三执行：

mysql> begin;  //第五步 Query OK, 0 rows affected (0.00 sec)  mysql> insert into  song_rank(songId,weight) values(18,100