Oracle环境下HikariCP超时后事务未回滚，求回滚实现方案

数据库发生超时后，HikariCP未执行回滚，反而提交事务并异常终止。

@Transactional(rollbackFor = Exception.class)
method{
  1.delete table11
  2.delete tablel2  -> this point sqltimeout
}

执行delete table2时触发超时并抛出SQLTimeoutException，但delete table11的操作已被提交，日志输出：

org.springframework.transaction.TransactionSystemException: JDBC rollback failed.

@Around("execution(" + DAO + ")")
@Transactional(rollbackFor = Exception.class)
public Object daoLog(ProceedingJoinPoint joinpoint) throws Throwable {

    Signature signature = joinpoint.getSignature();
    
    // Execution class name 
    String className = ""; 
    // Execution method name 
    String methodName = ""; 
    // Execution function ID 
    String functionId = ""; 
    // DB action 
    String action = ""; 
    // Number of processes 
    int processCount = 0;
    
    if (signature instanceof MethodSignature) { 
        // get method name 
        methodName = signature.getName(); 
        // get class name 
        className = joinpoint.getTarget().getClass().getSimpleName();
    
        // get DB action 
        action = getDbAction(methodName); 
    }
    
    if (CompareUtil.isNotNull(loginInfoDto)) { 
        if (CompareUtil.isNotEmpty(loginInfoDto.getFunctionId())) { 
            // get function ID 
            functionId = loginInfoDto.getFunctionId(); 
        }
    }
    
    try { 
        // execute DAO method 
        Object result = joinpoint.proceed(); 
    
        // get the number of processes 
        processCount = getProcessCount(result);
    
        // output log 
        log.info(String.format(LOG_FORMAT, functionId, className, methodName, String.format(MESSAGE_FORMAT, processCount, action)));
    
        return result; 
    
    } catch (Exception ex) { 
        TransactionAspectSupport.currentTransactionStatus().setRollbackOnly(); 
        log.info(String.format(LOG_FORMAT, functionId, className, methodName, "abnormal end " + ex.getClass().getName())); 
        throw ex; 
    }

}

1. joinpoint.proceed();
2. 触发超时
3. 进入catch (Exception ex) {块

此时连接已被提交并销毁。

spring:
  datasource:
    url : jdbc:oracle:thin:@XXXX/XX
    username : XXXX
    password : XXXX
    driver-class-name : oracle.jdbc.OracleDriver
    hikari:
      connection-timeout: 15000
      maximum-pool-size: 10 
      leak-detection-threshold: 60000 
      autoCommit: false

2025/06/11 11:07:08.003 [187] [900000] WARN c.z.h.p.ProxyConnection checkException HikariPool-5 - Connection oracle.jdbc.driver.T4CConnection@71ae8c9f marked as broken because of SQLSTATE(72000), ErrorCode(1013) 
java.sql.SQLTimeoutException: ORA-01013:. Cancel of current operation requested by user


Caused by: oracle.jdbc.OracleDatabaseException:. ORA-01013: Cancel of current operation requested by user

    at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:530) ~[ojdbc10-19.20.0.0.jar:19.20.0.0.0] 
... 181 more 
2025/06/11 11:07:08.009 [187] [900000] INFO o.s.d.j.UtilLoggingJdbcLogger log [DOMA2222] THROW : CLASS=jp.co.xxxx.db.table.dao.tbikomeisaiexp.xxxxxpDaoImpl, METHOD=deleteTXxxxxxx, EXCEPTION=org.seasar.doma. SqlExecutionException 
2025/06/11 11:07:08.010 [187] [900000] INFO j.c.h.c.i.DaoLogInterceptor daoLog SHMT0320 [Target Table]:TbikoMeisaiExpDaoImpl [SQL to execute]:deleteTbikoMeisaiExp Abnormal end org. QueryTimeoutException 
2025/06/11 11:07:08.011 [187] [900000] INFO j.c.h.c.i.AppLogInterceptor controllerLog SHMT0320 900000 SHMT0320Controller regist abnormal termination org.springframework.transaction. TransactionSystemException 
2025/06/11 11:07:08.012 [187] [900000] ERROR j.c.h.c.h.ControllerExceptionHandler handleException JDBC rollback failed 
org.springframework.transaction. JDBC rollback faile



