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.ht ml，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 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)