Exception when using timeout on transaction

I use Spring Boot 3 with an Oracle database (JpaRepository and Hikari connection are used) and I have a strange behaviour when using timeout on @Transactional. What I want is to do a select but if the query is too long, then I want a timeout with an exception to catch to return a specific error to the front-end.

I use this on the service method:

@Transactional(readOnly = true, timeout = 30)

When timeout occurs, I see these errors in logs:

ORA-01013: user requested cancel of current operation <-- seems to be due to the timeout
...
ERROR o.s.t.i.TransactionInterceptor - Application exception overridden by rollback exception
...
Caused by: oracle.jdbc.OracleDatabaseException: ORA-01013

In the controller, if I catch the Exception thrown by the service, I have a JPA exception (org.springframework.orm.jpa.JpaSystemException: Unable to rollback against JDBC Connection) due to the fact that a rollback cannot be done because the connection is closed:

org.springframework.orm.jpa.JpaSystemException: Unable to rollback against JDBC Connection
at org.springframework.orm.jpa.vendor.HibernateJpaDialect.convertHibernateAccessException(HibernateJpaDialect.java:341)
at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:241)
at org.springframework.orm.jpa.JpaTransactionManager.doRollback(JpaTransactionManager.java:593)
...
Caused by: org.hibernate.TransactionException: Unable to rollback against JDBC Connection
at org.hibernate.resource.jdbc.internal.AbstractLogicalConnectionImplementor.rollback(AbstractLogicalConnectionImplementor.java:137)
at org.hibernate.resource.transaction.backend.jdbc.internal.JdbcResourceLocalTransactionCoordinatorImpl$TransactionDriverControlImpl.rollback(JdbcResourceLocalTransactionCoordinatorImpl.java:289)
at org.hibernate.engine.transaction.internal.TransactionImpl.rollback(TransactionImpl.java:142)
...
Caused by: java.sql.SQLException: Connection is closed
at com.zaxxer.hikari.pool.ProxyConnection$ClosedConnection.lambda$getClosedConnection$0(ProxyConnection.java:503)
at jdk.proxy3/jdk.proxy3.$Proxy144.rollback(Unknown Source)
at com.zaxxer.hikari.pool.ProxyConnection.rollback(ProxyConnection.java:386)
at com.zaxxer.hikari.pool.HikariProxyConnection.rollback(HikariProxyConnection.java)

I do not understand because my transaction is readOnly so I do not expect to have a commit or a rollback. Also, I do not expect to have an error due to a closed connection but just to have a TransactionTimedOutException or something like that to catch indicating that the error is due to timeout.

I would like to catch an exception in my controller linked to the timeout like this if it is possible to be able to return a specific response to the front-end that there was a timeout:

try {
  this.myService.doService();
} catch(Exception e) { // An explicit exception indication that we had a timeout (ex: TransactionTimedOutException)
  // return a specific error code to front-end
}

For the moment, I have a org.springframework.orm.jpa.JpaSystemException: Unable to rollback against JDBC Connection due to the fact that the rollback cannot be done because the connection has been closed after a SQL exception (with ORA-01013 error). I am not able to have information about the original exception that is a SQLTimeoutException.

Can you help me please?

Solution

After putting the behaviour on the Github of the Spring team, a new version 6.2.6 of spring-jdbc dependency has been published.

This is what you have to do to catch a org.springframework.dao.QueryTimeoutException using this new version:

Use LazyConnectionDataSourceProxy where the change has been done (https://github.com/spring-projects/spring-framework/commit/cc5ae239156bb9553263b3a825fb34dfeb5f5265):

@Configuration
public class DatabaseConfiguration {
    @Bean
    @ConfigurationProperties(prefix = "spring.datasource.hikari")
    public HikariDataSource hikariDataSource(DataSourceProperties properties) {
        HikariDataSource hikariDataSource = createDataSource(properties, HikariDataSource.class);
        if (StringUtils.hasText(properties.getName())) {
            hikariDataSource.setPoolName(properties.getName());
        }
        return hikariDataSource;
    }

    protected static <T> T createDataSource(DataSourceProperties properties, Class<? extends DataSource> type) {
        return (T) properties.initializeDataSourceBuilder().type(type).build();
    }

    @Primary
    @Bean
    public DataSource dataSource(HikariDataSource hikariDataSource) {
        // Wrap hikariDataSource in a LazyConnectionDataSourceProxy
        LazyConnectionDataSourceProxy lazyDataSource = new LazyConnectionDataSourceProxy();
        lazyDataSource.setTargetDataSource(hikariDataSource);
        return lazyDataSource;
    }
}

Disable DataSourceAutoConfiguration because of the previous specific configuration:

@EnableAutoConfiguration(exclude = { DataSourceAutoConfiguration.class })

catch org.springframework.dao.QueryTimeoutException in the controller because with the change this is now what we receive instead of an exception for the rollback that cannot be done because of a closed connection before.

Thank you to the Spring team to do this fast change because I am now able to catch this exception related to the timeout of the transaction to return REQUEST_TIMEOUT error to the front-end.