使用for update skip locked仍遇OptimisticLockException的原因排查

核心原因拆解 #

• 事务边界不匹配：如果select ... for update SKIP LOCKED和后续的update不在同一个事务中，InnoDB会在select语句执行完成后立即释放锁。这就导致其他线程可以抢占同一批数据，后续update时就会因为数据已被修改或锁定而抛出异常。必须将查询和更新逻辑放在同一个事务内，保持锁的持有直到更新完成。
• 乐观锁与悲观锁冲突：如果tb_contacts对应的实体类添加了@Version乐观锁注解，即便使用了for update SKIP LOCKED的悲观锁，Hibernate在执行update时仍会检查版本号。若其他线程已修改过数据版本，就会触发OptimisticLockException。
• SKIP LOCKED生效条件未满足：MySQL的for update SKIP LOCKED仅在以下场景生效：
  • 表引擎为InnoDB（MyISAM不支持行级锁）
  • 事务隔离级别为READ COMMITTED或REPEATABLE READ（SERIALIZABLE级别下SKIP LOCKED无效）
  • 操作处于同一个事务中
    若以上任一条件不满足，SKIP LOCKED无法跳过已锁定行，仍会出现锁竞争。
• update语句无状态过滤：如果update仅通过id in (:ids)作为条件，未限制原状态（比如status = 1），即便select时锁定了数据，在Java处理期间，其他线程可能通过其他逻辑修改了这些数据的状态，导致本线程update时触发锁冲突或数据版本异常。

对应解决建议 #

• 统一事务范围：用@Transactional注解包裹从select到update的完整流程，确保锁在整个操作周期内有效：

  @Transactional
  public void processContacts() {
      // 带状态过滤的锁查询
      Query selectQuery = entityManager.createNativeQuery(
          "select id from tb_contacts where status = 1 limit 10 for update SKIP LOCKED"
      );
      List<Long> ids = selectQuery.getResultList();
      
      // 业务处理逻辑
      handleContactData(ids);
      
      // 带原状态过滤的更新
      Query updateQuery = entityManager.createNativeQuery(
          "update tb_contacts set status = 2 where id in (:ids) and status = 1"
      );
      updateQuery.setParameter("ids", ids);
      updateQuery.executeUpdate();
  }
  

• 调整乐观锁配置：如果不需要乐观锁，直接移除实体类上的@Version注解；若必须保留，需在update语句中同步更新版本字段，例如：

  update tb_contacts set status = 2, version = version + 1 where id in (:ids) and status = 1
  

• 检查数据库配置：确认tb_contacts表的引擎为InnoDB，事务隔离级别设置为READ COMMITTED或REPEATABLE READ（可通过show variables like 'transaction_isolation';查看）。

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