最近，由于以下事务并行运行，我遇到了 MySQL 死锁:

(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');

表定义:

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

显示引擎 INNODB 状态:

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

事务使用不同的 (ei_id, dst_site) 运行，但对于 2 个并行事务，ei_id 是常见的并且只有 dst_site不同。

有趣的是，innodb status 打印的死锁查询总是显示 name 和 value 字段的 NULL 值(这当然是有效的情况)。另请注意，对于 (1) 交易根本没有查询。

插入按 name 和 value 降序运行(这意味着 NULL name 和 value 插入在最后完成)。

我最感兴趣的是为什么会出现这样的死锁？ 我对此没有问题，因为重试操作完成了工作，但我已经为此奋斗了几天，我只是好奇是什么原因以及是否有可能在本地重现它。

没有帮助的行为:

• 将 FOR UPDATE 添加到选择查询
• 将选择查询移出事务

最佳答案

将 FOR UPDATE 添加到 SELECT 的末尾。这会给引擎一个线索，表明您将要执行某项操作。它很可能将死锁转变为“等待”，这具有更小的侵入性。

(已添加)如果没有 FOR UPDATE，它可以在意识到需要锁定某些行之前深入到事务中。到那时，它所能做的就是死锁，而不仅仅是等待。

此外，对您的应用进行编码以在确实发生死锁时重试整个事务。

关于mysql - 另一个 MySQL 删除/插入死锁，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/40108686/