转载自Simple Life
本文链接地址: 一个线上死锁问题分析
一个线上死锁问题分析
死锁日志如下:
TRANSACTION 48AA4BB9, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 6 lock struct(s), heap size 1248, 4 row lock(s), undo log entries 2
MySQL thread id 1409173, OS thread handle 0x5659f940, query id 1084083936 10.246.138.197 bop_libra update
insert into deadlock_test
(deadlock_config_id, block_id, type, gmt_create, gmt_modified)
values
(31643, 92354, 1, now(), now());
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 17 page no 161 n bits 584 index `idx_block_id` of table `deadlock`.`test_deadlock` trx id 48AA4BB9 lock_mode X insert intention waiting
*** (2) TRANSACTION:
TRANSACTION 48AA4BBF, ACTIVE 0 sec inserting, thread declared inside InnoDB 500
mysql tables in use 1, locked 1
5 lock struct(s), heap size 1248, 3 row lock(s), undo log entries 2
MySQL thread id 1393832, OS thread handle 0x7699f940, query id 1084083946 10.246.138.197 bop_libra update
insert into deadlock_test
(deadlock_config_id, block_id, type, gmt_create, gmt_modified)
values
(31643, 92353, 1, now(), now());
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 17 page no 161 n bits 584 index `idx_block_id` of table `deadlock`.`test_deadlock` trx id 48AA4BBF lock_mode X
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 17 page no 161 n bits 584 index `idx_block_id` of table `deadlock`.`test_deadlock` trx id 48AA4BBF lock_mode X insert intention waiting
*** WE ROLL BACK TRANSACTION (2)
表上有聚集索引和二级索引,死锁发生在二级索引idx_block_id上。
session1: