SQL Server中@Transactional行锁失效，多JVM下文件生成冲突求助

问题场景 #

生成XML文件时采用命名格式：Doc_type_currentDateTimeYYMMDDHHMMSS，多线程/多JVM并发执行时，同一秒生成的文件会被后续线程覆盖。

原尝试方案：

• 方法标注@Transactional，通过开头更新数据库行获取行锁，试图阻止其他线程并行处理，但无效
• 使用synchronized块仅适用于单JVM环境，无法满足多JVM部署需求
• 无法修改文件命名规则，存在每秒仅能处理一个文件的限制

技术栈：SQL Server数据库 + Micronaut框架

当前代码实现 #

@Transactional
public String writeXMLFile(String xmlStr, String fileLocation) {
    try {
        LOGGER.info("Writing xml file for doc type: {}" , docProp.getDocumentType());
        fileRepo.updateFileLastUpdated(docProp.getDocumentType(), LocalDateTime.now());

        Date currDate = new Date();
        String currentDateTimeFileName = new SimpleDateFormat(DATE_TIME_FORMAT).format(currDate);

        String fileName = new StringBuilder(fileLocation).append("/")
                .append(docProp.getDocumentType()).append("_")
                .append(currentDateTimeFileName).toString();
        LOGGER.info("Generated file name: {}", fileName);
        String fileNameWithExtn = fileName + "." + docProp.getDocumentFormat().toLowerCase();

        Path path = Paths.get(fileNameWithExtn);
        String fileNameDateTime = currentDateTimeFileName;
        Date newDate = currDate;
        while (Files.exists(path)) {
            LOGGER.info("File is already exists  for fileName: {}", path.toString());
            newDate = DateUtils.addSeconds(newDate, 1);
            fileNameDateTime = new SimpleDateFormat(DATE_TIME_FORMAT).format(newDate);
            fileNameWithExtn = fileNameWithExtn.replace(currentDateTimeFileName, fileNameDateTime);
            path = Paths.get(fileNameWithExtn);
            currentDateTimeFileName = fileNameDateTime;
        }
        LOGGER.info("updating latest file name date time{} in database for document type {}", fileNameDateTime, docProp.getDocumentType());
        batchDAO.updateFileName(docProp.getDocumentType(), fileNameDateTime, LocalDateTime.now());

        Files.write(path, xmlStr.getBytes());
        LOGGER.info("lock released");
        return currentDateTimeFileName;
    } catch (Exception e) {
        LOGGER.error("Error in writing output file");
    }
}

问题原因分析 #

1. 行锁未正确持有：默认情况下SQL Server的UPDATE语句仅在执行瞬间加行锁，若未指定锁提示，事务期间锁可能提前释放，导致其他线程可并发修改同一行
2. 事务边界与非DB操作冲突：方法内包含文件IO操作，若事务持有时间过长可能导致锁失效，或事务配置未正确覆盖整个方法逻辑
3. 无锁保护文件名生成逻辑：文件名生成（基于当前时间）未在锁范围内，即使DB行锁生效，仍可能出现多线程生成同一时间戳的情况

解决方案 #

方案1：增强DB行锁（推荐，基于现有表） #

修改fileRepo.updateFileLastUpdated的SQL语句，添加UPDLOCK, HOLDLOCK锁提示，确保事务期间持有排他行锁：

UPDATE your_file_table WITH (UPDLOCK, HOLDLOCK) 
SET last_updated = @now 
WHERE document_type = @docType

• UPDLOCK：对目标行加更新排他锁，阻止其他线程的更新操作
• HOLDLOCK：将锁持有至事务结束，替代默认的锁释放时机
• 确保每个document_type在表中存在对应行，若不存在，使用MERGE语句原子化插入/更新：

MERGE INTO your_file_table WITH (HOLDLOCK) AS target
USING (SELECT @docType AS document_type, @now AS last_updated) AS source
ON target.document_type = source.document_type
WHEN MATCHED THEN
    UPDATE SET last_updated = source.last_updated
WHEN NOT MATCHED THEN
    INSERT (document_type, last_updated) VALUES (source.document_type, source.last_updated);

方案2：独立分布式锁表 #

创建专门的分布式锁表，实现跨JVM的锁控制：

1. 创建锁表：

CREATE TABLE distributed_lock (
    lock_key VARCHAR(100) PRIMARY KEY,
    lock_expiry DATETIME NOT NULL,
    created_at DATETIME DEFAULT GETUTCDATE()
);

2. 获取锁的原子操作：

MERGE INTO distributed_lock WITH (HOLDLOCK) AS target
USING (SELECT @lockKey AS lock_key) AS source
ON target.lock_key = source.lock_key
WHEN MATCHED THEN
    UPDATE SET lock_expiry = DATEADD(SECOND, 30, GETUTCDATE()) -- 设置30秒过期，避免死锁
WHEN NOT MATCHED THEN
    INSERT (lock_key, lock_expiry) VALUES (@lockKey, DATEADD(SECOND, 30, GETUTCDATE()));

-- 验证锁是否有效
SELECT lock_expiry FROM distributed_lock WHERE lock_key = @lockKey AND lock_expiry > GETUTCDATE();

3. 释放锁：

UPDATE distributed_lock SET lock_expiry = DATEADD(SECOND, -1, GETUTCDATE()) WHERE lock_key = @lockKey;

代码调整要点 #

• 确保@Transactional正确生效：方法需为public，避免内部调用（需通过Micronaut代理），数据源配置开启事务支持
• 优化事务范围：若文件IO耗时较长，可将锁获取+文件名生成逻辑放在事务内，提交事务后再执行文件写入，减少锁持有时间：

  @Transactional
  public String generateUniqueFileName(String docType) {
      // 执行DB锁操作
      // 生成唯一时间戳文件名
      return fileNameDateTime;
  }
  
  public String writeXMLFile(String xmlStr, String fileLocation) {
      String fileNameDateTime = generateUniqueFileName(docProp.getDocumentType());
      // 生成文件路径，写入文件
      // 后续DB更新（若不需要锁保护）
  }
  

• 避免SimpleDateFormat线程安全问题：替换为DateTimeFormatter（Java 8+），确保多线程下时间格式化正确：

  private static final DateTimeFormatter DATE_TIME_FORMATTER = DateTimeFormatter.ofPattern("yyMMddHHmmss");
  // 使用方式：
  String currentDateTimeFileName = LocalDateTime.now().format(DATE_TIME_FORMATTER);
  

常见排查点 #

• 检查DB事务是否开启：Micronaut配置中需启用数据源事务支持（micronaut.data.transaction.enabled=true）
• 验证锁语句是否命中行：若document_type对应行不存在，锁逻辑会失效，需确保行存在或用MERGE处理
• 排查隔离级别：SQL Server默认隔离级别为READ COMMITTED，无需修改，锁提示会自动提升锁级别

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