多线程更新MySQL（无线程更新同ID）却出现死锁问题咨询

这问题其实挺典型的——哪怕你觉得线程们完全碰不到相同的行，MySQL的锁机制还是可能在你看不见的地方埋下死锁的隐患。咱们结合你给出的UPDATE语句，一步步拆解原因：

核心原因分析 #

1. 多层子查询带来的不必要锁范围 #

你的UPDATE语句嵌套了多层子查询，而且子查询直接操作了目标表table。MySQL在执行这类包含同表子查询的UPDATE时，可能会先对整张表（或大范围数据）加读锁，因为子查询需要扫描符合DateString > ...、field_a = ?、id <= ?条件的数据。

如果查询优化器没选到最优索引，甚至触发全表扫描，那不同线程的子查询就会因为读锁和写锁的冲突互相阻塞。哪怕你的field_a参数不同，只要子查询的扫描范围有重叠（比如都覆盖了同一个日期区间），锁冲突就可能发生。

2. 间隙锁（Gap Locks）的隐形阻塞 #

InnoDB在默认的可重复读（RR）隔离级别下，会用间隙锁来防止幻读。你的子查询里有id <= ?和DateString > ...的范围条件，InnoDB会给这些范围对应的间隙（比如id在某个区间的空白位置）加锁。

哪怕不同线程的field_a完全不同，只要它们的id或DateString范围存在重叠的间隙，这些间隙锁就会互相阻塞。比如线程1的id <= 100，线程2的id <= 150，它们的间隙锁可能覆盖了同一个区间，就会形成循环等待，触发死锁。

3. 更新行的加锁顺序不一致 #

MySQL执行UPDATE时，是先定位要更新的行，加排他锁，再执行更新。你的语句里，子查询先计算出要更新的行集合，主语句再匹配这些行。如果多个线程要更新的行在表中的物理存储顺序是交叉的（比如线程1要更id1、3、5，线程2要更id2、4、6），它们加锁的顺序可能是1→2和2→1，这样就形成了循环等待，直接触发死锁。

解决建议 #

• 重构子查询，避免同表嵌套：把多层子查询改成先计算结果到临时表，再用临时表关联更新原表。比如：

  -- 先把计算结果存入临时表
  CREATE TEMPORARY TABLE temp_avg AS
  SELECT field_a, field_b, TRUNCATE(AVG(Sumfield_c), 2) avgfield_c, TRUNCATE(AVG(Sumfield_d), 2) avgfield_d 
  FROM (
      SELECT field_a, field_b, DateString, sum(field_c) Sumfield_c, sum(field_d) Sumfield_d 
      FROM table 
      WHERE DateString > DATE_FORMAT(SUBDATE(CURDATE(), 22), '%Y%m%d') 
        and field_a = ? 
        and id <= ? 
      GROUP BY field_a, field_b, DateString
  ) A 
  GROUP BY field_a, field_b;
  
  -- 再用临时表更新原表
  UPDATE table t
  JOIN temp_avg temp ON t.field_a = temp.field_a and t.field_b = temp.field_b
  SET t.Avgfield_c = temp.avgfield_c, t.Avgfield_d = temp.avgfield_d 
  WHERE t.id > ?;
  

  这样MySQL能更精准地锁定需要更新的行，减少不必要的锁范围。

• 添加合适的联合索引：给field_a、DateString、id建联合索引，比如CREATE INDEX idx_fielda_datestring_id ON table(field_a, DateString, id);。这样子查询能快速定位到目标数据，避免全表扫描，缩小锁的覆盖范围。

• 调整事务隔离级别（如果业务允许）：把隔离级别改成读已提交（RC），InnoDB在RC级别下不会使用间隙锁（除了唯一索引和外键场景），能大幅减少间隙锁导致的死锁。

• 统一加锁顺序：如果必须用多线程并行更新，确保所有线程按相同的顺序加锁（比如按field_a的字典序、或者id的升序处理更新任务），避免循环等待的情况。

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