Caused by: java.sql.SQLException: Connection is closed
    at com.zaxxer.hikari.pool.ProxyConnection$ClosedConnection.lambda$getClosedConnection$0(ProxyConnection.java:502) ~[HikariCP-5.0.1.jar:?]
    at jdk.proxy4.$Proxy117.rollback(Unknown Source) ~[?:?]
    at com.zaxxer.hikari.pool.ProxyConnection.rollback(ProxyConnection.java:385) ~[HikariCP-5.0.1.jar:?]
    at com.zaxxer.hikari.pool.HikariProxyConnection.rollback(HikariProxyConnection.java) ~[HikariCP-5.0.1.jar:?]
    at org.springframework.jdbc.datasource.DataSourceTransactionManager.doRollback(DataSourceTransactionManager.java:352) ~[spring-jdbc-6.0.12.jar:6.0.12]
    ... 127 more
2025/06/11 11:07:18.383 [187] [] ERROR o.a.c.c.C.[Tomcat].[localhost].[/xxxxx].[dispatcherServlet] log Servlet.service() for servlet [dispatcherServlet] in context with path [/xxxxx] threw exception [Request processing failed: org.springframework.transaction.TransactionSystemException: JDBC rollback failed] with root cause
java.sql.SQLException: Connection is closed

1. SQL执行超时（ORA-01013）
2. HikariCP将连接标记为损坏并关闭
3. Doma与Spring捕获异常
4. Spring尝试回滚但因连接已关闭失败
5. 异常以TransactionSystemException向上传播
6. 记录到Tomcat错误日志

1. 使用@Transactional(rollbackFor = Exception.class) → 无效
2. 在try-catch中调用TransactionAspectSupport.currentTransactionStatus().setRollbackOnly(); → 无效
3. 在HikariCP配置中设置autoCommit: false → 无效

请问是否有遇到过超时后仍能实现事务回滚的场景？或者能否告知如何让HikariCP在关闭连接时执行事务回滚？我希望即使发生超时，事务也能正常回滚。

核心原因 #

Oracle抛出ORA-01013超时异常时，HikariCP会直接标记连接为损坏并关闭，此时Spring事务管理器再尝试回滚时连接已不可用，导致回滚失败。另外，DAO拦截器上的@Transactional会造成事务边界混乱，若DAO层方法本身也带事务注解，会形成嵌套事务，干扰回滚逻辑。

具体解决步骤 #

1. 调整事务边界
   移除DAO拦截器上的@Transactional注解，将事务注解放在业务层（Service）而非DAO层，让Spring清晰管理整个事务的生命周期，避免嵌套事务导致的回滚失效。

2. 配置HikariCP事务隔离级别
   在HikariCP配置中明确指定事务隔离级别，确保连接处于正确的事务上下文：

   spring:
     datasource:
       hikari:
         transactionIsolation: TRANSACTION_READ_COMMITTED
   

3. 优先手动回滚再处理连接
   在捕获SQLTimeoutException时，先尝试手动回滚事务，再交由HikariCP处理连接：

   // 需注入DataSource到拦截器
   @Autowired
   private DataSource dataSource;
   
   try {
       Object result = joinpoint.proceed();
       // ... 原有日志逻辑
       return result;
   } catch (SQLTimeoutException ex) {
       try {
           Connection conn = DataSourceUtils.getConnection(dataSource);
           if (!conn.isClosed()) {
               conn.rollback();
           }
       } catch (SQLException e) {
           log.error("手动回滚失败", e);
       } finally {
           TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
           log.info(String.format(LOG_FORMAT, functionId, className, methodName, "abnormal end " + ex.getClass().getName()));
           throw ex;
       }
   } catch (Exception ex) {
       TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
       log.info(String.format(LOG_FORMAT, functionId, className, methodName, "abnormal end " + ex.getClass().getName()));
       throw ex;
   }
   

4. 统一JDBC与HikariCP超时配置
   在JDBC URL中添加驱动级超时参数，与HikariCP配置协同：

   jdbc:oracle:thin:@XXXX/XX?oracle.net.CONNECT_TIMEOUT=15000&oracle.jdbc.ReadTimeout=30000
   

   避免两者超时逻辑不一致导致的连接状态异常。

5. 升级HikariCP版本
   当前使用的HikariCP 5.0.1存在Oracle超时场景下的连接处理问题，升级到5.0.3+的最新稳定版，官方已修复部分连接关闭时的事务处理逻辑。

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