首页 > 数据库 >MySQL死锁日志

MySQL死锁日志

时间:2024-02-19 17:46:38浏览次数:43  
标签:lock hex len t1 asc 死锁 MySQL 日志 id

MySQL死锁日志

MySQL的死锁可以通过show engine innodb status\G;来查看,
最近的死锁信息在LATEST DETECTED DEADLOCK下面。
但是这种方式只能显示最新的一条死锁信息,该方式无法完全捕获到系统发生的死锁信息。

MySQL 系统内部提供一个 innodb_print_all_deadlocks 参数,该参数默认是关闭的,
开启后可以将死锁信息自动记录到 MySQL 的错误日志中。下面我们来看下这个参数的作用:

# 查看参数是否开启
mysql> show variables like 'innodb_print_all_deadlocks';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| innodb_print_all_deadlocks | OFF   |
+----------------------------+-------+
1 row in set (0.00 sec)

# 开启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    |
+----------------------------+-------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
查看错误日志文件放在哪里


mysql> show variables like 'log_error%';
+---------------------+---------------------+
| Variable_name       | Value               |
+---------------------+---------------------+
| log_error           | /var/log/mysqld.log |
| log_error_verbosity | 3                   |
+---------------------+---------------------+
2 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
也可以查看my.cnf的log-error属性

新建表

CREATE TABLE t1 (
  id int(11) NOT NULL,
  a int(11) DEFAULT NULL,
  b int(11) DEFAULT NULL,
  PRIMARY KEY (id),
  KEY a (a)
) ENGINE=InnoDB;
insert into t1(id,a,b)values(5,5,5),(10,10,10);
1
2
3
4
5
6
7
8
构造死锁情况

A    B
t1    update t1 set b = b + 100 where id = 5;    update t1 set b = b + 200 where id = 10;
t2        update t1 set b = b + 222 where id = 5;
t3    update t1 set b = b + 111 where id = 10;    
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction    
使用show engine innodb status\G;查看

找到LATEST DETECTED DEADLOCK

------------------------
LATEST DETECTED DEADLOCK
------------------------
2023-03-10 14:57:50 0x7f5c285a3700
*** (1) TRANSACTION:
TRANSACTION 1399, ACTIVE 20 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 36, OS thread handle 140033790449408, query id 647 localhost root updating
update t1 set b = b + 222 where id = 5
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 25 page no 3 n bits 72 index PRIMARY of table `test`.`t1` trx id 1399 lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 80000005; asc     ;;
 1: len 6; hex 000000000576; asc      v;;
 2: len 7; hex 4c000001580110; asc L   X  ;;
 3: len 4; hex 80000005; asc     ;;
 4: len 4; hex 8000008a; asc     ;;

*** (2) TRANSACTION:
TRANSACTION 1398, ACTIVE 34 sec starting index read
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 35, OS thread handle 140033790719744, query id 648 localhost root updating
update t1 set b = b + 111 where id = 10
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 25 page no 3 n bits 72 index PRIMARY of table `test`.`t1` trx id 1398 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 80000005; asc     ;;
 1: len 6; hex 000000000576; asc      v;;
 2: len 7; hex 4c000001580110; asc L   X  ;;
 3: len 4; hex 80000005; asc     ;;
 4: len 4; hex 8000008a; asc     ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 25 page no 3 n bits 72 index PRIMARY of table `test`.`t1` trx id 1398 lock_mode X locks rec but not gap waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 8000000a; asc     ;;
 1: len 6; hex 000000000577; asc      w;;
 2: len 7; hex 4d000001590110; asc M   Y  ;;
 3: len 4; hex 8000000a; asc     ;;
 4: len 4; hex 80000107; asc     ;;

*** WE ROLL BACK TRANSACTION (2)
------------
TRANSACTIONS
------------
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
也可以通过查看错误日志找到死锁信息,

2023-03-10T06:57:50.483512Z 35 [Note] InnoDB: Transactions deadlock detected, dumping detailed information.
2023-03-10T06:57:50.484236Z 35 [Note] InnoDB:
*** (1) TRANSACTION:

TRANSACTION 1399, ACTIVE 20 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 36, OS thread handle 140033790449408, query id 647 localhost root updating
update t1 set b = b + 222 where id = 5
2023-03-10T06:57:50.484283Z 35 [Note] InnoDB: *** (1) WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 25 page no 3 n bits 72 index PRIMARY of table `test`.`t1` trx id 1399 lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 80000005; asc     ;;
 1: len 6; hex 000000000576; asc      v;;
 2: len 7; hex 4c000001580110; asc L   X  ;;
 3: len 4; hex 80000005; asc     ;;
 4: len 4; hex 8000008a; asc     ;;

2023-03-10T06:57:50.484432Z 35 [Note] InnoDB: *** (2) TRANSACTION:

TRANSACTION 1398, ACTIVE 34 sec starting index read
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 35, OS thread handle 140033790719744, query id 648 localhost root updating
update t1 set b = b + 111 where id = 10
2023-03-10T06:57:50.484455Z 35 [Note] InnoDB: *** (2) HOLDS THE LOCK(S):

RECORD LOCKS space id 25 page no 3 n bits 72 index PRIMARY of table `test`.`t1` trx id 1398 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 80000005; asc     ;;
 1: len 6; hex 000000000576; asc      v;;
 2: len 7; hex 4c000001580110; asc L   X  ;;
 3: len 4; hex 80000005; asc     ;;
 4: len 4; hex 8000008a; asc     ;;

