Suppress "Unable to rollback against JDBC Connection" in case of timeout (connection closed)

[phildeg31]

Hi,
I use Spring Boot 3.4.4 with an Oracle database (JpaTransactionManager, JpaRepository and Hikari connection are used).

I set a timeout on @transactional to stop the treatment of the service if it is too long (due to long database queries according to parameters) like this:
@Transactional(readOnly = true, timeout = 40)

If the timeout is reached for the transaction, then I expect to have an exception related to this reason thrown by JPA transaction manager (ex: JPATimeoutException) but I have the following exception with no link to the timeout:

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)

If I check logs, I see that a cancel of the query has been asked (because of the timeout if I understand):
ORA-01013: user requested cancel of current operation
Then, I see a log indicating that the transaction is converted into a rollback exception:
ERROR o.s.t.i.TransactionInterceptor - Application exception overridden by rollback exception
Also, it seems that the connection is closed after the cancel.

The rollback exception provokes a rollback in HikariProxyConnection that cannot be done because the connection has been closed.

If I catch the exception thrown by the service (on which I have @transactional with timeout), I have the JpaSystemException with a root cause indicating that that the connection is closed (see the stacktrace above). The information about the timeout reached is lost and I am not able to identify that I had a timeout (as a "Caused by" at least).

Is it possible to keep at least the root cause (=the timeout was reached) because here it is strange to have nothing related to it?

[jhoeller]

What was the application exception that got overridden, actually, in that error log entry? Did that one indicate a timeout? I suppose the Oracle driver automatically cancels the current statement in case of a timeout, but I'm not sure why it automatically closes the connection then.

In terms of exception content, we are ultimately exposing what the JDBC driver and the connection pool give us, to some degree through Hibernate which actually handles the transaction. If there is no timeout indicated there, it won't be obvious from the exception content.

[phildeg31]

In logs I see multiple errors and stacktraces but the first one is this one:

2025-04-03 12:19:12 [http-nio-8080-exec-5] WARN  c.zaxxer.hikari.pool.ProxyConnection - HikariPool-1 - Connection oracle.jdbc.driver.T4CConnection@4ff3602d marked as broken because of SQLSTATE(72000), ErrorCode(1013)
java.sql.SQLTimeoutException: ORA-01013: user requested cancel of current operation

