MySQL InnoDB自动提交引发死锁：三类核心技术疑问求助

背景场景 #

测试环境存在两张关联表，表结构如下：

表1（table_1）结构 #

create table table_1
(
    id    int auto_increment
        primary key,
    data1 text null,
    data2 text null
);

表2（table_2）结构 #

create table table_2
(
    id    int auto_increment
        primary key,
    t1_id int  null,
    data1 text null,
    data2 text null,
    constraint table_2_table_1_id_fk
        foreign key (t1_id) references table_1 (id)
            on update cascade on delete cascade
);

测试数据：table_1含30条记录，table_2含60条记录（每2条table_2记录关联1条table_1记录）。

编写PHP脚本，通过table_2的过滤条件循环更新table_1的部分记录，启动150个脚本实例后触发Deadlock错误。尝试过子查询排序、CTE、IN子句等优化手段，均无法解决死锁问题。

核心疑问 #

1. 为何已按固定顺序排序记录，仍会出现死锁？（附死锁时SHOW ENGINE INNODB STATUS;输出）
2. 为何使用临时表存储待更新ID的方式能解决死锁？
3. 已开启autocommit，为何显式事务无死锁，默认自动提交却会出现死锁？

解答 #

疑问1：排序后仍死锁的原因 #

即使查询指定了排序，InnoDB的锁机制与事务执行逻辑仍可能导致死锁：

• 外键触发的锁顺序不一致：由于table_2存在关联table_1的外键，关联更新时InnoDB会先锁定table_2的匹配行，再锁定对应table_1行。若多个事务锁定table_2行的顺序不同（比如不同脚本实例的查询条件命中不同table_2行组合），即便最终更新的table_1行是排序的，前置的table_2锁顺序仍可能形成循环等待。
• 间隙锁/Next-Key Lock的干扰：若查询使用范围条件（如LIKE模糊匹配、范围比较），InnoDB会自动添加间隙锁，锁范围可能重叠，不同事务获取锁的顺序交叉后就会引发死锁。
• 锁的分批获取机制：即便写了排序的子查询，InnoDB执行时可能分批次获取锁，而非一次性按排序顺序锁定所有行，中间过程中其他事务可能获取反向顺序的锁，进而形成死锁。

疑问2：临时表解决死锁的原因 #

临时表通过拆分锁获取流程从根源避免死锁：

1. 第一阶段：查询所有待更新的table_1 ID并存入临时表，此过程仅读取数据，不加排他锁，不会与其他事务冲突。
2. 第二阶段：从临时表按固定顺序（如ID升序）取出ID执行更新，确保所有事务都以相同顺序获取table_1的排他锁，彻底避免循环等待。
   另外，临时表是会话私有对象，其查询结果固定，不会因其他事务的修改而动态变化，消除了锁范围变动带来的死锁风险。

疑问3：autocommit与显式事务的死锁差异 #

• autocommit模式：每条SQL语句都是独立事务。关联更新时，单语句事务会依次锁定table_2和table_1的行，不同事务的锁顺序可能反向（比如事务A先锁table_2行1再锁table_1行1，事务B先锁table_2行2再锁table_1行2，若table_2行1关联table_1行2、table_2行2关联table_1行1，就会形成循环等待）。
• 显式事务：可将查询待更新ID与更新操作纳入同一事务，先按固定顺序获取所有需要的锁（比如先锁定所有待更新table_1行，再处理table_2），或严格控制锁的获取顺序，确保所有事务遵循相同逻辑，避免循环等待。此外，显式事务中可先读取所有待更新ID，再按顺序执行更新，原理与临时表类似，只是将临时表替换为内存ID列表。

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