1、通过show engine innodb status;
获取死锁日志。
------------------------
LATEST DETECTED DEADLOCK
------------------------
2021-01-25 09:44:09 0x2a0c
*** (1) TRANSACTION:
TRANSACTION 2782972, ACTIVE 103 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 7 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 1
MySQL thread id 479, OS thread handle 12268, query id 126238 localhost 127.0.0.1 root update
insert into backup_taskIds(BACKUP_TASK_ID_, PROC_INST_ID_, TASK_STATUS, CAN_WITHDRAW)
VALUE( NAME_CONST('param_taskId',_utf8'11883911' COLLATE 'utf8_general_ci'), NAME_CONST('param_processInstanceId',_utf8'11883896' COLLATE 'utf8_general_ci'), 0, 1)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 75260 page no 305 n bits 728 index backup_taskIds_procInstanceId of table `jcerp_139`.`backup_taskids` trx id 2782972 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 560 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 8; hex 3131383835313939; asc 11885199;;
1: len 8; hex 3131383835323036; asc 11885206;;
*** (2) TRANSACTION:
TRANSACTION 2782971, ACTIVE 103 sec inserting, thread declared inside InnoDB 1
mysql tables in use 1, locked 1
6 lock struct(s), heap size 1136, 5 row lock(s), undo log entries 1
MySQL thread id 476, OS thread handle 10764, query id 126236 localhost 127.0.0.1 root update
insert into backup_taskIds(BACKUP_TASK_ID_, PROC_INST_ID_, TASK_STATUS, CAN_WITHDRAW)
VALUE( NAME_CONST('param_taskId',_utf8'11883948' COLLATE 'utf8_general_ci'), NAME_CONST('param_processInstanceId',_utf8'11883941' COLLATE 'utf8_general_ci'), 0, 1)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 75260 page no 305 n bits 728 index backup_taskIds_procInstanceId of table `jcerp_139`.`backup_taskids` trx id 2782971 lock_mode X locks gap before rec
Record lock, heap no 560 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 8; hex 3131383835313939; asc 11885199;;
1: len 8; hex 3131383835323036; asc 11885206;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 75260 page no 305 n bits 728 index backup_taskIds_procInstanceId of table `jcerp_139`.`backup_taskids` trx id 2782971 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 560 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 8; hex 3131383835313939; asc 11885199;;
1: len 8; hex 3131383835323036; asc 11885206;;
*** WE ROLL BACK TRANSACTION (2)
由上日志可分析:
事务1等待space id 75260 page no 305 n bits 728的X锁。
事务2持有space id 75260 page no 305 n bits 728的X锁、并等待space id 75260 page no 305 n bits 728的X锁。
疑问:那事务2为什么持有还要等待锁呢?
猜测:事务2先持有锁,然后事务1等待锁,再事务2等待锁,MySQL锁机制需要排队?事务2的第二次获取锁需要等待前面的事务都完成才能拿到?