https://docs.oracle.com/error-help/db/ora-01013/
	at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:709)
	at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:609)
	at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1290)
	at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:1100)
	at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:408)
	at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:483)
	at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:261)
	at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:1121)
	at oracle.jdbc.driver.OracleStatement.prepareDefineBufferAndExecute(OracleStatement.java:1299)
	at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1224)
	at oracle.jdbc.driver.OracleStatement.executeSQLSelect(OracleStatement.java:1592)
	at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1442)
	at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3932)
	at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:4036)
	at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1099)
	at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52)
	at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeQuery(HikariProxyPreparedStatement.java)
	at org.hibernate.sql.results.jdbc.internal.DeferredResultSetAccess.executeQuery(DeferredResultSetAccess.java:250)
	at org.hibernate.sql.results.jdbc.internal.DeferredResultSetAccess.getResultSet(DeferredResultSetAccess.java:171)
	at org.hibernate.sql.results.jdbc.internal.JdbcValuesResultSetImpl.<init>(JdbcValuesResultSetImpl.java:74)
	at org.hibernate.sql.exec.internal.JdbcSelectExecutorStandardImpl.resolveJdbcValuesSource(JdbcSelectExecutorStandardImpl.java:355)
	at org.hibernate.sql.exec.internal.JdbcSelectExecutorStandardImpl.doExecuteQuery(JdbcSelectExecutorStandardImpl.java:137)
	at org.hibernate.sql.exec.internal.JdbcSelectExecutorStandardImpl.executeQuery(JdbcSelectExecutorStandardImpl.java:102)
	at org.hibernate.sql.exec.spi.JdbcSelectExecutor.executeQuery(JdbcSelectExecutor.java:91)
	at org.hibernate.sql.exec.spi.JdbcSelectExecutor.list(JdbcSelectExecutor.java:165)
	at org.hibernate.query.sqm.internal.ConcreteSqmSelectQueryPlan.lambda$new$1(ConcreteSqmSelectQueryPlan.java:152)
	at org.hibernate.query.sqm.internal.ConcreteSqmSelectQueryPlan.withCacheableSqmInterpretation(ConcreteSqmSelectQueryPlan.java:442)
	at org.hibernate.query.sqm.internal.ConcreteSqmSelectQueryPlan.performList(ConcreteSqmSelectQueryPlan.java:362)
	at org.hibernate.query.sqm.internal.QuerySqmImpl.doList(QuerySqmImpl.java:380)
	at org.hibernate.query.spi.AbstractSelectionQuery.list(AbstractSelectionQuery.java:143)
	at org.hibernate.query.Query.getResultList(Query.java:120)
	at org.springframework.data.jpa.repository.support.SimpleJpaRepository.executeCountQuery(SimpleJpaRepository.java:941)
	at org.springframework.data.jpa.repository.support.SimpleJpaRepository.lambda$7(SimpleJpaRepository.java:716)
	at org.springframework.data.support.PageableExecutionUtils.getPage(PageableExecutionUtils.java:71)
	at org.springframework.data.jpa.repository.support.SimpleJpaRepository.readPage(SimpleJpaRepository.java:715)
	at org.springframework.data.jpa.repository.support.SimpleJpaRepository.findAll(SimpleJpaRepository.java:456)
        at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77)
	at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.base/java.lang.reflect.Method.invoke(Method.java:568)
	at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:359)
	at org.springframework.data.repository.core.support.RepositoryMethodInvoker$RepositoryFragmentMethodInvoker.lambda$new$0(RepositoryMethodInvoker.java:277)
	at org.springframework.data.repository.core.support.RepositoryMethodInvoker.doInvoke(RepositoryMethodInvoker.java:170)
	at org.springframework.data.repository.core.support.RepositoryMethodInvoker.invoke(RepositoryMethodInvoker.java:158)
	at org.springframework.data.repository.core.support.RepositoryComposition$RepositoryFragments.invoke(RepositoryComposition.java:515)
	at org.springframework.data.repository.core.support.RepositoryComposition.invoke(RepositoryComposition.java:284)
	at org.springframework.data.repository.core.support.RepositoryFactorySupport$ImplementationMethodExecutionInterceptor.invoke(RepositoryFactorySupport.java:731)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)
	at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.doInvoke(QueryExecutorMethodInterceptor.java:174)
	at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.invoke(QueryExecutorMethodInterceptor.java:149)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)
	at org.springframework.data.projection.DefaultMethodInvokingMethodInterceptor.invoke(DefaultMethodInvokingMethodInterceptor.java:69)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)
	at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:380)
	at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:119)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)
	at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:138)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)
	at org.springframework.data.jpa.repository.support.CrudMethodMetadataPostProcessor$CrudMethodMetadataPopulatingMethodInterceptor.invoke(CrudMethodMetadataPostProcessor.java:165)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)
	at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:223)
	at jdk.proxy4/jdk.proxy4.$Proxy205.findAll(Unknown Source)
        ...
  Caused by: oracle.jdbc.OracleDatabaseException: ORA-01013: user requested cancel of current operation
        at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:717)
	... 223 common frames omitted
2025-04-03 12:19:12 [http-nio-8080-exec-5] WARN  o.h.e.jdbc.spi.SqlExceptionHelper - SQL Error: 1013, SQLState: 72000
2025-04-03 12:19:12 [http-nio-8080-exec-5] ERROR o.h.e.jdbc.spi.SqlExceptionHelper - ORA-01013: user requested cancel of current operation

Then I see this error:

https://docs.oracle.com/error-help/db/ora-01013/
2025-04-03 12:19:12 [http-nio-8080-exec-5] ERROR o.s.t.i.TransactionInterceptor - Application exception overridden by rollback exception
org.springframework.dao.QueryTimeoutException: JDBC exception executing SQL [query] [ORA-01013: user requested cancel of current operation
https://docs.oracle.com/error-help/db/ora-01013/] [n/a]; SQL [n/a]
	at org.springframework.orm.jpa.vendor.HibernateJpaDialect.convertHibernateAccessException(HibernateJpaDialect.java:280)
	at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:244)
	at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.translateExceptionIfPossible(AbstractEntityManagerFactoryBean.java:560)
	at org.springframework.dao.support.ChainedPersistenceExceptionTranslator.translateExceptionIfPossible(ChainedPersistenceExceptionTranslator.java:61)
	at org.springframework.dao.support.DataAccessUtils.translateIfNecessary(DataAccessUtils.java:343)
	at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:160)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)
	at org.springframework.data.jpa.repository.support.CrudMethodMetadataPostProcessor$CrudMethodMetadataPopulatingMethodInterceptor.invoke(CrudMethodMetadataPostProcessor.java:165)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)
	at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:223)
	at jdk.proxy4/jdk.proxy4.$Proxy205.findAll(Unknown Source)
        at com.testapp.services.TestService.findByCriteria(TestService.java:95)
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77)
	at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.base/java.lang.reflect.Method.invoke(Method.java:568)
	at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:359)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:196)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
	at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:380)
	at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:119)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)
	at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:727)
	at com.testapp.services.TestService$$SpringCGLIB$$0.findByCriteria(<generated>)
	at com.testapp.api.controllers.TestController.findByCriteria(TestController.java:206)

