高并发环境下MariaDB插入/删除操作死锁问题及原因问询

一、直接删除方案导致死锁的底层原因 #

• InnoDB默认采用REPEATABLE READ隔离级别，针对delete where last_changed < T1这类范围条件的写操作，会触发Next-Key Lock机制：既锁定符合条件的行（加排他锁X锁），也锁定行之间的索引间隙（加间隙锁），目的是防止幻读，阻止新的符合条件的记录插入到这些间隙中。
• 插入新记录时，InnoDB会在目标索引位置申请插入意向锁——这是一种特殊的间隙锁，允许多个事务同时插入不冲突的记录，但会和覆盖该间隙的Next-Key Lock互斥。
• 并发场景下的死锁触发逻辑：
  1. 删除事务A执行范围删除，先锁定索引中某一段的行和间隙，正在向右扫描加锁。
  2. 插入事务B插入一条处于事务A未锁定的间隙、且符合last_changed < T1的记录，成功获取该行的X锁。
  3. 事务A继续扫描到事务B插入的记录，尝试给它加X锁以执行删除，被事务B持有的X锁阻塞，进入等待。
  4. 同时，另一个删除事务C开始执行范围删除，先扫描到事务B的记录，尝试加X锁时被事务B阻塞；或另一个插入事务D尝试插入到事务A已锁定的间隙，被事务A的Next-Key Lock阻塞。
  5. 当出现循环等待的锁依赖（比如事务A等事务B，事务B等事务C，事务C等事务A），InnoDB的死锁检测机制会判定死锁，终止其中一个事务。
• 加last_changed索引无法解决的原因：索引仅优化了删除操作的行定位效率，但范围查询的Next-Key Lock机制依然生效，插入意向锁和Next-Key Lock的互斥关系未改变，因此死锁仍可能触发。

二、简单表结构复现死锁是否正常 #

完全正常。死锁的核心触发条件是并发事务的锁请求顺序形成循环等待，和表结构的复杂度无关。只要涉及InnoDB的范围锁（Next-Key Lock）与插入操作的并发交互，即使是仅含两列的简单表，也会因锁机制的设计逻辑触发死锁，这是InnoDB保证事务隔离级别和数据一致性的必然结果。

补充：先查ID再删除方案的解决原理 #

当先查询出要删除的主键ID，再执行delete from test_entity where id in (...)时，删除操作是基于主键的精准匹配：

• InnoDB仅对匹配的主键行加X锁，不会使用Next-Key Lock（精准匹配唯一索引时，REPEATABLE READ级别下不会加间隙锁）。
• 插入操作的插入意向锁不会与任何间隙锁冲突，不会形成循环等待的锁依赖，从而避免死锁。但该方案会多一次查询操作，且若查询与删除之间有其他事务修改数据，可能需要额外处理，因此性能有所下降。

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