title: Resolving MySQL gap lock
body: <p>Need help with resolving deadlock and understanding the reason which causes it</p>
  
  <p>Table:</p>
  
  <pre><code>CREATE TABLE `payments` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `billing_id` int(10) unsigned NOT NULL,
    `foreign_transaction_id` varchar(255) NOT NULL,
    `product_type` enum('invoice') DEFAULT NULL,
    `product_id` int(10) unsigned DEFAULT NULL,
    `subscription_id` int(10) unsigned DEFAULT NULL,
    `credit_id` int(10) unsigned DEFAULT NULL,
    `time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `type` enum('payment') DEFAULT NULL,
    `amount` decimal(8,2) NOT NULL DEFAULT '0.00',
    `fee` decimal(8,2) NOT NULL DEFAULT '0.00',
    `vat` decimal(8,2) DEFAULT '0.00',
    `status` enum('active','deleted') NOT NULL,
    `test` enum('no','yes') NOT NULL DEFAULT 'no',
    PRIMARY KEY (`id`),
    UNIQUE KEY `billing_id` (`billing_id`,`foreign_transaction_id`),
    KEY `subscription_id_type` (`subscription_id`,`type`),
    KEY `credit_id_type` (`credit_id`,`type`),
    KEY `product_type` (`product_type`,`product_id`),
    KEY `time` (`time`)
  ) ENGINE=InnoDB AUTO_INCREMENT=3679531 DEFAULT CHARSET=utf8;
  </code></pre>
  
  <p>Text from the "innodb status" report immediately after the deadlock occurs:</p>
  
  <pre><code>LATEST DETECTED DEADLOCK
  ------------------------
  2018-12-17 12:10:38 0x2f8
  *** (1) TRANSACTION:
  TRANSACTION 153838477815, ACTIVE 1 sec inserting
  mysql tables in use 1, locked 1
  LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
  MySQL thread id 446845346, OS thread handle 974, query id 88919281792 123.123.123.123 root update
  INSERT  INTO mydb.`payments`
              SET `billing_id` = '11', `foreign_transaction_id` = '1416436', `product_type` = 'invoice', `product_id` = '232886', `type` = 'payment', `amount` = '9.99', `fee` = '0.454545', `vat` = '0', `status` = 'active', `test` = 'no'
              ON DUPLICATE KEY UPDATE `billing_id` =  '11', `foreign_transaction_id` =  '1416436', `product_type` =  'invoice', `product_id` =  '232886', `type` =  'payment', `amount` =  '9.99', `fee` =  '0.454545', `vat` =  '0', `status` =  'active', `test` =  'no', `id` = LAST_INSERT_ID(`id`)
  *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
  RECORD LOCKS space id 1898595 page no 25610 n bits 488 index billing_id of table `mydb`.`payments` trx id 153838477815 lock_mode X locks gap before rec insert intention waiting
  Record lock, heap no 61 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
   0: len 4; hex 0000000b; asc     ;;
   1: len 6; hex 313431363434; asc 141644;;
   2: len 4; hex 00239264; asc  # d;;
  
  *** (2) TRANSACTION:
  TRANSACTION 153838477576, ACTIVE 1 sec inserting, thread declared inside InnoDB 1
  mysql tables in use 1, locked 1
  4 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 1
  MySQL thread id 446846795, OS thread handle 760, query id 88919279740 123.123.123.123 root update
  INSERT  INTO mydb.`payments`
              SET `billing_id` = '11', `foreign_transaction_id` = '1416430', `product_type` = 'invoice', `product_id` = '1317214', `type` = 'payment', `amount` = '9.99', `fee` = '0.454545', `vat` = '0', `status` = 'active', `test` = 'no'
              ON DUPLICATE KEY UPDATE `billing_id` =  '11', `foreign_transaction_id` =  '1416430', `product_type` =  'invoice', `product_id` =  '1317214', `type` =  'payment', `amount` =  '9.99', `fee` =  '0.454545', `vat` =  '0', `status` =  'active', `test` =  'no', `id` = LAST_INSERT_ID(`id`)
  *** (2) HOLDS THE LOCK(S):
  RECORD LOCKS space id 1898595 page no 25610 n bits 488 index billing_id of table `mydb`.`payments` trx id 153838477576 lock_mode X locks gap before rec
  Record lock, heap no 61 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
   0: len 4; hex 0000000b; asc     ;;
   1: len 6; hex 313431363434; asc 141644;;
   2: len 4; hex 00239264; asc  # d;;
  
  *** (2) WAITING FOR THIS LOCK TO BE GRANTED:
  RECORD LOCKS space id 1898595 page no 25610 n bits 488 index billing_id of table `mydb`.`payments` trx id 153838477576 lock_mode X locks gap before rec insert intention waiting
  Record lock, heap no 61 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
   0: len 4; hex 0000000b; asc     ;;
   1: len 6; hex 313431363434; asc 141644;;
   2: len 4; hex 00239264; asc  # d;;
  
  *** WE ROLL BACK TRANSACTION (1)
  </code></pre>
  
  <p>I guess the reason is in composite unique index "billing_id". I'd like to know how MySQL creates the lock gap step by step in this case. And what should be done for resolving it </p>