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)

查看错误日志文件放在哪里

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)

也可以查看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);

构造死锁情况

使用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 00: 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 00: 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 00: 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
------------

也可以通过查看错误日志找到死锁信息，

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 00: 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 00: 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 00: 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)

本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！