MariaDB 10.5.12 Galera集群两条UPDATE事务死锁原因咨询

核心触发逻辑 #

这个死锁是典型的不同事务加锁顺序相反导致的InnoDB层死锁，和Galera集群本身无关，具体逻辑如下：

1. 批量更新语句索引缺失
   你提供的事务1是范围更新语句：

UPDATE `trt_employees` SET `availability`=
  CASE 
    WHEN `availability`='99' THEN `availability`+'1' 
    ELSE `availability`+'2'
  END
WHERE `company` IN ('241','94','208') AND `availability`<'100'

如果trt_employees表没有针对(company, availability)的联合索引，InnoDB只能通过扫描主键索引（聚集索引）来筛选符合条件的行，加锁顺序完全按照主键从小到大的顺序逐行加锁，且会持有大量行锁（从死锁日志可以看到事务1已经持有12835个行锁），极大提升锁冲突概率。

2. 循环等待形成
   从死锁日志可以明确看到两个事务的锁依赖关系：

• 事务1已经拿到了小主键行（主键值为0x8004561e，对应十进制id=283166）的排他锁，正在请求更大主键行（主键值为0x80045e10，对应十进制id=284190，就是事务2要更新的行）的排他锁，处于等待状态。
• 事务2已经拿到了大主键行id=284190的排他锁，正在请求小主键行id=283166的排他锁，处于等待状态。
  两者互相等待对方持有的锁，符合死锁的四个必要条件，因此被InnoDB死锁检测机制捕获，最终回滚了事务2。

注：你可能疑惑事务2只是单条按id更新的语句，为什么需要锁小主键行？因为死锁日志只会打印事务当前正在执行的语句，实际上事务2所在的业务事务在执行这条UPDATE之前已经执行过其他写操作，日志中的undo log entries 2也可以佐证：这个事务已经产生了2条修改记录，需要申请第二行锁的时候触发了死锁。

• 给trt_employees表添加联合索引idx_company_availability(company, availability)，让批量更新可以直接通过二级索引定位符合条件的行，避免全表扫描，大幅缩小加锁范围和持锁行数。
• 如果批量更新涉及的行数超过1000行，建议拆分成多个小事务分批更新，每次只更新少量数据，缩短锁持有时间。
• 检查事务2所在的业务逻辑，确保同一事务内的多个写操作按照主键从小到大的顺序执行，避免和批量更新的加锁顺序相反。

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