MySQL Latest Deadlock Explanation

deadlockMySQLmysql-5.5performance

Can someone explain this deadlock to me? It seems as if there are 2 inserts happening within milliseconds of each other with the same Primary Key. I'm not sure how this can cause a deadlock. At most a duplicate key error. Please let me know.

   LATEST DETECTED DEADLOCK
    ------------------------
    141210 19:12:08
    *** (1) TRANSACTION:
    TRANSACTION 5D33C809, ACTIVE 1 sec inserting
    mysql tables in use 1, locked 1
    LOCK WAIT 4 lock struct(s), heap size 1248, 2 row lock(s), undo log entries 1
    MySQL thread id 10092312, OS thread handle 0x7fbc69898700, query id 16041618913 xxxxx.net 10.x.x.x prod_user update
    INSERT INTO `shipments` (`applicant_id`, `courier_id`, `created_at`, `shipment_type_id`, `status_time`, `tracking_number`, `updated_at`) VALUES (4621087, 3, '2014-12-10 19:12:07', 1, '2014-12-10 18:42:00', '5068214830', '2014-12-10 19:12:07')
    *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
    RECORD LOCKS space id 2536 page no 18587 n bits 552 index `index_shipments_on_ci_ai_tn_sti` of table `prod`.`shipments` trx id 5D33C809 lock_mode X locks gap before rec insert intention waiting
    Record lock, heap no 191 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
     0: len 4; hex 80000003; asc     ;;
     1: len 4; hex 80468328; asc  F (;;
     2: len 10; hex 33353032373538363034; asc 3502758604;;
     3: len 4; hex 80000001; asc     ;;
     4: len 4; hex 8044fe80; asc  D  ;;

    *** (2) TRANSACTION:
    TRANSACTION 5D33C810, ACTIVE 1 sec inserting, thread declared inside InnoDB 1
    mysql tables in use 1, locked 1
    4 lock struct(s), heap size 1248, 2 row lock(s), undo log entries 1
    MySQL thread id 10098192, OS thread handle 0x7fafa1434700, query id 16041619033 xxxxx.net 10.x.x.x prod_user update
    INSERT INTO `shipments` (`applicant_id`, `courier_id`, `created_at`, `shipment_type_id`, `status_time`, `tracking_number`, `updated_at`) VALUES (4621087, 3, '2014-12-10 19:12:08', 1, '2014-12-10 18:42:00', '5068214830', '2014-12-10 19:12:08')
    *** (2) HOLDS THE LOCK(S):
    RECORD LOCKS space id 2536 page no 18587 n bits 552 index `index_shipments_on_ci_ai_tn_sti` of table `prod`.`shipments` trx id 5D33C810 lock mode S locks gap before rec
    Record lock, heap no 191 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
     0: len 4; hex 80000003; asc     ;;
     1: len 4; hex 80468328; asc  F (;;
     2: len 10; hex 33353032373538363034; asc 3502758604;;
     3: len 4; hex 80000001; asc     ;;
     4: len 4; hex 8044fe80; asc  D  ;;

    *** (2) WAITING FOR THIS LOCK TO BE GRANTED:
    RECORD LOCKS space id 2536 page no 18587 n bits 552 index `index_shipments_on_ci_ai_tn_sti` of table `prod`.`shipments` trx id 5D33C810 lock_mode X locks gap before rec insert intention waiting
    Record lock, heap no 191 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
     0: len 4; hex 80000003; asc     ;;
     1: len 4; hex 80468328; asc  F (;;
     2: len 10; hex 33353032373538363034; asc 3502758604;;
     3: len 4; hex 80000001; asc     ;;
     4: len 4; hex 8044fe80; asc  D  ;;

    *** WE ROLL BACK TRANSACTION (2)

Best Answer

From the output the two inserts are doing gap locking on the index index_shipments_on_ci_ai_tn_sti. Without knowing the table definition, I think the index is a UNIQUE index.

Here is a decent blog explaining the reason UNIQUE constraints can cause deadlocks on concurrent inserts. Snippet here:

Mysql innodb engine performs row locking on inserts. If column A has a unique key constraint, and we are adding the value "bbb" for column A in an insert statement, mysql needs to lock any gap in the index between the two current records where "bbb" will be inserted at.

More information on gap locking can be found in the docs: http://dev.mysql.com/doc/refman/5.6/en/innodb-record-level-locks.html

Related Question