MariaDB并发操作OrderGroups表如何避免死锁并保证单开放组策略

你对死锁原因的判断完全准确：MariaDB/InnoDB 默认REPEATABLE READ隔离级别下，FOR UPDATE查询不存在的行时，只会在对应索引范围加间隙锁（Gap Lock），而间隙锁之间是兼容的，不会互斥阻塞。因此两个并发事务可以同时拿到同一个clientId对应范围的间隙锁，后续双方执行INSERT时都需要申请插入意向锁，插入意向锁和已存在的间隙锁互斥，最终形成循环等待触发死锁。

以下方案按可靠性、性能优先级排序，都能严格保证「同一客户端仅存在一个未关闭订单组」的规则： #

方案1：条件唯一索引+冲突重试（最优推荐） #

从数据库层强制业务规则，从根源堵死重复数据，同时避免不必要的锁开销：

1. 给表添加条件唯一索引，仅对未关闭的分组生效：

   ALTER TABLE OrderGroups ADD UNIQUE KEY uk_client_active_group (clientId, IsClosed) WHERE IsClosed = 0;
   

   该索引会直接禁止同一个clientId存在两条IsClosed=0的记录，完全符合业务规则约束。
2. 调整事务逻辑：

   BEGIN TRANSACTION;
   SELECT * FROM OrderGroups WHERE clientId = {id} AND IsClosed = 0 FOR UPDATE;
   -- 查到分组直接关联使用
   -- 未查到则执行插入
   INSERT INTO OrderGroups(clientId, IsClosed) VALUES({id}, 0);
   COMMIT;
   

3. 新增异常捕获逻辑：如果INSERT抛出1062唯一键冲突错误，说明并发事务已经提前创建了该client的未关闭分组，直接回滚当前事务，重试整个查询逻辑即可拿到已存在的分组。

该方案没有额外的全局锁开销，唯一约束由数据库原生保证不会出现脏数据，重试逻辑仅在极低概率的并发冲突时触发，整体性能最优。 #

方案2：锚点行锁串行化（无重试逻辑） #

如果不想在业务层加重试逻辑，可以通过提前锁定确定存在的行，把同一个client的并发请求串行化：

1. 新建客户端锚点表，以clientId为主键，提前为所有存在的客户端初始化对应记录：

   CREATE TABLE ClientLock(
       clientId INT PRIMARY KEY
   );
   

2. 调整事务逻辑，操作OrderGroups前先锁定对应client的锚点行：

   BEGIN TRANSACTION;
   -- clientId是主键，行锁互斥，同client请求会在这里阻塞排队
   SELECT 1 FROM ClientLock WHERE clientId = {id} FOR UPDATE;
   SELECT * FROM OrderGroups WHERE clientId = {id} AND IsClosed = 0 FOR UPDATE;
   -- 后续查询、插入逻辑和原来一致，不会出现并发拿间隙锁的情况
   COMMIT;
   

该方案逻辑简单不需要重试，但需要额外维护ClientLock表，新客户端首次接入时需要先在ClientLock插入对应记录。 #

方案3：调整隔离级别关闭间隙锁 #

将当前事务的隔离级别临时调整为READ COMMITTED，该级别下MariaDB/InnoDB会关闭普通查询的间隙锁（仅外键、唯一键检查场景保留），FOR UPDATE查询不存在的行时不会加兼容的间隙锁，后续插入时的行锁会自然阻塞并发请求：

BEGIN TRANSACTION;
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
-- 后续查询、插入逻辑和原来一致
COMMIT;
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

注意该方案要求数据库binlog格式必须为ROW，否则会出现主从不一致问题，不建议在原有隔离级别为REPEATABLE READ的生产环境直接使用。

不要通过全表加锁、扩大锁范围的方式规避死锁，这类方案会阻塞所有客户端的请求，高并发场景下会直接拖垮数据库性能。

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