【Java场景题】MySQL死锁排查

大家好，今天XiXi给大家分享一个MySQL死锁排查的实验，文章主要有：

• 通过show engine innodb status，查看最近一次死锁信息
• 开启innodb_print_all_deadlocks，在错误日志中能够记录所有死锁信息
• 通过解析binlog日志定位死锁事务全部SQL信息
• 由于binlog中不会记录查询语句，无法排查因select for update导致的死锁SQL信息，经过研究后，采用通过开启general_log的方式解决

表结构创建

CREATE DATABASE deadlock;
use deadlock;
CREATE TABLE `test_deadlock` (`id` INT(11) NOT NULL AUTO_INCREMENT,`num` INT(11) DEFAULT NULL,PRIMARY KEY (`id`)
) ENGINE=InnoDB;INSERT INTO `test_deadlock` VALUES (1, 1), (2, 2);

死锁场景构建

事务1	事务2
use deadlock;	use deadlock;
start transaction;
	start transaction;
select * from test_deadlock where id = 1 for update;（持有记录1的锁）
	select * from test_deadlock where id = 2 for update;（持有记录2的锁）
select * from test_deadlock where id = 2 for update;（请求并等待记录1的锁）
	select * from test_deadlock where id = 1 for update;（死锁产生，自动取消事务）
commit;

事务1命令执行

事务2命令执行

查看最近一次死锁信息

show engine innodb status;

局限性：仅可以查看最近一次死锁的信息

我们可以借助AI分析一下上面日志：

• 事务2070：正在等待id=2的X锁（排他锁）
• 事务2071：已持有id=2的X锁，同时等待id=1的X锁
• 最终导致两个事务互相等待对方的锁资源，形成循环等待的死锁

记录所有死锁信息

通过show engine innodb status，我们可以查到最近一次死锁信息，但是我们想得到所有死锁的信息。于是可以开启innodb_print_all_deadlocks，所有的死锁信息会记录到错误日志。

-- 查看错误日志路径
show variables like 'log_error';
-- 开启死锁日志打印
set global innodb_print_all_deadlocks=ON;
-- 执行死锁案例后，查看错误日志
tail -n35 /var/log/mysqld.log

通过binlog查看死锁相关SQL

我们现在已经可以知道数据库的一些死锁信息，但是死锁信息不是很直观，我们想知道死锁的相关事务到底执行了哪些SQL，于是我们可以开启binlog，通过binlog去分析。

开启binlog日志

• 配置/etc/my.cnf
• 重启MySQL服务

MySQL8的binlog是默认开启的，且格式是：ROW

[mysqld]
log_bin=binlog       # 启用binlog,且文件名为binlog，默认路径为MySQL数据目录
binlog_format = ROW       # 设置格式为ROW
server-id = 1             # 主从复制需唯一（MySQL 5.7以下必须配置）
expire_logs_days = 7      # 日志保留7天
max_binlog_size = 500M    # 单个日志文件最大500MB

解析binlog日志

mysqlbinlog /var/lib/mysql/binlog.000002 -u root -p --base64-output=decode-rows -v

实验做到这里发现问题：binlog会记录update、insert语句，但是对于查询语句（select for update）不会记录在binlog中的。因此我实验中死锁事务的SQL并没有在binlog中。

死锁事务完整SQL查看方式

对于select for update语句binlog是不记录的，所以想要看到死锁事务的全部SQL，这里XiXi想到用如下方式

开启general_log

set global general_log = 'ON';
show variables like '%general_log%';

执行死锁案例

见死锁场景构建案例

查看死锁信息

• 事务1：线程id=54
• 事务2：线程id=9

在general_log中寻找

awk '$2 == 54 || $2 == 9' xixi.log

总结

好的，MySQL死锁的排查实验就分享到这里。欢迎批评指正。