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