2023-03-10T06:57:50.484600Z 35 [Note] InnoDB: *** (2) WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 25 page no 3 n bits 72 index PRIMARY of table `test`.`t1` trx id 1398 lock_mode X locks rec but not gap waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 8000000a; asc     ;;
 1: len 6; hex 000000000577; asc      w;;
 2: len 7; hex 4d000001590110; asc M   Y  ;;
 3: len 4; hex 8000000a; asc     ;;
 4: len 4; hex 80000107; asc     ;;

2023-03-10T06:57:50.484737Z 35 [Note] InnoDB: *** WE ROLL BACK TRANSACTION (2)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
文章知识点与官方知识档案匹配,可进一步学习相关知识
————————————————

                            版权声明:本文为博主原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。
                        
原文链接:https://blog.csdn.net/lizc_lizc/article/details/129444088

 

标签:lock,hex,len,t1,asc,死锁,MySQL,日志,id
From: https://www.cnblogs.com/iancloud/p/18021599

相关文章

  • MySQL死锁日志的查看和分析
    MySQL死锁日志的查看和分析 目录一,关于MySQL的死锁二,人造一个死锁的场景三,查看最近一次死锁的日志四,死锁日志的内容1,事务1信息2,事务1持有的锁3,事务1正在等待的锁4,事务2信息5,事务2正在持有的锁6,事务2正在等待的锁7,死锁处理结果五,关于mysql的八种锁1,行锁(RecordL......
  • tomcat日志文件按日期生成
    tomcat日志文件按日期生成有时候想查一下日志看看什么问题,发现tomcat的catalina.out日志很大,很不方便,so安装cronolog互联网服务器,直接安装yuminstallcronolog查询cronolog路径:whichcronolog/usr/sbin/cronolog修改catalina.sh文件注释touch"\(CATALINA_OUT"找到"\)C......
  • MySQL学习之存储过程
    存储过程-介绍介绍存储过程是事先经过编译并存储在数据库中的一段SQL语句的集合,调用存储过程可以简化应用开发人员的很多工作,减少数据在数据库和应用服务器之间的传输,对于提高数据处理的效率是有好处的。存储过程思想上很简单,就是数据库SQL语言层面的代码封装与重用。特点......
  • Docker安装MySQL容器
    Docker安装MySQL容器查看原文安装MySQL#查找官方镜像dockersearchmysql#下载mysql镜像sudodockerpullmysql:5.7#检查当前所有Docker下载的镜像dockerimages启动MySQL容器dockerrun-p3306:3306--namemysql\-v/usr/local/docker/mysql/conf:/etc/mysq......
  • linux下安装MySql
    linux下安装MySql查看原文点击下载地址,选择版本8.0.26系统为linux-Generic,下载安装包mysql-8.0.26-linux-glibc2.12-x86_64.tar.xz解压安装包#如果有mariadb包,需要提前卸载!!!!!!!!#tar.xz无法直接解压#将tar.xz转换为tarxz-dmysql-8.0.26-linux-glibc2.12-x86_64.tar.x......
  • mysql-udf-http插件的安装与使用
    mysql-udf-http插件的安装与使用查看原文安装curl点击下载地址,下载curl-7.69.0.tar.gz#解压curl-7.69.0.tar.gztar-zvxfcurl-7.69.0.tar.gzcdcurl-7.69.0#配置安装路径./configure-prefix=/usr/local/curl#进行安装make&&makeinstall安装mysql-udf-http点......
  • 01 MySQL的基本架构
    前言:记录对林晓斌老师的《MySQL实战45讲》课程学习路程。01MySQL的基本架构MySQL的逻辑架构图MySQL分为Server层和存储引擎层两部分。Server层功能:实现所有跨存储引擎的功能,比如存储过程、触发器、视图等。连接器、查询缓存、分析器、优化器、执行器等,以及......
  • SQLite、MySQL和PostgreSQL的区别
    SQLite、MySQL和PostgreSQL都是广泛使用的开源关系型数据库管理系统(RDBMS),但它们在设计目标、适用场景和功能特性上各有特点:SQLite:简介:SQLite是一个轻量级的嵌入式数据库引擎,它不需要独立服务器进程就可以运行。SQLite数据库文件直接存储在磁盘上,应用程序通过API直接与数据库......
  • 从兼容MySql的国产数据库OceanBase导出数据到MySql5.7报错解决
    现象:用MySQL的客户端或管理工具(如DBeaver/heidisql)正常导出是没有问题的,但是导入会提示错误.原因:导出时,默认建表语句添加了一些OceanBase特有的选项,MySQL不支持.打开导出的.sql文件可以看到,建表语句最后有类似: COLLATE=utf8mb4_binROW_FORMAT=DYNAMICCOMPRESSI......
  • centos7安装mysql随记
    安装途中出现RPM-GPG-KEY-mysql实现问题,通过rpm--importhttps://repo.mysql.com/RPM-GPG-KEY-mysql-2022解决,如果网络不好也可以直接复制RPM-GPG-KEY-mysql-2022的内容到RPM-GPG-KEY-mysql中虚拟机版本是centos7,安装mysql5.7,把rpm文件下载好之后直接放到虚拟机上也可以通过虚......