innodb_print_all_deadlocks是InnoDB存储引擎的一个配置参数。用于控制在发生死锁时将所有的死锁信息打印到错误日志中。
本文基于MySQL8.4.3版本。MySQL8.4官方参考手册：https://dev.mysql.com/doc/refman/8.4/en/innodb-parameters.html#sysvar_innodb_print_all_deadlocks
innodb_print_all_deadlocks = ON
当设置为ON时，InnoDB引擎会在每次检测到死锁时，将与该死锁相关的所有事务的详细信息记录到log_error日志中。
临时生效
|
1 |
SET GLOBAL innodb_print_all_deadlocks = "ON"; |
永久生效修改MySQL配置文件以下参数
|
1 |
innodb_print_all_deadlocks = ON |
innodb_print_all_deadlocks = OFF
当设置为OFF时（默认值），InnoDB只会记录最后一个发生死锁的信息，需要使用SHOW ENGINE INNODB STATUS;语句查看，但是不会将死锁信息写入到log_error日志中。
临时生效
|
1 |
SET GLOBAL innodb_print_all_deadlocks = "OFF"; |
永久生效修改MySQL配置文件以下参数
|
1 |
innodb_print_all_deadlocks = OFF |
演示示例
基于wlnmp源，安装MySQL8.4.3版本验证innodb_print_all_deadlocks参数实际效果。
临时开启死锁日志记录
|
1 |
SET GLOBAL innodb_print_all_deadlocks = ON; |
查看死锁状态是否开启
|
1 2 3 4 5 6 |
show variables like 'innodb_print_all_deadlocks'; +----------------------------+-------+ | Variable_name | Value | +----------------------------+-------+ | innodb_print_all_deadlocks | ON | +----------------------------+-------+ |
查看log_error_verbosity参数值
|
1 2 3 4 5 6 |
show variables like 'log_error_verbosity'; +---------------------+-------+ | Variable_name | Value | +---------------------+-------+ | log_error_verbosity | 3 | +---------------------+-------+ |
创建测试库
|
1 |
CREATE DATABASE test_db; |
创建测试表
|
1 2 |
use test_db; CREATE TABLE accounts ( id INT PRIMARY KEY, balance DECIMAL (10, 2) ); |
插入一些测试数据
|
1 |
INSERT INTO accounts (id, balance) VALUES (1, 1000.00), (2, 2000.00); |
执行上述操作后，这个表是如下样子：
|
1 2 3 4 5 6 7 |
SELECT * FROM accounts; +----+---------+ | id | balance | +----+---------+ | 1 | 1000.00 | | 2 | 2000.00 | +----+---------+ |
分别开启两个终端会话来模拟死锁场景。
在第一个会话中
|
1 2 3 4 5 6 |
-- 启动事务A START TRANSACTION; -- 事务A锁定记录1 UPDATE accounts SET balance = balance - 100 WHERE id = 1; -- 等待10秒，以确保事务B可以获取锁 SELECT SLEEP(10); |
在第二个会话中
|
1 2 3 4 5 6 |
-- 启动事务B START TRANSACTION; -- 事务B锁定记录2 UPDATE accounts SET balance = balance - 200 WHERE id = 2; -- 等待10秒，以确保事务A可以获取锁 SELECT SLEEP(10); |
在第一个会话中
|
1 2 |
-- 事务A尝试锁定记录2 UPDATE accounts SET balance = balance + 100 WHERE id = 2; |
在第二个会话中
|
1 2 |
-- 事务B尝试锁定记录1 UPDATE accounts SET balance = balance + 200 WHERE id = 1; |
此时，事务A已经锁定了记录1，但它需要锁定记录2，而事务B已经锁定了记录2，但它需要锁定记录1。这就形成了一个死锁。
log_error_verbosity值为3，log_error日志内容如下
|
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 |
2024-11-30T16:29:38.178861+08:00 0 [Note] [MY-012468] [InnoDB] Transactions deadlock detected, dumping detailed information. 2024-11-30T16:29:38.178932+08:00 0 [Note] [MY-012469] [InnoDB] *** (1) TRANSACTION: TRANSACTION 2932, ACTIVE 23 sec starting index read mysql tables in use 1, locked 1 LOCK WAIT 3 lock struct(s), heap size 1128, 2 row lock(s), undo log entries 1 MySQL thread id 10, OS thread handle 139650885469952, query id 96 localhost root updating UPDATE accounts SET balance = balance + 100 WHERE id = 2 2024-11-30T16:29:38.178996+08:00 0 [Note] [MY-012469] [InnoDB] *** (1) HOLDS THE LOCK(S): RECORD LOCKS space id 6 page no 4 n bits 72 index PRIMARY of table `test_db`.`accounts` trx id 2932 lock_mode X locks rec but not gap Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 0: len 4; hex 80000001; asc ;; 1: len 6; hex 000000000b74; asc t;; 2: len 7; hex 02000001290151; asc ) Q;; 3: len 5; hex 8000038400; asc ;; 2024-11-30T16:29:38.179177+08:00 0 [Note] [MY-012469] [InnoDB] *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 6 page no 4 n bits 72 index PRIMARY of table `test_db`.`accounts` trx id 2932 lock_mode X locks rec but not gap waiting Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 0: len 4; hex 80000002; asc ;; 1: len 6; hex 000000000b75; asc u;; 2: len 7; hex 01000001260151; asc & Q;; 3: len 5; hex 8000070800; asc ;; 2024-11-30T16:29:38.179419+08:00 0 [Note] [MY-012469] [InnoDB] *** (2) TRANSACTION: TRANSACTION 2933, ACTIVE 14 sec starting index read mysql tables in use 1, locked 1 LOCK WAIT 3 lock struct(s), heap size 1128, 2 row lock(s), undo log entries 1 MySQL thread id 11, OS thread handle 139650885740288, query id 97 localhost root updating UPDATE accounts SET balance = balance + 200 WHERE id = 1 2024-11-30T16:29:38.179484+08:00 0 [Note] [MY-012469] [InnoDB] *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 6 page no 4 n bits 72 index PRIMARY of table `test_db`.`accounts` trx id 2933 lock_mode X locks rec but not gap Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 0: len 4; hex 80000002; asc ;; 1: len 6; hex 000000000b75; asc u;; 2: len 7; hex 01000001260151; asc & Q;; 3: len 5; hex 8000070800; asc ;; 2024-11-30T16:29:38.179749+08:00 0 [Note] [MY-012469] [InnoDB] *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 6 page no 4 n bits 72 index PRIMARY of table `test_db`.`accounts` trx id 2933 lock_mode X locks rec but not gap waiting Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 0: len 4; hex 80000001; asc ;; 1: len 6; hex 000000000b74; asc t;; 2: len 7; hex 02000001290151; asc ) Q;; 3: len 5; hex 8000038400; asc ;; 2024-11-30T16:29:38.180058+08:00 0 [Note] [MY-012469] [InnoDB] *** WE ROLL BACK TRANSACTION (2) |
可以删除测试表，或自行修改测试的UPDATE，我们来验证当log_error_verbosity值为2，log_error死锁日志的情况
|
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 |
TRANSACTION 3363, ACTIVE 29 sec starting index read mysql tables in use 1, locked 1 LOCK WAIT 3 lock struct(s), heap size 1128, 2 row lock(s), undo log entries 1 MySQL thread id 8, OS thread handle 139811856066304, query id 21 localhost root updating UPDATE accounts SET balance = balance + 100 WHERE id = 2 RECORD LOCKS space id 7 page no 4 n bits 72 index PRIMARY of table `test_db`.`accounts` trx id 3363 lock_mode X locks rec but not gap Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 0: len 4; hex 80000001; asc ;; 1: len 6; hex 000000000d23; asc #;; 2: len 7; hex 01000001090367; asc g;; 3: len 5; hex 8000038400; asc ;; RECORD LOCKS space id 7 page no 4 n bits 72 index PRIMARY of table `test_db`.`accounts` trx id 3363 lock_mode X locks rec but not gap waiting Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 0: len 4; hex 80000002; asc ;; 1: len 6; hex 000000000d24; asc $;; 2: len 7; hex 020000012d0151; asc - Q;; 3: len 5; hex 8000070800; asc ;; TRANSACTION 3364, ACTIVE 15 sec starting index read mysql tables in use 1, locked 1 LOCK WAIT 3 lock struct(s), heap size 1128, 2 row lock(s), undo log entries 1 MySQL thread id 9, OS thread handle 139811775387392, query id 22 localhost root updating UPDATE accounts SET balance = balance + 200 WHERE id = 1 RECORD LOCKS space id 7 page no 4 n bits 72 index PRIMARY of table `test_db`.`accounts` trx id 3364 lock_mode X locks rec but not gap Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 0: len 4; hex 80000002; asc ;; 1: len 6; hex 000000000d24; asc $;; 2: len 7; hex 020000012d0151; asc - Q;; 3: len 5; hex 8000070800; asc ;; RECORD LOCKS space id 7 page no 4 n bits 72 index PRIMARY of table `test_db`.`accounts` trx id 3364 lock_mode X locks rec but not gap waiting Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 0: len 4; hex 80000001; asc ;; 1: len 6; hex 000000000d23; asc #;; 2: len 7; hex 01000001090367; asc g;; 3: len 5; hex 8000038400; asc ;; |
可以删除测试表，或自行修改测试的UPDATE，我们来验证当log_error_verbosity值为1，log_error死锁日志的情况
|
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 |
TRANSACTION 3867, ACTIVE 32 sec starting index read mysql tables in use 1, locked 1 LOCK WAIT 3 lock struct(s), heap size 1128, 2 row lock(s), undo log entries 1 MySQL thread id 8, OS thread handle 139696269883136, query id 20 localhost root updating UPDATE accounts SET balance = balance + 100 WHERE id = 2 RECORD LOCKS space id 8 page no 4 n bits 72 index PRIMARY of table `test_db`.`accounts` trx id 3867 lock_mode X locks rec but not gap Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 0: len 4; hex 80000001; asc ;; 1: len 6; hex 000000000f1b; asc ;; 2: len 7; hex 010000011a0477; asc w;; 3: len 5; hex 8000038400; asc ;; RECORD LOCKS space id 8 page no 4 n bits 72 index PRIMARY of table `test_db`.`accounts` trx id 3867 lock_mode X locks rec but not gap waiting Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 0: len 4; hex 80000002; asc ;; 1: len 6; hex 000000000f1c; asc ;; 2: len 7; hex 020000011a02da; asc ;; 3: len 5; hex 8000070800; asc ;; TRANSACTION 3868, ACTIVE 14 sec starting index read mysql tables in use 1, locked 1 LOCK WAIT 3 lock struct(s), heap size 1128, 2 row lock(s), undo log entries 1 MySQL thread id 9, OS thread handle 139696269612800, query id 21 localhost root updating UPDATE accounts SET balance = balance + 200 WHERE id = 1 RECORD LOCKS space id 8 page no 4 n bits 72 index PRIMARY of table `test_db`.`accounts` trx id 3868 lock_mode X locks rec but not gap Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 0: len 4; hex 80000002; asc ;; 1: len 6; hex 000000000f1c; asc ;; 2: len 7; hex 020000011a02da; asc ;; 3: len 5; hex 8000070800; asc ;; RECORD LOCKS space id 8 page no 4 n bits 72 index PRIMARY of table `test_db`.`accounts` trx id 3868 lock_mode X locks rec but not gap waiting Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 0: len 4; hex 80000001; asc ;; 1: len 6; hex 000000000f1b; asc ;; 2: len 7; hex 010000011a0477; asc w;; 3: len 5; hex 8000038400; asc ;; |
通过该示例可以发现，log_error_verbosity值1或2时，依旧会记录死锁信息，并且几乎没有区别，log_error_verbosity等于3死锁信息更详细。
注意事项
1、死锁日志：当log_error_verbosity值等于1或2，发生死锁时，log_error日志中记录的内容相似，主要包括死锁的发生情况和相关的事务，但不包含时间戳。当log_error_verbosity值等于3，发生死锁时，log_error日志将详细记录死锁的情况，包括时间戳、更多的上下文信息。
2、死锁关闭：默认情况下innodb_print_all_deadlocks处于OFF状态，在这种情况下，只能通过SHOW ENGINE INNODB STATUS;命令查看最后一次发生死锁的详细信息，但是不会将死锁信息写入到log_error日志中。
3、性能影响：开启innodb_print_all_deadlocks可能会对性能产生一定影响，尤其是在高并发的环境中，因为每次发生死锁时都会记录详细信息。建议在开发、测试环境中使用，或在生产环境中仅在排查问题时临时开启。
总结
通过合理使用innodb_print_all_deadlocks，可以更有效地监控和优化应用程序的数据库操作，以减少死锁的发生。
附，MySQL my.cnf配置文件生成器：https://dbcnf.wlnmp.com/
原文链接：MySQL innodb_print_all_deadlocks参数介绍，转载请注明来源！
