Mysql 死锁分析
通过show innodb status查看
先构造出死锁的sql，客户端1执行
begin ;
select * from User where id = 1 for update ;
select sleep(10);
select * from User where id = 2 for update ;
commit ;
客户端2执行
begin ;
select * from User where id = 2 for update ;
select sleep(10);
select * from User where id = 1 for update ;
commit ;
死锁出现后执行
SHOW ENGINE INNODB STATUS;
观察日志
------------------------
LATEST DETECTED DEADLOCK
------------------------
2025-03-14 12:35:22 0x7000072bc000
*** (1) TRANSACTION:
TRANSACTION 731408, ACTIVE 13 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1128, 2 row lock(s)
MySQL thread id 13, OS thread handle 123145437552640, query id 398 localhost 127.0.0.1 root statistics
/* ApplicationName=GoLand 2024.1.6 */ select * from User where id = 1 for update
*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 571 page no 4 n bits 72 index PRIMARY of table `test`.`user` trx id 731408 lock_mode X locks rec but not gap
Record lock, heap no 5 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 80000002; asc ;;
1: len 6; hex 0000000afa28; asc (;;
2: len 7; hex 01000000f40235; asc 5;;
3: len 16; hex 79766574746540707269736d612e696f; asc yvette@prisma.io;;
4: len 12; hex 7a68616f686f6e6766656e67; asc zhaohongfeng;;
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 571 page no 4 n bits 72 index PRIMARY of table `test`.`user` trx id 731408 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 80000001; asc ;;
1: len 6; hex 000000004bee; asc K ;;
2: len 7; hex 81000001610110; asc a ;;
3: len 3; hex 313233; asc 123;;
4: len 12; hex 7a68616f686f6e6766656e67; asc zhaohongfeng;;
*** (2) TRANSACTION:
TRANSACTION 731409, ACTIVE 10 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1128, 2 row lock(s)
MySQL thread id 12, OS thread handle 123145435422720, query id 407 localhost 127.0.0.1 root statistics
/* ApplicationName=GoLand 2024.1.6 */ select * from User where id = 2 for update
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 571 page no 4 n bits 72 index PRIMARY of table `test`.`user` trx id 731409 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 80000001; asc ;;
1: len 6; hex 000000004bee; asc K ;;
2: len 7; hex 81000001610110; asc a ;;
3: len 3; hex 313233; asc 123;;
4: len 12; hex 7a68616f686f6e6766656e67; asc zhaohongfeng;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 571 page no 4 n bits 72 index PRIMARY of table `test`.`user` trx id 731409 lock_mode X locks rec but not gap waiting
Record lock, heap no 5 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 80000002; asc ;;
1: len 6; hex 0000000afa28; asc (;;
2: len 7; hex 01000000f40235; asc 5;;
3: len 16; hex 79766574746540707269736d612e696f; asc yvette@prisma.io;;
4: len 12; hex 7a68616f686f6e6766656e67; asc zhaohongfeng;;
*** WE ROLL BACK TRANSACTION (2)
错误日志查看
开启死锁日志记录
-- 开启所有死锁信息的记录（MySQL 5.6+）
SET GLOBAL innodb_print_all_deadlocks = ON;
分析死锁日志
查看当前锁信息
SELECT * FROM performance_schema.data_locks; -- 查看当前锁信息