同一事务中@Transactional引发JPA与JDBC锁冲突致表重命名失败

问题场景 #

在带有@Transactional注解的updateFromLocalFiles方法中，执行以下操作时出现阻塞：

1. 通过JPA的saveAllAndFlush方法向临时表adw_material_temp写入数据；
2. 通过JDBCTemplate执行ALTER TABLE语句重命名该临时表。

数据库现象 #

• 进程列表显示重命名语句处于Waiting for table metadata lock状态；
• 查询performance_schema.metadata_locks可见：临时表已被授予SHARED_WRITE锁，重命名请求的EXCLUSIVE排他锁处于PENDING等待状态。

原因分析 #

@Transactional注解会让整个方法处于同一个数据库事务中：

• JPA的saveAllAndFlush执行时，会对目标临时表加SHARED_WRITE锁，且该锁在事务未提交前会持续持有；
• ALTER TABLE重命名操作需要获取表的EXCLUSIVE排他锁，排他锁与已持有的共享写锁互斥，导致重命名请求一直等待锁释放，最终阻塞。

解决方法 #

移除updateFromLocalFiles方法上的@Transactional注解，让JPA写入和表重命名操作不在同一个事务中执行。JPA操作完成后会立即提交并释放锁，后续的重命名操作就能顺利获取排他锁完成执行。

相关代码 #

原问题代码 #

@HandlingTime
@Transactional
public void updateFromLocalFiles(@NotNull String materialFile, @NotNull String... statusFiles) {
    saveDataToTempTable(materialFile, statusFiles);
    checkDataIntegrity();
    backupAndDeleteOriginTable();
    replaceTemporaryToOriginTable();
    dropExpiredTable();
}

saveDataToTempTable调用的JPA实现：

protected long saveAllToTempTable(List<AdwMaterialTemp> materialList) {
    List<AdwMaterialTemp> adwMaterialTemps = adwMaterialTempRepository.saveAllAndFlush(materialList);
    return adwMaterialTemps.size();
}

replaceTemporaryToOriginTable调用的JDBC实现：

public void changeTableName(String originTableName, String targetTableName) throws DataAccessException {
    String RENAME_TABLE_NAME = "ALTER TABLE %s RENAME TO %s;";
    var sql = String.format(RENAME_TABLE_NAME, originTableName, targetTableName);
    jdbcTemplate.execute(sql);
    log.info(SQL_LOG, sql);
}

修改后的代码（移除事务注解） #

@HandlingTime
public void updateFromLocalFiles(@NotNull String materialFile, @NotNull String... statusFiles) {
    saveDataToTempTable(materialFile, statusFiles);
    checkDataIntegrity();
    backupAndDeleteOriginTable();
    replaceTemporaryToOriginTable();
    dropExpiredTable();
}

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