Yet another MySQL delete / insert deadlock
<p>Recently I have run into MySQL deadlocks happening because of the following transactions being run parallelly:</p>

<pre><code>(given `ei_id` and `dst_site`)
    SELECT id from item_specifics WHERE ei_id=X AND dst_site=Y;
(run only if any ids from above select)
    DELETE FROM item_specifics WHERE id in (2,3,1);
(next multiple inserts are executed) e.g.
    INSERT INTO item_specifics (category_id, name, value, dst_name, dst_value,
                                src_site, dst_site, ebay_category_id, type,
                                ei_id, name_translation_source, value_translation_source)
        VALUES (NULL, 'MPN', '65104703', 'MPN', '65104703',
                'UK', 'IT', NULL, 'S',
                 72111556, 'Y', 'Y');
    INSERT INTO item_specifics (category_id, name, value, dst_name, dst_value,
                            src_site, dst_site, ebay_category_id, type,
                            ei_id, name_translation_source, value_translation_source)
        VALUES (NULL, NULL, NULL, 'Talia', 'L',
                'UK', 'IT', NULL, 'D',
                72111556, 'Y', 'Y');
</code></pre>

<p>Table definition:</p>

<pre><code>CREATE TABLE `item_specifics` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `when_created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `category_id` int(11) DEFAULT NULL,
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `value` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `dst_name` varchar(255) DEFAULT NULL,
  `dst_value` varchar(255) DEFAULT NULL,
  `src_site` varchar(4) NOT NULL,
  `dst_site` varchar(4) NOT NULL,
  `ebay_category_id` varchar(10) DEFAULT NULL,
  `type` varchar(1) NOT NULL DEFAULT 'S' COMMENT 'S - source, D - destination',
  `ei_id` int(11) DEFAULT NULL,
  `state` varchar(1) NOT NULL DEFAULT 'A',
  `name_translation_source` char(1) DEFAULT NULL,
  `value_translation_source` char(1) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `ei_id` (`ei_id`,`dst_site`,`name`,`value`),
  KEY `category_id` (`category_id`),
  KEY `idx_item_specifics_dst` (`src_site`,`dst_site`,`dst_name`,`dst_value`,`ebay_category_id`),
  KEY `ebay_category_id` (`ebay_category_id`),
  KEY `name_dst_name` (`name`,`dst_name`),
  KEY `value_dst_value` (`value`,`dst_value`),
  KEY `dst_site` (`dst_site`),
  KEY `idx_platform2` (`platform`,`value`,`name`,`src_site`),
  CONSTRAINT `item_specifics_ibfk_2` FOREIGN KEY (`category_id`)
         REFERENCES `ebay_categories` (`id`),
  CONSTRAINT `item_specifics_ibfk_5` FOREIGN KEY (`ei_id`)
         REFERENCES `original_items` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=871759967 DEFAULT CHARSET=utf8
            STATS_PERSISTENT=0 STATS_AUTO_RECALC=0
</code></pre>

<p><code>SHOW ENGINE INNODB STATUS</code>:</p>

<pre><code>------------------------
LATEST DETECTED DEADLOCK
------------------------
2016-10-18 11:47:49 7f9e6f72c700
*** (1) TRANSACTION:
TRANSACTION 189044299927, ACTIVE 0 sec inserting
mysql tables in use 2, locked 2
LOCK WAIT 13 lock struct(s), heap size 2936, 24 row lock(s), undo log entries 11
MySQL thread id 121701505, OS thread handle 0x7fa078764700, query id 89285938631 10.0.25.162 consumer update
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 153 page no 1422842 n bits 480 index `ei_id` of table `wi`.`item_specifics` trx id 189044299927 lock mode S waiting
*** (2) TRANSACTION:
TRANSACTION 189044300080, ACTIVE 0 sec inserting
mysql tables in use 2, locked 2
15 lock struct(s), heap size 2936, 35 row lock(s), undo log entries 17
MySQL thread id 121711228, OS thread handle 0x7f9e6f72c700, query id 89285942437 10.0.27.182 consumer update
INSERT INTO item_specifics (category_id, name, value, dst_name, dst_value, src_site, dst_site, ebay_category_id, type, ei_id, name_translation_source, value_translation_source) VALUES (NULL, NULL, NULL, 'MPN', '65104703', 'UK', 'IT', NULL, 'D', 72111556, 'Y', 'Y')
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 153 page no 1422842 n bits 480 index `ei_id` of table `wi`.`item_specifics` trx id 189044300080 lock_mode X locks rec but not gap
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 153 page no 1422842 n bits 480 index `ei_id` of table `wi`.`item_specifics` trx id 189044300080 lock_mode X locks gap before rec insert intention waiting
*** WE ROLL BACK TRANSACTION (1)
</code></pre>

<p>The transactions are run with distinct <code>(ei_id, dst_site)</code> but it can happen that for the 2 parallel transactions the <code>ei_id</code> is common and only <code>dst_site</code> differs. </p>

<p>What's interesting the deadlock query printed by innodb status always shows NULL values for <code>name</code> and <code>value</code> fields (it's a valid case of course). Also note there is no query at all for the (1) transactions.</p>

<p>The inserts are run in order of <code>name</code> and <code>value</code> in descending order (which implies NULL <code>name</code> and <code>value</code> inserts are done at the end).</p>

<p>What is interesting for me the most is why such deadlock happens?
I don't have a problem with it, since the retry operation gets the job done but I've been fighting with this for some days now and I'm just curious what is the reason and if it's possible to reproduce it locally.</p>

<p>Actions that didn't help:</p>

<ul>
<li>Adding <code>FOR UPDATE</code> to the select query</li>
<li>Moving select query outside the transaction</li>
</ul>