自动提交模式下单表UPDATE引发MySQL死锁的原因咨询

开发者阿泽

2026-8-19

单表自动提交模式下InnoDB死锁的原因分析与解决

问题背景

常规认知里死锁多发生在跨表事务场景：两个并行事务各自锁定一张表后，尝试获取对方已锁定表的锁，最终互相等待触发死锁。但当前场景特殊：

数据库处于自动提交模式，无显式事务
触发死锁的SQL仅涉及单表
日志频繁出现死锁错误：

06:35:06 | 1213 : Deadlock found when trying to get lock; try restarting transaction

触发死锁的SQL语句：

UPDATE request_table 
    SET  report_id ='', 
        change_date ='2022-09-14 06:35:03' 
WHERE customer_id_1 = '283649'
AND customer_id_2 = '2893463'
AND report_request_id ='' 
AND report_type ='vat'

补充表结构（无触发器或自动处理流程）：

--
-- structure for table `request_table`
--
CREATE TABLE `request_table` (
  `id` int(11) NOT NULL,
  `customer_id_1` int(11) NOT NULL,
  `customer_id_2` int(11) NOT NULL,
  `change_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `report_type` varchar(255) COLLATE latin1_german1_ci NOT NULL,
  `report_request_id` varchar(255) COLLATE latin1_german1_ci DEFAULT NULL,
  `start_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `end_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `submitted_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `report_processing_status` varchar(255) COLLATE latin1_german1_ci NOT NULL,
  `report_id` varchar(255) COLLATE latin1_german1_ci DEFAULT NULL,
  `processing_message` varchar(255) COLLATE latin1_german1_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_german1_ci PACK_KEYS=0;

--
-- Indices for table `request_table`
--
ALTER TABLE `request_table`
  ADD PRIMARY KEY (`id`),
  ADD KEY `customer_id_1` (`customer_id_2`),
  ADD KEY `customer_id_2` (`portal_account_id`),
  ADD KEY `report_request_id` (`report_request_id`),
  ADD KEY `report_type` (`report_type`),
  ADD KEY `report_processing_status` (`report_processing_status`),
  ADD KEY `submitted_date` (`submitted_date`);

--
-- AUTO_INCREMENT for table `request_table`
--
ALTER TABLE `request_table`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=38030718;

疑问：并行进程更新同一行不该触发死锁，且所有SQL都很简单，为何出现死锁？

核心原因分析

即使是自动提交的单表UPDATE，InnoDB也可能触发死锁，核心问题出在索引失效导致的锁竞争和InnoDB的锁机制：

索引定义错误+无匹配索引导致全表扫描锁竞争
你的UPDATE语句WHERE条件包含customer_id_1 + customer_id_2 + report_request_id + report_type，但当前表的索引存在明显错误：
- ADD KEY customer_id_1 (customer_id_2)：索引名和关联字段不匹配，完全无法匹配查询条件里的customer_id_1
- ADD KEY customer_id_2 (portal_account_id)：关联的portal_account_id字段不存在，这个索引完全无效
  这导致执行UPDATE时，InnoDB只能走全表扫描，需要逐个锁定扫描到的行。如果两个并行UPDATE进程的扫描顺序相反（比如一个按id升序扫，一个降序扫），就会出现：
  进程A锁定行1，尝试锁定行2；进程B锁定行2，尝试锁定行1，互相等待触发死锁。
Next-Key锁的隐性影响
InnoDB默认隔离级别是REPEATABLE READ，该级别下UPDATE会使用Next-Key锁（行锁+间隙锁）防止幻读。当WHERE条件无精确索引匹配时，InnoDB会锁定整个数据间隙，若有其他并行操作锁定相邻间隙或行，就可能触发死锁。
自动提交的隐式事务特性
自动提交模式下，单个SQL本身就是一个隐式事务，InnoDB会为这个事务持有锁直到语句执行完成。如果SQL因全表扫描耗时较长，锁持有时间增加，会大幅提升锁竞争和死锁概率。

解决方案

修复索引并创建匹配的联合索引
首先修复错误索引：

-- 删除无效索引
ALTER TABLE request_table DROP KEY customer_id_1;
ALTER TABLE request_table DROP KEY customer_id_2;
-- 创建正确的单字段索引
ALTER TABLE request_table ADD KEY customer_id_1 (customer_id_1);
ALTER TABLE request_table ADD KEY customer_id_2 (customer_id_2);

然后为UPDATE的WHERE条件创建联合索引，让InnoDB直接定位目标行，避免全表扫描：

ALTER TABLE request_table ADD KEY idx_update_query (customer_id_1, customer_id_2, report_request_id, report_type);

调整事务隔离级别（可选）
如果业务允许，可将隔离级别调整为READ COMMITTED，该级别下InnoDB仅使用行锁，不会生成间隙锁，能降低间隙锁导致的死锁概率。
添加应用层死锁重试逻辑
极端情况下死锁仍可能发生，建议在应用层捕获1213错误，增加1-3次重试逻辑。

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

自动提交模式下单表UPDATE引发MySQL死锁的原因咨询

问题背景 #

核心原因分析 #

解决方案 #

问题背景

核心原因分析

解决方案