MySQL 死锁日志详细解读
1. 死锁日志配置
1.1 开启死锁日志记录
-- 查看当前死锁日志配置
SHOW VARIABLES LIKE 'innodb_print_all_deadlocks';
-- 开启死锁日志（临时）
SET GLOBAL innodb_print_all_deadlocks = ON;
-- 在my.cnf中永久配置
[mysqld]
innodb_print_all_deadlocks = ON
log_error = /var/log/mysql/error.log
1.2 查看死锁日志的几种方式
-- 方式1：查看错误日志
SHOW VARIABLES LIKE 'log_error';
-- 方式2：获取最近的死锁信息
SHOW ENGINE INNODB STATUS;
-- 方式3：查看锁信息表
SELECT * FROM information_schema.INNODB_TRX; -- 当前运行的事务
SELECT * FROM information_schema.INNODB_LOCKS; -- 当前锁信息
SELECT * FROM information_schema.INNODB_LOCK_WAITS; -- 锁等待信息
2. 完整的死锁日志示例
2.1 准备测试数据
CREATE TABLE `user_account` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user_id` int(11) NOT NULL,
`account_type` tinyint(4) NOT NULL COMMENT '1:现金 2:积分',
`balance` decimal(10,2) NOT NULL DEFAULT '0.00',
PRIMARY KEY (`id`),
UNIQUE KEY `uk_user_account` (`user_id`,`account_type`),
KEY `idx_user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO `user_account` (`id`, `user_id`, `account_type`, `balance`) VALUES
(1, 1001, 1, 1000.00),
(2, 1001, 2, 5000.00),
(3, 1002, 1, 2000.00);
2.2 创建死锁场景
事务1 (Session A):
-- Session A
START TRANSACTION;
-- 对user_id=1003的记录加锁（不存在，会加间隙锁）
UPDATE user_account SET balance = balance + 100
WHERE user_id = 1003 AND account_type = 1;
-- 等待一段时间...
事务2 (Session B):
-- Session B
START TRANSACTION;
-- 尝试对同一间隙加锁
UPDATE user_account SET balance = balance + 200
WHERE user_id = 1004 AND account_type = 1;
事务1继续:
-- Session A 继续执行
INSERT INTO user_account (user_id, account_type, balance)
VALUES (1003, 1, 100);
-- 此时会等待Session B的锁
事务2继续:
-- Session B 继续执行
INSERT INTO user_account (user_id, account_type, balance)
VALUES (1004, 1, 200);
-- 发生死锁！
3. 死锁日志详细解读
3.1 完整的死锁日志输出
------------------------
LATEST DETECTED DEADLOCK
------------------------
2023-10-01 10:30:25 0x7f8e2c0e9700
*** (1) TRANSACTION:
TRANSACTION 5831, ACTIVE 25 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 2
MySQL thread id 52, OS thread handle 140128847619840, query id 1234 127.0.0.1 root update
INSERT INTO user_account (user_id, account_type, balance) VALUES (1003, 1, 100)
*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 45 page no 4 n bits 72 index uk_user_account of table `test_db`.`user_account` trx id 5831 lock_mode X locks gap before rec
Record lock, heap no 3 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 4; hex 800003e9; asc ;; -- user_id=1001 (0x3e9=1001)
1: len 1; hex 02; asc ;; -- account_type=2
2: len 4; hex 80000001; asc ;; -- id=1
*** (1) WAITING FOR THIS LOCK(S):
RECORD LOCKS space id 45 page no 4 n bits 72 index uk_user_account of table `test_db`.`user_account` trx id 5831 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 4; hex 800003e9; asc ;;
1: len 1; hex 02; asc ;;
2: len 4; hex 80000001; asc ;;
*** (2) TRANSACTION:
TRANSACTION 5832, ACTIVE 15 sec inserting
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 2
MySQL thread id 53, OS thread handle 140128847357696, query id 1235 127.0.0.1 root update
INSERT INTO user_account (user_id, account_type, balance) VALUES (1004, 1, 200)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 45 page no 4 n bits 72 index uk_user_account of table `test_db`.`user_account` trx id 5832 lock_mode X locks gap before rec
Record lock, heap no 3 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 4; hex 800003e9; asc ;;
1: len 1; hex 02; asc ;;
2: len 4; hex 80000001; asc ;;
*** (2) WAITING FOR THIS LOCK(S):
RECORD LOCKS space id 45 page no 4 n bits 72 index uk_user_account of table `test_db`.`user_account` trx id 5832 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 4; hex 800003e9; asc ;;
1: len 1; hex 02; asc ;;
2: len 4; hex 80000001; asc ;;
*** WE ROLL BACK TRANSACTION (2)
3.2 日志分段解读
第一部分：基本信息
------------------------
LATEST DETECTED DEADLOCK
------------------------
2023-10-01 10:30:25 0x7f8e2c0e9700
- LATEST DETECTED DEADLOCK: 最新检测到的死锁
- 时间戳: 死锁发生的时间
- 线程句柄: 操作系统线程标识
第二部分：事务1信息
*** (1) TRANSACTION:
TRANSACTION 5831, ACTIVE 25 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 2
MySQL thread id 52, OS thread handle 140128847619840, query id 1234 127.0.0.1 root update
INSERT INTO user_account (user_id, account_type, balance) VALUES (1003, 1, 100)
- TRANSACTION 5831: 事务ID
- ACTIVE 25 sec: 事务已活跃25秒
- inserting: 正在执行插入操作
- tables in use 1: 涉及1张表
- locked 1: 有1个表锁
- 4 lock struct(s): 4个锁结构
- 3 row lock(s): 持有3个行锁
- undo log entries 2: 有2条undo日志记录
- thread id 52: MySQL线程ID
- query id 1234: 查询ID
- 最后执行的SQL: 正在等待执行的INSERT语句
第三部分：事务1持有的锁
*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 45 page no 4 n bits 72 index uk_user_account of table `test_db`.`user_account` trx id 5831 lock_mode X locks gap before rec
Record lock, heap no 3 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 4; hex 800003e9; asc ;; -- user_id=1001 (0x3e9=1001)
1: len 1; hex 02; asc ;; -- account_type=2
2: len 4; hex 80000001; asc ;; -- id=1
- RECORD LOCKS: 记录锁（行锁）
- space id 45: 表空间ID
- page no 4: 页编号
- index uk_user_account: 在唯一索引上
- lock_mode X: 排他锁
- locks gap before rec: 间隙锁（锁定记录前的间隙）
- heap no 3: 堆中的记录编号
- PHYSICAL RECORD: 物理记录内容
- 字段1: user_id=1001 (十六进制800003e9)
- 字段2: account_type=2 (十六进制02)
- 字段3: id=1 (十六进制80000001)
第四部分：事务1等待的锁
*** (1) WAITING FOR THIS LOCK(S):
RECORD LOCKS space id 45 page no 4 n bits 72 index uk_user_account of table `test_db`.`user_account` trx id 5831 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 4; hex 800003e9; asc ;;
1: len 1; hex 02; asc ;;
2: len 4; hex 80000001; asc ;;
- lock_mode X locks gap before rec insert intention waiting:
- lock_mode X: 排他锁
- locks gap before rec: 间隙锁
- insert intention: 插入意向锁（特别类型的间隙锁）
- waiting: 正在等待这个锁
第五部分：事务2信息（类似事务1）
*** (2) TRANSACTION:
TRANSACTION 5832, ACTIVE 15 sec inserting
...
INSERT INTO user_account (user_id, account_type, balance) VALUES (1004, 1, 200)
第六部分：事务2持有的锁和等待的锁
*** (2) HOLDS THE LOCK(S): -- 事务2持有的锁
*** (2) WAITING FOR THIS LOCK(S): -- 事务2等待的锁
- 持有相同的间隙锁（在相同的记录上）
- 等待相同的插入意向锁
第七部分：死锁处理结果
*** WE ROLL BACK TRANSACTION (2)
- MySQL选择回滚事务(2)来打破死锁
- 通常回滚undo log量较小的事务
4. 死锁矩阵分析
4.1 锁的兼容性矩阵
锁类型 X(排他) S(共享) GAP(间隙) INSERT_INTENTION(插入意向)
X(排他锁) 冲突 冲突 冲突 冲突
S(共享锁) 冲突 兼容 兼容 兼容
GAP(间隙锁) 冲突 兼容 兼容 冲突
INSERT_INTENTION 冲突 兼容 冲突 兼容
- GAP间隙锁和GAP间隙锁 是兼容的，这也解释了为什么事务2的update没有被事务1的update阻塞。
- 间隙锁和插入意向锁是冲突的，这是为啥事务1和2的insert被阻塞了。
- 插入意向锁和插入意向锁也是兼容的。
4.2 本例死锁分析
时间线：
1. T1: UPDATE ... WHERE user_id=1003 → 获得间隙锁G1
2. T2: UPDATE ... WHERE user_id=1004 → 获得间隙锁G2（与G1相同间隙）
3. T1: INSERT user_id=1003 → 申请插入意向锁II1，等待T2的G2
4. T2: INSERT user_id=1004 → 申请插入意向锁II2，等待T1的G1
死锁形成：
T1持有G1，等待II1（被T2的G2阻塞）
T2持有G2，等待II2（被T1的G1阻塞）
5. 深入解读锁类型
5.1 各种锁类型说明
-- 记录锁（Record Lock）
lock_mode X locks rec but not gap
-- 排他记录锁，只锁记录，不锁间隙
-- 间隙锁（Gap Lock）
lock_mode X locks gap before rec
-- 锁定记录之前的间隙
-- 临键锁（Next-Key Lock）
lock_mode X
-- 记录锁+间隙锁，锁定记录和前面的间隙
-- 插入意向锁（Insert Intention Lock）
lock_mode X locks gap before rec insert intention
-- 特殊的间隙锁，表示准备插入
5.2 实际案例中的锁
-- 案例1：等值查询不存在的记录
UPDATE user_account SET balance = 100 WHERE user_id = 9999;
-- 会加间隙锁，锁定user_id=9999所在的间隙
-- 案例2：范围查询
UPDATE user_account SET balance = 100 WHERE user_id > 1000;
-- 会加临键锁，锁定所有符合条件的记录和间隙
-- 案例3：唯一索引等值查询存在的记录
UPDATE user_account SET balance = 100 WHERE user_id = 1001 AND account_type = 1;
-- 只会加记录锁，不加间隙锁
6. 常见死锁场景及解决方案
场景1：间隙锁死锁（本例）
原因: 多个事务更新不存在的记录 解决方案:
-- 方案1：使用读已提交隔离级别
-- 方案2：使用SELECT ... FOR UPDATE先锁定
START TRANSACTION;
SELECT * FROM user_account WHERE user_id = 1003 FOR UPDATE;
-- 如果记录存在，执行UPDATE；如果不存在，执行INSERT
COMMIT;
场景2：锁顺序死锁
原因: 多个事务以不同顺序访问资源
-- 事务1: 先更新A，再更新B
-- 事务2: 先更新B，再更新A
解决方案: 统一sql顺序
参考
- 《MySQL45讲》
- AI大模型