MySQL打印死锁日志的方法步骤

目录
  • 前言:
  • 1.手动打印死锁日志
  • 2.自动保存死锁日志
  • 总结:

前言:

在 MySQL 运维过程中,难免会遇到 MySQL 死锁的情况,一旦线上业务日渐复杂,各种业务操作之间往往会产生锁冲突,有些会导致死锁异常。这种死锁异常一般要在特定时间特定数据和特定业务操作才会复现,有时候处理起来毫无头绪,一般只能从死锁日志下手。本篇文章我们一起来看下 MySQL 的死锁日志。

1.手动打印死锁日志

当业务发生死锁时,首先是线上错误日志报警发现死锁异常,也会提示一些堆栈信息,然后会反馈到数据库层面进行排查。我们一般会在命令行执行 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 不会显示所有持有和等待的锁,但死锁日志也显示了相关的信息来帮你确定,排查死锁发生的索引,这对于你确定能否避免死锁有较大的价值。

2.自动保存死锁日志

从上面内容我们知道 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 参数可以自动将死锁信息输出到错误日志中,有助于我们及时发现并处理死锁异常。

到此这篇关于MySQL打印死锁日志的方法步骤的文章就介绍到这了,更多相关MySQL打印死锁日志内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们!

(0)

相关推荐

  • 细说MySQL死锁与日志二三事

    最近线上 MySQL 接连发生了几起数据异常,都是在凌晨爆发,由于业务场景属于典型的数据仓库型应用,白天压力较小无法复现.甚至有些异常还比较诡异,最后 root cause 分析颇费周折.那实际业务当中咱们如何能快速的定位线上 MySQL 问题,修复异常呢?下文我会根据两个实际 case,分享下相关的经验与方法. Case1:部分数据更新失败 某天渠道同学反馈某报表极个别渠道数据为 0,大部分渠道数据正常.这个数据是由一个统计程序每天凌晨例行更新的,按理来说,要么全部正常,要么全部失败,那会是什

  • MySQL Innodb表导致死锁日志情况分析与归纳

    案例描述在定时脚本运行过程中,发现当备份表格的sql语句与删除该表部分数据的sql语句同时运行时,mysql会检测出死锁,并打印出日志.两个sql语句如下:(1)insert into backup_table select * from source_table(2)DELETE FROM source_table WHERE Id>5 AND titleWeight<32768 AND joinTime<'$daysago_1week'teamUser表的表结构如下:PRIMARY

  • MySQL打印死锁日志的方法步骤

    目录 前言: 1.手动打印死锁日志 2.自动保存死锁日志 总结: 前言: 在 MySQL 运维过程中,难免会遇到 MySQL 死锁的情况,一旦线上业务日渐复杂,各种业务操作之间往往会产生锁冲突,有些会导致死锁异常.这种死锁异常一般要在特定时间特定数据和特定业务操作才会复现,有时候处理起来毫无头绪,一般只能从死锁日志下手.本篇文章我们一起来看下 MySQL 的死锁日志. 1.手动打印死锁日志 当业务发生死锁时,首先是线上错误日志报警发现死锁异常,也会提示一些堆栈信息,然后会反馈到数据库层面进行排查

  • 开启MySQL的binlog日志的方法步骤

    binlog 就是binary log,二进制日志文件,这个文件记录了mysql所有的dml操作.通过binlog日志我们可以做数据恢复,做主住复制和主从复制等等.对于开发者可能对binlog并不怎么关注,但是对于运维或者架构人员来讲是非常重要的. 如何开启mysql的binlog日志呢? 在my.inf主配置文件中直接添加三行 log_bin=ON log_bin_basename=/var/lib/mysql/mysql-bin log_bin_index=/var/lib/mysql/my

  • Springboot2 配置AOP日志的方法步骤

    Spring boot2 配置AOP前置增强,后置增强,异常增强,环绕增强,最终增强 关于AOP切面相关具体概念不做过多阐述(概念弄懂有利于理解思想),这是配置AOP的各种增强日志,解决日志嵌套在业务代码的麻烦和不科学 先来个Git demo项目压压惊: https://github.com/zhang-xiao-xiang/boot-aop  (有的更新了一些) 1pom依赖(这里使用log4j2作为日志框架,因为比log4j或者其他日志框架,它效率更高,功能更加强大) <!-- 引入log4

  • springboot+mybatis配置控制台打印sql日志的方法

    此处项目环境为简单的springboot+mybatis环境.可查看到上一篇文章搭建的简单springboot+mybatis的项目 想要控制台打印sql日志. 只需要在resources目录下添加logback.xml文件即可 logback内容如下.直接复制即可 <?xml version="1.0" encoding="UTF-8"?> <configuration debug="false"> <!--定义日

  • Mysql双主搭建的方法步骤

    目录 1. Mysql binlog参数配置 2. Mysql binlog查看详细内容 3. Mysql双主搭建 4. Mysql双主解决数据回环 4.1 双主同步测试一 4.2 双主同步测试二 4.3 双主同步测试三 1. Mysql binlog参数配置 log-bin=mysql-bin 打开二进制日志功能,默认在datadir下 binlog-ignore-db binlog-ignore-db=mysql binlog-ignore-db=information_schema bin

  • SpringBoot中使用AOP打印接口日志的方法

    前言 AOP 是 Aspect Oriented Program (面向切面)的编程的缩写.他是和面向对象编程相对的一个概念.在面向对象的编程中,我们倾向于采用封装.继承.多态等概念,将一个个的功能在对象中来实现.但是,我们在实际情况中也发现,会有另外一种需求就是一类功能在很多对象的很多方法中都有需要.例如有一些对数据库访问的方法有事务管理的需求,有很多方法中要求打印日志.按照面向对象的方式,那么这些相同的功能要在很多地方来实现或者在很多地方来调用.这就非常繁琐并且和这些和业务不相关的需求耦合太

  • mysql清除log-bin日志的方法

    前一阵子工作项目上的事情忙的焦头烂额,最近要进行部门调整将要去做新的项目.又要学习很多新的知识了,还是很兴奋激动的.今天下班回来查看了一下VPS状态,发现VPS的空间只剩下了1G多!第一反应是被入侵了,但是看了一下log并没有发现什么异常的登录,加上平时基本都是用私钥免密码登录的VPS,别入侵的可能也不是很大.那我就很疑惑了,因为系统文件占用应该也就3G多,我平时并没有在VPS放过什么大文件,不应该一下子少那么多空间.于是开始一番du查找终于找到了罪魁祸首!原来是mysql的log文件导致的.

  • mysql binlog(二进制日志)查看方法

    例如,在创建新表或更新现有表上的数据时,这些事件将存储在mysql binlog中,也就是MySQL数据库的二进制日志. 二进制日志在MySQL复制中非常有用,主服务器将数据从二进制日志发送到远程服务器. 当您在MySQL中执行任何类型的恢复操作时,您还将处理二进制日志文件. mysqlbinlog命令用于以可读的用户友好格式查看二进制日志的内容. 那么我们怎么获取查看mysql binlog(二进制日志)? 下面给大家介绍获取当前二进制日志列表的命令方法. 从mysql执行以下show bin

  • 查看MySQL的错误日志的方法

    MySQL的错误信息是在data目录下的,且文件名为<hostname>.err(<hostname>指的是主机名),但由于每个人安装的环境不一样,或你忘记了data目录的所在位置,你可以通过下面方法查找. #hostname //获得主机名 <hostname> #find / -name <hostname>.err ..... #cd ... #vi <hostname>.err

  • Linux中 MySQL 授权远程连接的方法步骤

    说明:当别的机子(IP )通过客户端的方式在没有授权的情况下是无法连接 MySQL 数据库的,如果需要远程连接 Linux 系统上的 MySQL 时,必须为其 IP 和 具体用户 进行 授权 .一般 root 用户不会提供给开发者.如:使用 Windows 上的 SQLyog 图形化管理工具连接 Linux 上的 MySQL 数据库,必须先对其进行授权. 1.在虚拟机中使用 root 用户登录 mysql 数据库 mysql -u root -p 说明:root 用户密码一般设置为 root 2

随机推荐