mysql 查看死锁日志

基础概念

MySQL中的死锁是指两个或多个事务互相等待对方释放资源，导致所有相关事务都无法继续执行的情况。死锁日志记录了MySQL检测到的死锁事件，有助于分析和解决死锁问题。

查看死锁日志的方法

MySQL默认情况下不会生成死锁日志文件，需要手动配置。以下是查看和配置MySQL死锁日志的步骤：

启用死锁日志：编辑MySQL配置文件（通常是my.cnf或my.ini），添加或修改以下配置项：
启用死锁日志：编辑MySQL配置文件（通常是my.cnf或my.ini），添加或修改以下配置项：
然后重启MySQL服务使配置生效。
查看死锁日志：启用死锁日志后，MySQL会将死锁信息记录到错误日志文件中。可以通过以下命令查看错误日志文件的位置：
查看死锁日志：启用死锁日志后，MySQL会将死锁信息记录到错误日志文件中。可以通过以下命令查看错误日志文件的位置：
假设错误日志文件路径为/var/log/mysql/error.log，可以使用以下命令查看死锁日志：
假设错误日志文件路径为/var/log/mysql/error.log，可以使用以下命令查看死锁日志：

死锁日志示例

死锁日志通常包含以下信息：

死锁发生的时间
涉及的事务ID
每个事务的锁请求和锁等待情况
死锁检测和回滚的详细信息

例如：

2023-04-01 10:30:00 7f8d3c0e8700 InnoDB: Transaction (1) was chosen as a deadlock victim.
2023-04-01 10:30:00 7f8d3c0e8700 InnoDB: There are 2 deadlocks. The following is a deadlock report.
2023-04-01 10:30:00 7f8d3c0e8700 InnoDB: Transaction (2) was waiting for 1 lock(s) in connection 3.
2023-04-01 10:30:00 7f8d3c0e8700 InnoDB: Transaction (1) was waiting for 1 lock(s) in connection 2.
2023-04-01 10:30:00 7f8d3c0e8700 InnoDB: *** (2) TRANSACTION:
2023-04-01 10:30:00 7f8d3c0e8700 InnoDB: BEGIN; COMMIT;
2023-04-01 10:30:00 7f8d3c0e8700 InnoDB: *** (1) TRANSACTION:
2023-04-01 10:30:00 7f8d3c0e8700 InnoDB: BEGIN; COMMIT;
2023-04-01 10:30:00 7f8d3c0e8700 InnoDB: *** (2) HOLDS THE LOCK(S):
2023-04-01 10:30:00 7f8d3c0e8700 InnoDB: RECORD LOCKS space id 12 page no 3 n bits 72 index `idx_name` of table `db`.`table` trx id 2 0 sec locking rec but not gap waiting
2023-04-01 10:30:00 7f8d3c0e8700 InnoDB: Record lock, heap no 1 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
2023-04-01 10:30:00 7f8d3c0e8700 InnoDB: 0: len 4; hex 80000001; asc     ;;
2023-04-01 10:30:00 7f8d3c0e8700 InnoDB: 1: len 6; hex 00000000050a; asc     ;;
2023-04-01 10:30:00 7f8d3c0e8700 InnoDB: 2: len 7; hex 000000002a0000000000; asc *      ;;
2023-04-01 10:30:00 7f8d3c0e8700 InnoDB: *** (2) WAITING FOR THIS LOCK TO BE GRANTED:
2023-04-01 10:30:00 7f8d3c0e8700 InnoDB: RECORD LOCKS space id 12 page no 4 n bits 72 index `idx_name` of table `db`.`table` trx id 2 0 sec waiting
2023-04-01 10:30:00 7f8d3c0e8700 InnoDB: Record lock, heap no 1 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
2023-04-01 10:30:00 7f8d3c0e8700 InnoDB: 0: len 4; hex 80000002; asc     ;;
2023-04-01 10:30:00 7f8d3c0e8700 InnoDB: 1: len 6; hex 00000000050b; asc     ;;
2023-04-01 10:30:00 7f8d3c0e8700 InnoDB: 2: len 7; hex 000000002b0000000000; asc +      ;;
2023-04-01 10:30:00 7f8d3c0e8700 InnoDB: *** (1) HOLDS THE LOCK(S):
2023-04-01 10:30:00 7f8d3c0e8700 InnoDB: RECORD LOCKS space id 12 page no 4 n bits 72 index `idx_name` of table `db`.`table` trx id 1 0 sec locking rec but not gap
2023-04-01 10:30:00 7f8d3c0e8700 InnoDB: Record lock, heap no 1 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
2023-04-01 10:30:00 7f8d3c0e8700 InnoDB: 0: len 4; hex 80000002; asc     ;;
2023-04-01 10:30:00 7f8d3c0e8700 InnoDB: 1: len 6; hex 00000000050b; asc     ;;
2023-04-01 10:30:00 7f8d3c0e8700 InnoDB: 2: len 7; hex 000000002b0000000000; asc +      ;;
2023-04-01 10:30:00 7f8d3c0e8700 InnoDB: *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
2023-04-01 10:30:00 7f8d3c0e8700 InnoDB: RECORD LOCKS space id 12 page no 3 n bits 72 index `idx_name` of table `db`.`table` trx id 1 0 sec waiting
2023-04-01 10:30:00 7f8d3c0e8700 InnoDB: Record lock, heap no 1 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
2023-04-01 10:30:00 7f8d3c0e8700 InnoDB: 0: len 4; hex 80000001; asc     ;;
2023-04-01 10:30:00 7f8d3c0e8700 InnoDB: 1: len 6; hex 00000000050a; asc     ;;
2023-04-01 10:30:00 7f8d3c0e8700 InnoDB: 2: len 7; hex 000000002a0000000000; asc *      ;;