MariaDB并发INSERT SELECT死锁问题排查及解决方案咨询

核心死锁原因分析 #

从死锁日志和你的业务场景来看，死锁的触发逻辑很明确：
两个并发执行的INSERT ... SELECT事务，在查询统计数据阶段（日志里的Creating sort index状态），因为计算中位数需要排序、聚合函数需要扫描数据，在默认的REPEATABLE READ隔离级别下，InnoDB会对扫描范围加**共享间隙锁（S Gap Lock）**以防止幻读。当两个事务的锁范围重叠时，就出现了循环等待：

• 事务1持有部分行锁，等待事务2持有的间隙锁的X插入意向锁
• 事务2持有间隙锁，同时等待表级的AUTO-INC锁（插入需要自增ID）
  最终触发死锁，回滚其中一个事务。

针对你的疑问的直接解答 #

1. 死锁确实和ORDER BY/聚合函数直接相关
   计算中位数必须对数据做ORDER BY排序，MAX/MIN/AVG等聚合操作也需要扫描指定范围的数据，这些操作都会让SELECT阶段持有更大范围的锁，大幅提升了并发锁冲突的概率。

2. READ COMMITTED是可行方案，但并非唯一最优解
   切换到READ COMMITTED隔离级别后，InnoDB会关闭普通查询的间隙锁（仅保留唯一索引和外键的间隙锁），只锁定实际存在的行，能显著减少锁冲突。但需要注意：

   • 该级别允许不可重复读，但你的场景是统计历史数据并插入，不可重复读对结果几乎无影响
   • 外键索引fk_device的间隙锁可能依然存在，需要结合其他优化进一步降低冲突

具体优化建议 #

1. 调整事务隔离级别（快速见效） #

直接将隔离级别改为READ COMMITTED，可以快速减少间隙锁带来的冲突：

-- 当前会话生效
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
-- 全局生效（新会话或重启后生效）
SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;

在my.cnf中配置永久生效：

[mysqld]
transaction-isolation = READ-COMMITTED

2. 优化INSERT ... SELECT语句，缩小锁范围 #

• 严格限定时间窗口：统计时必须通过WHERE子句精准限定时间范围（比如timestamp >= '2024-05-06 20:07:00' AND timestamp < '2024-05-06 20:08:00'），避免扫描全表，减少锁覆盖的行数。
• 拆分大事务：如果一次插入多个设备/单元的统计数据，拆分为单条设备/单元的INSERT语句，每个小事务只锁定对应设备的行，降低锁冲突概率。
• 用临时表预计算：先将统计结果写入会话级临时表，再从临时表插入目标表。临时表不会加全局锁，能大幅缩短对tbl_value的锁持有时间：

-- 预计算统计数据到临时表
CREATE TEMPORARY TABLE temp_stats AS
SELECT device_id, unit_id, MEDIAN(value) AS median, MIN(value) AS min_val, MAX(value) AS max_val, AVG(value) AS avg_val
FROM tbl_value 
WHERE timestamp BETWEEN '2024-05-06 20:00:00' AND '2024-05-06 20:59:59'
GROUP BY device_id, unit_id;

-- 插入到目标统计表
INSERT INTO tbl_aggregated_stats (device_id, unit_id, median, min_val, max_val, avg_val)
SELECT device_id, unit_id, median, min_val, max_val, avg_val FROM temp_stats;

DROP TEMPORARY TABLE temp_stats;

3. 索引优化，减少扫描范围 #

给tbl_value建立复合索引：(device_id, unit_id, timestamp, value)，这样统计时能快速定位到指定设备、时间范围的数据，避免全表扫描，同时覆盖排序和聚合所需字段，缩短锁持有时间。

4. 调整AUTO-INC锁模式（MariaDB专属） #

MariaDB默认的consecutive AUTO-INC锁模式在高并发插入时会持有锁更久，改为interleaved模式允许事务交错获取自增ID，减少AUTO-INC锁的等待：

[mysqld]
innodb_autoinc_lock_mode = 2

注：该模式下自增ID可能不连续，但不影响统计业务。

5. 应用层重试兜底 #

即使做了以上优化，极端场景下仍可能出现死锁。在应用层捕获错误码1213（死锁错误），添加自动重试逻辑，重试次数建议3-5次即可。

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