MySQL(5.7) InnoDB死锁咨询：非匹配更新与删除为何触发死锁

问题背景 #

有一个MySQL 5.7 InnoDB表sample_table，表结构如下：

CREATE TABLE `sample_table`
(
  `id`                      bigint(20)                             NOT NULL AUTO_INCREMENT,
  `status`                  varchar(63) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'NEW',
  `message`                 varchar(255) COLLATE utf8mb4_unicode_ci         DEFAULT NULL,
  `client_data`             json                                            DEFAULT NULL,
  `client_id`               bigint(20)                                      DEFAULT NULL,
  `relation_data_data`      json                                            DEFAULT NULL,
  `relation_data_id`        bigint(20)                                      DEFAULT NULL,
  `parent_relation_data_id` bigint(20)                                      DEFAULT NULL,
  `document_data`           json                                            DEFAULT NULL,
  `document_id`             bigint(20)                                      DEFAULT NULL,
  `import_id`               bigint(20)                                      DEFAULT NULL,
  `customer_number`         varchar(63) COLLATE utf8mb4_unicode_ci          DEFAULT NULL,
  `insurance_number`        varchar(63) COLLATE utf8mb4_unicode_ci          DEFAULT NULL,
  `parent_insurance_number` varchar(63) COLLATE utf8mb4_unicode_ci          DEFAULT NULL,
  `created_at`              datetime(6)                            NOT NULL,
  `updated_at`              datetime(6)                            NOT NULL,
  `data_hash`               varchar(255) COLLATE utf8mb4_unicode_ci         DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `index_sample_table_on_data_hash` (`data_hash`),
  KEY `index_sample_table_on_status` (`status`),
  KEY `index_sample_table_on_client_id` (`client_id`),
  KEY `index_sample_table_on_relation_data_id` (`relation_data_id`),
  KEY `index_sample_table_on_document_id` (`document_id`),
  KEY `index_sample_table_on_import_id` (`import_id`),
  KEY `index_sample_table_on_customer_number` (`customer_number`),
  KEY `index_sample_table_on_insurance_number` (`insurance_number`)
) ENGINE = InnoDB
  AUTO_INCREMENT = 1307766
  DEFAULT CHARSET = utf8mb4
  COLLATE = utf8mb4_unicode_ci
  ROW_FORMAT = DYNAMIC;

同时执行以下两条SQL时触发死锁：

1. DELETE语句：

DELETE FROM `sample_table` WHERE `sample_table`.`id` = 1

2. UPDATE语句：

UPDATE `sample_table` SET `sample_table`.`client_id` = 906991
WHERE `sample_table`.`customer_number` IS NULL
AND `sample_table`.`import_id` = 4123521
AND `sample_table`.`relation_data` IS NOT NULL

从业务逻辑看，UPDATE的查询条件本应匹配不到数据库中唯一的id=1的行，但死锁依然发生，结合show engine innodb status结果分析原因如下：

死锁发生的具体过程 #

从InnoDB死锁日志可以拆解出两个事务的锁等待链：

事务1（UPDATE操作） #

• 执行时，因查询条件包含customer_number IS NULL，会先走index_sample_table_on_customer_number二级索引，找到所有customer_number为NULL的行（包括id=1的行）。
• InnoDB会先给这些二级索引记录加X锁，之后需要回表获取对应行的主键索引X锁来执行更新，但此时id=1的主键X锁已被事务2持有，事务1进入等待状态。

事务2（DELETE操作） #

• 执行时通过主键索引找到id=1的行，先获取该行的主键索引X锁。
• 由于InnoDB需要维护二级索引的一致性，删除行时必须同步更新所有包含该行的二级索引（包括index_sample_table_on_customer_number），此时事务2需要获取该二级索引对应记录的X锁，但这个锁已被事务1持有，事务2也进入等待状态。

死锁形成 #

事务1持有index_sample_table_on_customer_number中id=1对应记录的X锁，等待事务2持有的主键X锁；事务2持有主键X锁，等待事务1持有的二级索引X锁，形成循环等待，触发死锁。

关键原因解释 #

1. UPDATE的索引遍历逻辑：即使UPDATE的最终过滤条件（import_id=4123521、relation_data IS NOT NULL）不匹配id=1的行，InnoDB依然会先通过customer_number IS NULL走二级索引遍历所有符合该条件的行，回表后才会检查其他条件，遍历过程中已给二级索引记录加锁。
2. 二级索引的锁维护：DELETE操作在获取主键锁后，必须更新关联的二级索引，因此需要获取对应二级索引记录的锁，与UPDATE的锁形成冲突。
3. NULL值的索引特性：customer_number为NULL的行会被包含在index_sample_table_on_customer_number索引中，所以UPDATE会遍历到id=1的行。

解决方案建议 #

• 优化索引：创建联合索引(customer_number, import_id, relation_data)，让UPDATE直接通过联合索引过滤掉不符合条件的行，避免遍历到id=1的记录，缩小锁范围。
• 固定事务顺序：如果业务允许，让DELETE和UPDATE操作按固定顺序执行，避免循环等待。
• 增加重试机制：在应用层为死锁场景增加重试逻辑，InnoDB会自动回滚其中一个事务，重试可保证操作最终执行成功。

内容的提问来源于stack exchange，提问作者mario199