MySQL死锁问题排查

概述

在开发中经常用到的mysql数据库，mysql作为开源的数据库有很多优越的地方；大家可以不了解其中的原理，但是一定需要具备排查问题的基本功，例如死锁问题。

死锁的产生

在这里我们简单模拟一个死锁

表结构：

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 |
+-----------------+

关闭自动提交

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 |
+----+--------+--------+

死锁模拟

第一个客户端执行

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) on duplicate key update  weight=weight+1; //第六步

死锁查看

1、查看死锁日志

show engine innodb status;

在LATEST DETECTED DEADLOCK栏中可以看到死锁的日志
2、正在发生的死锁查看

// 查看产生的锁
select * from information_schema.innodb_locks;
// 查看持有的锁
select * from sys.innodb_lock_waits

3、设置锁的等待时间

// 查看锁等待时间
show variables like 'innodb_lock_wait_timeout';
// 设置锁超时等待时间是10秒
set global innodb_lock_wait_timeout=10;

总结

从上面的案例我们可以看出，mysql是默认的锁超时时间是50秒，当发生死锁后，事务等待超过50秒后会抛出一个错误；