GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

Concurrent insert cause deadlock?

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Concurrent insert cause deadlock?

    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)

  • #2
    btw:deadlock_config_id and idx_block_id index is not unique index

    Comment

    Working...
    X