And then I have this error:

https://docs.oracle.com/error-help/db/ora-01013/] [n/a]
	at org.hibernate.dialect.OracleDialect.lambda$buildSQLExceptionConversionDelegate$1(OracleDialect.java:1161)
	at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:58)
	at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:108)
	at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:94)
	at org.hibernate.sql.results.jdbc.internal.DeferredResultSetAccess.executeQuery(DeferredResultSetAccess.java:268)
	at org.hibernate.sql.results.jdbc.internal.DeferredResultSetAccess.getResultSet(DeferredResultSetAccess.java:171)
	at org.hibernate.sql.results.jdbc.internal.JdbcValuesResultSetImpl.<init>(JdbcValuesResultSetImpl.java:74)
	at org.hibernate.sql.exec.internal.JdbcSelectExecutorStandardImpl.resolveJdbcValuesSource(JdbcSelectExecutorStandardImpl.java:355)
	at org.hibernate.sql.exec.internal.JdbcSelectExecutorStandardImpl.doExecuteQuery(JdbcSelectExecutorStandardImpl.java:137)
	at org.hibernate.sql.exec.internal.JdbcSelectExecutorStandardImpl.executeQuery(JdbcSelectExecutorStandardImpl.java:102)
	at org.hibernate.sql.exec.spi.JdbcSelectExecutor.executeQuery(JdbcSelectExecutor.java:91)
	at org.hibernate.sql.exec.spi.JdbcSelectExecutor.list(JdbcSelectExecutor.java:165)
	at org.hibernate.query.sqm.internal.ConcreteSqmSelectQueryPlan.lambda$new$1(ConcreteSqmSelectQueryPlan.java:152)
	at org.hibernate.query.sqm.internal.ConcreteSqmSelectQueryPlan.withCacheableSqmInterpretation(ConcreteSqmSelectQueryPlan.java:442)
	at org.hibernate.query.sqm.internal.ConcreteSqmSelectQueryPlan.performList(ConcreteSqmSelectQueryPlan.java:362)
	at org.hibernate.query.sqm.internal.QuerySqmImpl.doList(QuerySqmImpl.java:380)
	at org.hibernate.query.spi.AbstractSelectionQuery.list(AbstractSelectionQuery.java:143)
	at org.hibernate.query.Query.getResultList(Query.java:120)
	at org.springframework.data.jpa.repository.support.SimpleJpaRepository.executeCountQuery(SimpleJpaRepository.java:941)
	at org.springframework.data.jpa.repository.support.SimpleJpaRepository.lambda$7(SimpleJpaRepository.java:716)
	at org.springframework.data.support.PageableExecutionUtils.getPage(PageableExecutionUtils.java:71)
	at org.springframework.data.jpa.repository.support.SimpleJpaRepository.readPage(SimpleJpaRepository.java:715)
	at org.springframework.data.jpa.repository.support.SimpleJpaRepository.findAll(SimpleJpaRepository.java:456)
        ...
        at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:380)
	at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:119)
        ...
Caused by: java.sql.SQLTimeoutException: ORA-01013: user requested cancel of current operation

So there is a SQLTimeoutException and then a QueryTimeoutException but this is not what I receive at the end when I catch Exception.

[jhoeller]

So we correctly identify it as a QueryTimeoutException and log it at error level, that's great for a start. I was afraid we would not even be able to identify it as such.

In terms of the actual exception thrown indicating the rollback failure, that's rather unfortunate but part of the transaction logic there: Generally speaking, rollback failures are a harder problem than a statement-level exception that happened before, and the harder problem overrides. Now that is a purely technical judgement - for a query timeout with auto-closing of the connection, we could try to provide a better user experience indeed. It's not entirely clear how we can technically reason about that scenaro in the transaction interceptor, though.

