基于排序列表的锁定更新场景是否会产生死锁及预防方案

表结构 #

假设存在表tableX，结构及索引如下：

id,colA,colB,colC,colD
复合索引 -> colA,colB,colC

数据列表 #

存在一个包含ColB和ColC字段的对象列表，已按ColB优先、ColC次之排序：

list = list.sortBasedOnColBThenColC();

查询语句生成逻辑 #

基于上述排序列表生成如下查询语句（伪代码）：

String query = "select * from tableX where colA=request.getColA()";
for (int i = 0; i < list.size(); i++) {
    query.append("(colB=list[i].getColB()");
    query.append("and")
    query.append("(colC=list[i].getColC())");
    query.append("or")
}
query += "for update"; // 排他锁读

事务执行流程 #

执行以下事务逻辑（伪代码），该事务是某API的一部分，会被并行执行：

start transaction

execute(query)

for i in list
   int rowsUpdated = update tableX set colD+=1 where colA=request.getColA() and colB=list[i].getColB() and colC=list[i].getColC();
   if(rowsUpdated < 1){
      insert into tableX (colA,colB,colC) values (request.getColA(), list[i].getColB(), list[i].getColC())
   }

commit

核心问题 #

由于列表已按ColB+ColC排序，请问此场景下是否可能出现死锁？若可能，该如何预防？

是否可能出现死锁？ #

是，仍然可能出现死锁，核心原因如下：

1. 虽然查询列表按ColB+ColC排序，但事务中存在插入操作的竞态：当两个并行事务都要插入同一组(colA,colB,colC)的新行时，select ... for update不会对不存在的行加锁，导致两个事务都能进入插入步骤。其中一个事务插入成功并持有该行排他锁，另一个事务会阻塞等待该锁；若此时第一个事务又需要等待第二个事务持有的其他行锁（比如第二个事务已插入列表中后续的行），就会形成循环等待，触发死锁。
2. 现有select ... for update的拼接逻辑有语法错误——最后会多一个多余的or，导致SQL无法正确执行，锁的获取逻辑完全失控，进一步提升死锁概率。

死锁预防方案 #

• 修复SQL语法错误，规范锁的获取：
  把拼接的or逻辑改成in子句的形式，同时用参数化查询避免SQL注入，保证锁按索引顺序获取：

  String query = "select * from tableX where colA=? and (colB, colC) in (";
  for (int i = 0; i < list.size(); i++) {
      if (i > 0) query.append(", ");
      query.append("(?, ?)");
  }
  query.append(") for update");
  

• 用原子操作替代查询+更新/插入的两步逻辑：
  把colA,colB,colC设为唯一索引，使用insert ... on duplicate key update语句，原子性完成更新或插入，避免中间的锁竞争：

  insert into tableX (colA, colB, colC, colD) 
  values (?, ?, ?, 1) 
  on duplicate key update colD = colD + 1;
  

  这一步直接把原本的查询、更新、插入合并为一个原子操作，从根源减少锁的持有和竞争。

• 缩短事务时长：
  把事务中不涉及数据修改的逻辑（如日志记录、参数校验）移出事务范围，减少锁的持有时间，降低死锁发生的概率。

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