innodb_print_all_deadlocks

最新推荐文章于 2025-06-06 23:23:10 发布

原创最新推荐文章于 2025-06-06 23:23:10 发布 · 3.8k 阅读

5 ·

CC 4.0 BY-SA版权

mysql 专栏收录该内容

56 篇文章

订阅专栏

当mysql 数据库发生死锁时， innodb status 里面会记录最后一次死锁的相关信息，但mysql 错误日志里面

不会记录死锁相关信息，要想记录，启动 innodb_print_all_deadlocks 参数。

当系统并发很高时，很多的线程等待同一个行锁，死锁检测可能会拖慢系统，这个时候关闭死锁检测可能更好

（innodb_deadlock_detect 5.7.15 版本才有）。让线程自动超时。

mysql> show variables like 'innodb_print_all_deadlocks';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| innodb_print_all_deadlocks | ON    |
+----------------------------+-------+

2018-07-13T12:57:35.621054+08:00 21018 [Note] InnoDB: 
*** (1) TRANSACTION:

TRANSACTION 1344, ACTIVE 9 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 21019, OS thread handle 139626295346944, query id 42108 localhost root updating
delete from t1 where id=1
2018-07-13T12:57:35.621146+08:00 21018 [Note] InnoDB: *** (1) WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 27 page no 3 n bits 72 index PRIMARY of table `test`.`t1` trx id 1344 lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 32
 0: len 4; hex 80000001; asc     ;;
 1: len 6; hex 00000000053b; asc      ;;;
 2: len 7; hex 28000000360110; asc (   6  ;;

2018-07-13T12:57:35.621428+08:00 21018 [Note] InnoDB: *** (2) TRANSACTION:

TRANSACTION 1339, ACTIVE 35 sec starting index read, thread declared inside InnoDB 5000
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 21018, OS thread handle 139626295879424, query id 42109 localhost root updating
delete from t1 where id=2
2018-07-13T12:57:35.621532+08:00 21018 [Note] InnoDB: *** (2) HOLDS THE LOCK(S):

RECORD LOCKS space id 27 page no 3 n bits 72 index PRIMARY of table `test`.`t1` trx id 1339 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 32
 0: len 4; hex 80000001; asc     ;;
 1: len 6; hex 00000000053b; asc      ;;;
 2: len 7; hex 28000000360110; asc (   6  ;;

2018-07-13T12:57:35.621739+08:00 21018 [Note] InnoDB: *** (2) WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 27 page no 3 n bits 72 index PRIMARY of table `test`.`t1` trx id 1339 lock_mode X locks rec but not gap waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 3; compact format; info bits 32
 0: len 4; hex 80000002; asc     ;;
 1: len 6; hex 000000000540; asc      @;;
 2: len 7; hex 2b000000300409; asc +   0  ;;

2018-07-13T12:57:35.621965+08:00 21018 [Note] InnoDB: *** WE ROLL BACK TRANSACTION (2)

2018-07-13T12:57:54.527054+08:00 21023 [Note] Access denied for user 'repl'@'10.10.19.202' (using password: YES)