1

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)
4

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

  • Thanks Derek for the great explanation. It is a UNIQUE index. Obviously the reason for the UNIQUE index is to protect data integrity but do you think it is worth it to remove the UNIQUE constraint to avoid gap locking? – MYoussef Dec 11 '14 at 20:29
  • I wouldn't recommend removing the unique constraint, no. Instead find a proper way to handle deadlocks For starters, you should be re-applying transactions that result in deadlock. Beyond that, depending on your environment, there are settings that might make sense to disable gap locking – Derek Downey Dec 11 '14 at 20:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.