共计 5502 个字符,预计需要花费 14 分钟才能阅读完成。
导读 | 本文主要介绍了 MySQL 打印死锁日志的方法步骤,文中通过示例代码介绍的非常详细,具有一定的参考价值,感兴趣的小伙伴们可以参考一下 |
在 MySQL 运维过程中,难免会遇到 MySQL 死锁的情况,一旦线上业务日渐复杂,各种业务操作之间往往会产生锁冲突,有些会导致死锁异常。这种死锁异常一般要在特定时间特定数据和特定业务操作才会复现,有时候处理起来毫无头绪,一般只能从死锁日志下手。本篇文章我们一起来看下 MySQL 的死锁日志。
当业务发生死锁时,首先是线上错误日志报警发现死锁异常,也会提示一些堆栈信息,然后会反馈到数据库层面进行排查。我们一般会在命令行执行 show engine innodb status\G 来输出死锁日志,\G 的作用是将查询到的结果,每行显示一个字段和字段值,方便查看。
show engine innodb status 是 MySQL 提供的一个用于查看 innodb 引擎系统信息的工具。它会输出大量的内部信息,内容分为很多小段,每一段对应 innodb 存储引擎不同部分的信息,其中 LATEST DETECTED DEADLOCK 部分显示的最近一次的死锁信息。
下面我们手动制造一次死锁,来看一下死锁日志相关信息:
------------------------
LATEST DETECTED DEADLOCK
------------------------
2021-11-10 17:03:10 0x7fb040672700
*** (1) TRANSACTION:
TRANSACTION 46913, ACTIVE 142 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 1
MySQL thread id 2997198, OS thread handle 140394973071104, query id 9145673 localhost root updating
update test_tb set stu_name = 'lisi' where stu_id = 1006
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 224 page no 4 n bits 80 index uk_stu_id of table `testdb`.`test_tb` trx id 46913 lock_mode X locks rec but not gap waiting
Record lock, heap no 7 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 800003ee; asc ;;
1: len 4; hex 80000006; asc ;;
*** (2) TRANSACTION:
TRANSACTION 46914, ACTIVE 103 sec starting index read
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 1
MySQL thread id 2997201, OS thread handle 140394971473664, query id 9145681 localhost root updating
update test_tb set age = 21 where stu_id = 1005
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 224 page no 4 n bits 80 index uk_stu_id of table `testdb`.`test_tb` trx id 46914 lock_mode X locks rec but not gap
Record lock, heap no 7 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 800003ee; asc ;;
1: len 4; hex 80000006; asc ;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 224 page no 4 n bits 80 index uk_stu_id of table `testdb`.`test_tb` trx id 46914 lock_mode X locks rec but not gap waiting
Record lock, heap no 6 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 800003ed; asc ;;
1: len 4; hex 80000005; asc ;;
*** WE ROLL BACK TRANSACTION (2)
# 以上为原文 下面增加个人分析
------------------------
LATEST DETECTED DEADLOCK
------------------------
2021-11-10 17:03:10 0x7fb040672700 #这里显示了最近一次发生死锁的日期和时间
*** (1) TRANSACTION: #死锁相关的第一个事务
TRANSACTION 46913, ACTIVE 142 sec starting index read
#这行表示事务 id 为 46913,事务处于活跃状态 142s,starting index read 表示正在使用索引读取数据行
mysql tables in use 1, locked 1
#这行表示该事务正在使用 1 个表,且涉及锁的表有 1 个
LOCK WAIT 4 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 1
#这行表示在等待 4 把锁,占用内存 1136 字节,涉及 3 行记录
MySQL thread id 2997198, OS thread handle 140394973071104, query id 9145673 localhost root updating
#这行表示该事务的线程 ID 信息,操作系统句柄信息,连接来源、用户
update test_tb set stu_name = 'lisi' where stu_id = 1006
#这行表示事务执行的最后一条 SQL 信息
*** (1) WAITING FOR THIS LOCK TO BE GRANTED: #事务 1 想要获取的锁
RECORD LOCKS space id 224 page no 4 n bits 80 index uk_stu_id of table `testdb`.`test_tb` trx id 46913 lock_mode X locks rec but not gap waiting
#这行信息表示等待的锁是一个 record lock,空间 id 是 224,页编号为 4,大概位置在页的 80 位处,锁发生在表 testdb.test_tb 的 uk_stu_id 索引上,是一个 X 锁,但是不是 gap lock,waiting 表示正在等待锁
Record lock, heap no 7 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 800003ee; asc ;;
1: len 4; hex 80000006; asc ;;
*** (2) TRANSACTION: #死锁相关的第一个事务
TRANSACTION 46914, ACTIVE 103 sec starting index read
#这行表示事务 2 的 id 为 46914,事务处于活跃状态 103s
mysql tables in use 1, locked 1
#正在使用 1 个表,涉及锁的表有 1 个
4 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 1
#涉及 4 把锁,3 行记录
MySQL thread id 2997201, OS thread handle 140394971473664, query id 9145681 localhost root updating
#事务 2 的线程 ID 信息,操作系统句柄信息,连接来源、用户
update test_tb set age = 21 where stu_id = 1005
#第二个事务的 SQL
*** (2) HOLDS THE LOCK(S): # 事务 2 持有的锁 正是事务 1 想要获取的锁
RECORD LOCKS space id 224 page no 4 n bits 80 index uk_stu_id of table `testdb`.`test_tb` trx id 46914 lock_mode X locks rec but not gap
Record lock, heap no 7 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 800003ee; asc ;;
1: len 4; hex 80000006; asc ;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 224 page no 4 n bits 80 index uk_stu_id of table `testdb`.`test_tb` trx id 46914 lock_mode X locks rec but not gap waiting
Record lock, heap no 6 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 800003ed; asc ;;
1: len 4; hex 80000005; asc ;;
#上面这部分是事务二正在等待的锁,从信息上看,等待的是同一个表,同一个索引,同一个 page 上的 record lock X 锁,但是 heap no 位置不同,即不同的行上的锁
*** WE ROLL BACK TRANSACTION (2) #表示事务 2 被回滚
从死锁日志中可以看到关联的两个事务相关信息,当一个事务持有了其他事务需要的锁,同时又想获得其他事务持有的锁时,等待关系上就会产生循环,Innodb 不会显示所有持有和等待的锁,但死锁日志也显示了相关的信息来帮你确定,排查死锁发生的索引,这对于你确定能否避免死锁有较大的价值。
从上面内容我们知道 MySQL 的死锁可以通过 show engine innodb status 来查看,但是这个命令需要手动执行并且只能显示最新的一条死锁,该方式无法完全捕获到系统发生的死锁信息。那有没有办法记录所有的死锁日志呢,我们来看下 MySQL 的系统参数。
MySQL 系统内部提供一个 innodb_print_all_deadlocks 参数,该参数默认是关闭的,开启后可以将死锁信息自动记录到 MySQL 的错误日志中。下面我们来看下这个参数的作用:
# 查看参数是否开启
mysql> show variables like 'innodb_print_all_deadlocks';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| innodb_print_all_deadlocks | OFF |
+----------------------------+-------+
# 开启 innodb_print_all_deadlocks, 此参数是全局参数,可以动态调整。记得要加入到配置文件中
mysql> set global innodb_print_all_deadlocks = 1;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like 'innodb_print_all_deadlocks';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| innodb_print_all_deadlocks | ON |
+----------------------------+-------+
建议将 innodb_print_all_deadlocks 参数设置为 1,这样每次发生死锁后,系统会自动将死锁信息输出到错误日志中,需要注意的是打开此参数后,只会记录死锁部分信息而不会记录 innodb 其他相关信息,即只会记录 show engine innodb status 中的 LATEST DETECTED DEADLOCK 部分。
其实 InnoDB 存储引擎还提供有 InnoDB Monitor 监视器,可以定期将 InnoDB 的状态信息输出到错误日志中,主要由 innodb_status_output 和 innodb_status_output_locks 参数控制,这两个系统变量是用来启用标准 InnoDB 监控和 InnoDB 锁监控的,开启后会将监控结果输出错误日志中,大约每隔 15 秒产生一次输出,输出内容与 show engine innodb status 一致。不过这会导致错误日志暴增,一般不建议开启这两个参数。
本篇文章介绍了 MySQL 死锁日志的获取方法,发生死锁后,可以根据死锁日志还获取相关信息。开启 innodb_print_all_deadlocks 参数可以自动将死锁信息输出到错误日志中,有助于我们及时发现并处理死锁异常。
![post-qrcode](https://www.xgss.net/wp-content/uploads/2023/07/二维码-微信公众号256x256.jpg)