It's still not clear why the connection gets automatically closed before we have a chance to formally trigger a rollback. Since you are using a rather long 40-second timeout at the transaction level, you could double-check whether Hikari happens to interfere with a timeout of its own there. As far as I can see, Hikari uses a connection wait timeout of 30 seconds - although that does not seem to immediately apply here, you could try to lower your transaction-level timeout to 25 seconds, for example (below Hikari's 30 seconds), and see whether the rollback works then.

[phildeg31]

In terms of the actual exception thrown indicating the rollback failure, that's rather unfortunate but part of the transaction logic there: Generally speaking, rollback failures are a harder problem than a statement-level exception that happened before, and the harder problem overrides. Now that is a purely technical judgement - for a query timeout with auto-closing of the connection, we could try to provide a better user experience indeed. It's not entirely clear how we can technically reason about that scenaro in the transaction interceptor, though.

At least if we can keep the root cause and have a "Caused by" with the original exception (QueryTimeoutException for example) or a new specific exception (ex: 'TimeoutNoRollbackException') to know that we had an issue with the rollback after a timeout. I think that it can be good because in my case the information is lost (erased by the exception due to the failure of the rollback due to closed connection) and I am not able to know for sure that it is due to a timeout when I try to catch it.
By the way, can you explain me why do we have a rollback here because my transaction is read only? I thought that it would permit to not have a commit or a rollback (optimization) because I only do a SELECT query in my service.

It's still not clear why the connection gets automatically closed before we have a chance to formally trigger a rollback. Since you are using a rather long 40-second timeout at the transaction level, you could double-check whether Hikari happens to interfere with a timeout of its own there. As far as I can see, Hikari uses a connection wait timeout of 30 seconds - although that does not seem to immediately apply here, you could try to lower your transaction-level timeout to 25 seconds, for example (below Hikari's 30 seconds), and see whether the rollback works then.

I will try it on Monday at work and I will keep you informed.

Thank you.

[jhoeller]

Admittedly, that rollback failure is a misleading exception, even more so since this is about a read-only transaction. The rollback follows from the technical demarcation that implies a transaction-scoped Connection underneath, where the transaction scope formally needs to end on the Connection before returning it to the pool (even if no actual transactional operations happened in this particular scope).

Such a transaction demarcation is generally recommended for read operations as well. For JPA, it forces the EntityManager to use the same Connection for all operations (whereas it could otherwise obtain a fresh Connection per operation), and you could possibly enforce reading with a consistent isolation level across multiple entities and queries. Last but not least, it allows the connection pool to be declared with an autoCommit=false setting.

Alternatively, you could declare @Transactional(propagation=SUPPORTS, readOnly=true) (rather than the default propagation=REQUIRED) to avoid such explicit demarcation at the resource level. Some resource reuse happens at Spring level then but no actual transaction will be active in JPA/JDBC terms. This also means that there won't be a rollback attempt, so no rollback exception either, which could be a way to address your immediate problem.

That said, we should try to sort out the behavior for your original scenario as well. A rollback failure just due to the Connection having been prematurely closed is arguably pointless, given that the transaction manager intends to close the Connection right after the rollback anyway. What complicates this is that it is actually Hibernate performing the rollback, so we can't easily check the Connection status ourselves.

[jhoeller]

Assuming that your scenario lets the underlying connection expire and we then fail in the transaction cleanup step where the connection is expected to be active still: There is not much we can do about a closed connection with a Hibernate-managed rollback. We cannot intercept the rollback for a Connection check, and the rollback exception does not cleanly indicate that the connection was closed since the cause is a plain SQLException (just with a message that says connection closed).

That said, we are able to cover this with Spring's LazyConnectionDataSourceProxy: a generally recommendable DataSource decorator that you can wrap around your HikariDataSource for the purpose of minimizing pool contention, not borrowing a physical connection at all when no actual statement is being sent (for example in case of a Hibernate operation that can be entirely satisfied from the cache). In that decorator, we could leniently suppress a rollback() attempt on a closed connection.

For reference: https://vladmihalcea.com/lazyconnectiondatasourceproxy-spring-data-jpa/ - setting that up right now is beneficial in other ways but won't address your immediate scenario with a late transaction timeout yet. I've added lenient rollback handling there for 6.2.6 now. Please give such a LazyConnectionDataSourceProxy setup an early try against the latest 6.2.6 snapshot!