前言
遇到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> inse