多实例应用下MySQL行锁失效求助：SELECT ... FOR UPDATE未生效

问题背景 #

业务需求是基于表opr_seq（结构：id | name | last_seq）生成不重复的6位traceNo：

• 第一个应用实例查询后，将last_seq更新为100
• 第二个实例查询后，last_seq更新为200，以此类推
• 每生成100个traceNo，就从数据库同步一次last_seq值

实际用JMeter压测时，即便尝试用SELECT ... FOR UPDATE做行锁，仍出现多个实例获取相同last_seq并重复更新的情况，导致生成重复的traceNo。

现有代码 #

public synchronized String constructTraceNo(){
    if(counter % 100 == 0){
        OprSeq oprSeq= oprSeqRepo.findByNm("opr_sequence");
        //update to repository
        counter = oprSeq.getLastSequence();
        oprSeq.setLastSequence(counter+100);
        oprSeqRepo.save(oprSeq);
    }else if(counter == 999999){
        OprSeq oprSeq= oprSeqRepo.findByNm("opr_sequence");
        counter = 0;
        oprSeq.setLastSequence(counter+100);
        oprSeqRepo.save(oprSeq);
    }
    counter+=1;
    return padding(String.valueOf(counter),6,"0",0);
}

问题原因分析 #

1. synchronized跨实例无效：该关键字仅在单个JVM进程内生效，多应用实例（多JVM）部署时，完全无法限制跨进程的并发访问。
2. 行锁未真正生效：默认的Spring Data JPA查询findByNm不会自动添加FOR UPDATE锁，导致并发场景下多个实例能同时读取到旧的last_seq值。
3. 查询与更新不在同一事务：如果查询和更新操作分属不同事务，行锁会在查询结束后立即释放，后续更新时已经没有锁保护，出现竞态条件。

解决方案 #

1. 给查询方法添加悲观写锁 #

修改JPA Repository接口，通过注解强制查询时带上SELECT ... FOR UPDATE，确保行锁生效：

public interface OprSeqRepo extends JpaRepository<OprSeq, Long> {
    @Lock(LockModeType.PESSIMISTIC_WRITE)
    OprSeq findByNm(String nm);
}

或者使用原生SQL实现：

@Query(value = "SELECT * FROM opr_seq WHERE nm = ?1 FOR UPDATE", nativeQuery = true)
OprSeq findByNmForUpdate(String nm);

2. 确保查询+更新在同一事务内 #

在constructTraceNo方法上添加@Transactional注解，保证整个业务逻辑在同一个事务中执行，行锁会持有到事务结束：

@Transactional
public String constructTraceNo(){ // 多实例下synchronized可移除，行锁已保证跨进程安全
    if(counter % 100 == 0){
        OprSeq oprSeq= oprSeqRepo.findByNm("opr_sequence");
        counter = oprSeq.getLastSequence();
        oprSeq.setLastSequence(counter+100);
        oprSeqRepo.save(oprSeq);
    }else if(counter == 999999){
        OprSeq oprSeq= oprSeqRepo.findByNm("opr_sequence");
        counter = 0;
        oprSeq.setLastSequence(counter+100);
        oprSeqRepo.save(oprSeq);
    }
    counter+=1;
    return padding(String.valueOf(counter),6,"0",0);
}

3. 优化为原子更新（推荐） #

避免先读后写的竞态，直接用MySQL原子更新语句完成操作，彻底消除并发问题：

@Transactional
public String constructTraceNo(){
    if(counter % 100 == 0){
        // 原子更新并返回新值
        int updatedRows = oprSeqRepo.updateLastSeqByNm("opr_sequence", 100);
        if(updatedRows > 0){
            OprSeq oprSeq = oprSeqRepo.findByNm("opr_sequence");
            counter = oprSeq.getLastSequence();
        }
    }else if(counter == 999999){
        int updatedRows = oprSeqRepo.resetLastSeqByNm("opr_sequence");
        if(updatedRows > 0){
            counter = 0;
        }
    }
    counter+=1;
    return padding(String.valueOf(counter),6,"0",0);
}

对应的Repository方法：

@Modifying
@Query(value = "UPDATE opr_seq SET last_seq = last_seq + ?2 WHERE nm = ?1", nativeQuery = true)
int updateLastSeqByNm(String nm, int increment);

@Modifying
@Query(value = "UPDATE opr_seq SET last_seq = 100 WHERE nm = ?1", nativeQuery = true)
int resetLastSeqByNm(String nm);

4. 确认数据库事务隔离级别 #

确保MySQL使用默认的REPEATABLE READ隔离级别，避免因隔离级别过低导致的并发问题。

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