MySQL队列归档与汇总流程死锁问题排查及解决咨询

问题背景 #

系统表结构 #

• queue：存储待处理的新条目
• log_yyyyMMdd：每日归档已处理的队列条目
• summary：每小时汇总每日日志表数据，用于报表生成

数据流转流程 #

1. 队列条目归档流程：

   • 启动事务：start transaction
   • 写入归档表：insert into log_yyyyMMdd (columns) select columns from queue where id = 123
   • 删除原队列数据：delete from queue where id = 123
   • 提交事务：commit

2. 数据汇总流程：

   • 写入临时表：insert into temp_table (summarized_columns) select summarized_columns from log_yyyyMMdd union all select summarized_columns from queue where date = yyyyMMdd
   • 启动事务：start transaction
   • 删除旧汇总数据：delete from summary where date = yyyyMMdd
   • 插入新汇总数据：insert into summary (summarized_columns) select summarized_columns from temp_table
   • 提交事务：commit

用户引入临时表试图避免锁冲突，但仍出现死锁，死锁日志如下：

------------------------
LATEST DETECTED DEADLOCK
------------------------
2024-05-05 06:10:14 0x7f5062220700
*** (1) TRANSACTION:
TRANSACTION xxx, ACTIVE 14 sec inserting
mysql tables in use 2, locked 2
LOCK WAIT 4 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 43518, OS thread handle 139982692251392, query id 4737203875 ... Sending data
    INSERT INTO log_20240502 
    (...) 
    SELECT ...
    -- This is query 1.ii
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 3456 page no 12297 n bits 120 index PRIMARY of table `transactions`.`log_20240502` trx id xxx lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 39; compact format; info bits 0
 0: ...

*** (2) TRANSACTION:
TRANSACTION yyy, ACTIVE 16 sec fetching rows
mysql tables in use 3, locked 3
14101 lock struct(s), heap size 1269968, 519680 row lock(s)
MySQL thread id 43548, OS thread handle 139983220508416, query id 4737203109 ... Sending data
    INSERT INTO summary_automated_temp (...)
    SELECT ...
    FROM log_20240502 
    ...
    -- This is query 2.i
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 3456 page no 12297 n bits 120 index PRIMARY of table     `transactions`.`log_20240502` trx id yyy lock mode S
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

 -- UPDATED WITH MORE LOGS BELOW

 Record lock, heap no 2 PHYSICAL RECORD: n_fields 39; compact format; info bits 0
 ...

 Record lock, heap no 51 PHYSICAL RECORD: n_fields 39; compact format; info bits 0
 ...
 
 *** (2) WAITING FOR THIS LOCK TO BE GRANTED:
 RECORD LOCKS space id 3366 page no 59172 n bits 112 index PRIMARY of table `transactions`.`queue` trx id 421459371442864 lock mode S locks rec but not gap waiting
 Record lock, heap no 41 PHYSICAL RECORD: n_fields 42; compact format; info bits 0
  0: len 30; hex      30623936613266302d373133342d343465632d383364622d363330306535; asc 0b96a2f0-7134-44ec-83db-6300e5; (total 36 bytes);

用户疑问：innodb_lock_wait_timeout设置为50，但死锁在10多秒后触发，不确定是死锁还是锁等待超时，MySQL判定为死锁，需解决建议。

死锁原因分析 #

1. 循环锁链形成：
   • 事务1（归档操作）：执行insert ... select时，需要对log_20240502加X插入意向锁，但事务2已持有该表主键索引的大范围S锁，导致事务1等待。
   • 事务2（汇总操作）：读取log_20240502的同时，还需读取queue表中指定日期的数据，此时它正在等待queue表某行的S锁——该行大概率被其他归档事务持有X锁，形成循环等待链，触发死锁。
2. 等待时间的说明：
   innodb_lock_wait_timeout是锁等待超时的阈值，而死锁是InnoDB主动检测到循环等待后立即回滚代价较小的事务，无需等到超时时间，因此10多秒触发死锁是正常的，死锁检测与锁等待超时是完全独立的机制。

解决建议 #

1. 优化汇总查询的锁范围 #

• 给log_yyyyMMdd表创建覆盖索引，避免全表扫描带来的大范围S锁：
  假设汇总按date分组，统计col1, col2字段，创建索引：

  CREATE INDEX idx_log_date_stats ON log_yyyyMMdd (date, col1, col2);
  

  汇总查询可直接通过索引获取数据，减少锁覆盖范围。
• 拆分union all的两个查询：先读取log_yyyyMMdd数据写入临时表，再单独读取queue表符合条件的数据，分开执行避免同时持有两个表的锁。

2. 调整归档事务的操作逻辑 #

将归档事务中的操作顺序调换，或优化为内存读取后再执行写入删除：

start transaction;
-- 先从queue读取数据到内存，再执行以下两步
delete from queue where id = 123;
insert into log_yyyyMMdd (columns) values (...); -- 直接使用内存中的数据，避免再次查询queue
commit;

缩短事务持有锁的时间，降低冲突概率。

3. 控制汇总任务的并发与执行时机 #

• 调整汇总任务的执行时间，错开业务高峰期（如每小时的低峰时段），减少与归档操作的并发冲突。
• 确保同一时间只有一个汇总任务处理同一日期的数据，避免多实例重复加锁。

4. 优化临时表的使用 #

使用会话级临时表（CREATE TEMPORARY TABLE），避免不同会话间的干扰，同时减少临时表的锁竞争。

5. 调整InnoDB锁相关参数 #

• 确保innodb_deadlock_detect=ON（默认开启），保证死锁被及时检测；若死锁频繁，可适当降低innodb_lock_wait_timeout，但需权衡锁等待超时的概率。
• 给queue表的id和date字段添加主键或唯一索引，避免查询时的表扫描和大范围锁。

补充说明 #

死锁与锁等待超时是两种不同机制：死锁是循环等待导致，InnoDB主动检测并回滚；锁等待超时是单个事务等待锁超过阈值后中断。你的情况属于死锁，MySQL的判定准确。

内容的提问来源于stack exchange，提问作者Chor Wai Chun