【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事务2use 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 f重启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死锁的排查实验就分享到这里。欢迎批评指正。