Concurrent insert cause deadlock? (未解决)

最近和percona也沟通过,但现象没有重现,目前未到到原因,mysql version:5.5.18-log Source distribution,Percona-XtraDB

Concurrent insert 3 rows then the deadlock happend.deadlock_config_id and idx_block_id index is not unique index

session 1:
insert into deadlock_test
(deadlock_config_id, block_id, type, gmt_create, gmt_modified)
values
(31643, 92352, 1, now(), now());
 
session 2:
insert into deadlock_test
(deadlock_config_id, block_id, type, gmt_create, gmt_modified)
values
(31643, 92353, 1, now(), now());
 
session 3:
insert into deadlock_test
(deadlock_config_id, block_id, type, gmt_create, gmt_modified)
values
(31643, 92354, 1, now(), now());
 
+-------+-------------------+------+----------+
| id | deadlock_config_id | type | block_id |
+-------+-------------------+------+----------
| 11111 | 31643 | 1 | 92352 |
| 11112 | 31643 | 1 | 92354 |
 
root@deadlock 11:36:56>show index from test_deadlock;
+---------------+------------+-------------- +-------------------+
| Table | Non_unique | Key_name | Column_name |
+---------------+------------+-------------- +-------------------+
| test_deadlock | 0 | PRIMARY | id |
| test_deadlock | 1 | deadlock_config_id | deadlock_config_id |
| test_deadlock | 1 | deadlock_config_id | type |
| test_deadlock | 1 | idx_block_id | block_id |
 
from dev.mysql.com(doc(refman(5.0(n(innodb-locks-set.html,we know that inserting into the same index gap need intetion lock,but insert idx_block_id values is different。
so i don't know why deadlock happend?
 
Session1:31643(X)92352(X)
Session2:31643(waiting)、92354(X)
Session3:31643(waiting)、92353(X)
 
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 100.242.138.197 deadlock 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 100.242.138.197 deadlock 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)

One Response to “Concurrent insert cause deadlock? (未解决)”

  1. […] 上次写过一篇Concurrent insert cause deadlock,当时原因未找到,经过@印风同学分析,目前有了结果: […]

Post a Comment