Spring Transactional TimeOut

Question

I am trying to use spring @Transactional annotation and timeout parameter. I basically test the code with put some Thread.sleep() codes. Then I get timeout exception as I expected. Also I want to get timeout exception when database operations take longer than my timeout period. I lock a record in a table in my database with for update select statement. I try to update that record. But program wait and do nothing. Here is my sample code.

@Transactional(rollbackFor = Exception.class, timeout=5)
public void executeService(List<sendData> list) throws Exception{
    List<sendData> newList = gDAO.updateSentList(list);
    
} 

In this case the program should throw timeout exception. How can I fix it?

Answer 1

The problem doesn't seem to be related to the @Transactional(timeout=20) or any time-related configuration. In the Spring framework, each transaction requires a database connection to start. If you set spring.datasource.hikari.maximum-pool-size to 20 (or its equivalent, spring.datasource.hikari.maximumPoolSize=20), and there are already 20 requests using connections, any new request will be stuck waiting for a connection to become available. No error will be raised until a connection is free to process the new request.

In this scenario, it's not a matter of multiple requests coming in and out quickly. The issue is that when all 20 connections are in use, any additional requests must wait until a connection is released, which can cause delays in the processing of those requests. This situation can be mitigated by either adjusting the connection pool size or optimizing the use of database connections in your application. you can use AOP to calculate exact time of hole process. and we have another config spring.datasource.hikari.connection-timeout=20000. NOTE: the time you wait to connection be released does not count in transaction timeout.

Answer 2

Finally I found the solution...

Short answer is, that the problem is in a way how you are testing the timeout - Thread.sleep() cannot be used...

Long answer:

What I had to use (because I tested with MySQL) was a real statement - select sleep(5). This call ended with:

Exception in thread "main" org.hibernate.TransactionException: transaction timeout expired
    at org.hibernate.engine.jdbc.internal.JdbcCoordinatorImpl.determineRemainingTransactionTimeOutPeriod(JdbcCoordinatorImpl.java:271)
    at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$StatementPreparationTemplate.setStatementTimeout(StatementPreparerImpl.java:208)
    at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$StatementPreparationTemplate.prepareStatement(StatementPreparerImpl.java:187)
    at org.hibernate.engine.jdbc.internal.StatementPreparerImpl.prepareQueryStatement(StatementPreparerImpl.java:160)
    at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1885)
    at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1862)
    at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1839)
    at org.hibernate.loader.Loader.doQuery(Loader.java:910)
    at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:355)
    at org.hibernate.loader.Loader.doList(Loader.java:2554)
    at org.hibernate.loader.Loader.doList(Loader.java:2540)
    at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2370)
    at org.hibernate.loader.Loader.list(Loader.java:2365)
    at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:353)
    at org.hibernate.internal.SessionImpl.listCustomQuery(SessionImpl.java:1909)
    at org.hibernate.internal.AbstractSessionImpl.list(AbstractSessionImpl.java:311)
    at org.hibernate.internal.SQLQueryImpl.list(SQLQueryImpl.java:141)
    at org.hibernate.internal.AbstractQueryImpl.uniqueResult(AbstractQueryImpl.java:966)
    at test.Dao.mysqlWait(Dao.java:41)
    at test.Dao$$FastClassBySpringCGLIB$$bb93a016.invoke(<generated>)
    at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204)
    at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:720)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157)
    at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:99)
    at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:281)
    at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
    at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:655)
    at test.Dao$$EnhancerBySpringCGLIB$$da2836b3.mysqlWait(<generated>)
    at test.Main.main(Main.java:16)

From stack this got my attention: StatementPreparerImpl$StatementPreparationTemplate.setStatementTimeout(StatementPreparerImpl.java:208)

...so I wanted to try with a different use case

@Transactional(timeout=10)
public void mysqlWait() {
    System.out.println("timeout: " + sessionFactory.getCurrentSession().getTransaction().getTimeout());

    for (int i = 0; i < 6; i++) {
        SQLQuery query = sessionFactory.getCurrentSession().createSQLQuery("select sleep(2)");
        System.out.println("executed (" + i + "): " + query.uniqueResult());
    }
}

and it resulted in:

Exception in thread "main" org.hibernate.exception.GenericJDBCException: could not extract ResultSet
    at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:54)
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:126)
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:112)
    at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:91)
    at org.hibernate.loader.Loader.getResultSet(Loader.java:2066)
    at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1863)
    at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1839)
    at org.hibernate.loader.Loader.doQuery(Loader.java:910)
    at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:355)
    at org.hibernate.loader.Loader.doList(Loader.java:2554)
    at org.hibernate.loader.Loader.doList(Loader.java:2540)
    at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2370)
    at org.hibernate.loader.Loader.list(Loader.java:2365)
    at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:353)
    at org.hibernate.internal.SessionImpl.listCustomQuery(SessionImpl.java:1909)
    at org.hibernate.internal.AbstractSessionImpl.list(AbstractSessionImpl.java:311)
    at org.hibernate.internal.SQLQueryImpl.list(SQLQueryImpl.java:141)
    at org.hibernate.internal.AbstractQueryImpl.uniqueResult(AbstractQueryImpl.java:966)
    at test.Dao.mysqlWait(Dao.java:46)
    at test.Dao$$FastClassBySpringCGLIB$$bb93a016.invoke(<generated>)
    at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204)
    at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:720)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157)
    at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:99)
    at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:281)
    at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
    at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:655)
    at test.Dao$$EnhancerBySpringCGLIB$$df6a7e0b.mysqlWait(<generated>)
    at test.Main.main(Main.java:16)
Caused by: com.mysql.jdbc.exceptions.MySQLTimeoutException: Statement cancelled due to timeout or client request
    at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1881)
    at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1962)
    at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:82)
    ... 26 more

and in log is also:

22:05:49.322 [main] DEBUG o.h.e.jdbc.spi.SqlExceptionHelper - could not extract ResultSet [n/a]
com.mysql.jdbc.exceptions.MySQLTimeoutException: Statement cancelled due to timeout or client request
    at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1881) ~[mysql-connector-java-5.1.38.jar:5.1.38]
    at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1962) ~[mysql-connector-java-5.1.38.jar:5.1.38]
    at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:82) ~[hibernate-core-4.3.11.Final.jar:4.3.11.Final]
    at org.hibernate.loader.Loader.getResultSet(Loader.java:2066) [hibernate-core-4.3.11.Final.jar:4.3.11.Final]
    at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1863) [hibernate-core-4.3.11.Final.jar:4.3.11.Final]
    at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1839) [hibernate-core-4.3.11.Final.jar:4.3.11.Final]
    at org.hibernate.loader.Loader.doQuery(Loader.java:910) [hibernate-core-4.3.11.Final.jar:4.3.11.Final]
    at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:355) [hibernate-core-4.3.11.Final.jar:4.3.11.Final]
    at org.hibernate.loader.Loader.doList(Loader.java:2554) [hibernate-core-4.3.11.Final.jar:4.3.11.Final]
    at org.hibernate.loader.Loader.doList(Loader.java:2540) [hibernate-core-4.3.11.Final.jar:4.3.11.Final]
    at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2370) [hibernate-core-4.3.11.Final.jar:4.3.11.Final]
    at org.hibernate.loader.Loader.list(Loader.java:2365) [hibernate-core-4.3.11.Final.jar:4.3.11.Final]
    at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:353) [hibernate-core-4.3.11.Final.jar:4.3.11.Final]
    at org.hibernate.internal.SessionImpl.listCustomQuery(SessionImpl.java:1909) [hibernate-core-4.3.11.Final.jar:4.3.11.Final]
    at org.hibernate.internal.AbstractSessionImpl.list(AbstractSessionImpl.java:311) [hibernate-core-4.3.11.Final.jar:4.3.11.Final]
    at org.hibernate.internal.SQLQueryImpl.list(SQLQueryImpl.java:141) [hibernate-core-4.3.11.Final.jar:4.3.11.Final]
    at org.hibernate.internal.AbstractQueryImpl.uniqueResult(AbstractQueryImpl.java:966) [hibernate-core-4.3.11.Final.jar:4.3.11.Final]
    at test.Dao.mysqlWait(Dao.java:46) [classes/:na]
    at test.Dao$$FastClassBySpringCGLIB$$bb93a016.invoke(<generated>) [classes/:na]
    at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204) [spring-core-4.2.4.RELEASE.jar:4.2.4.RELEASE]
    at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:720) [spring-aop-4.2.4.RELEASE.jar:4.2.4.RELEASE]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157) [spring-aop-4.2.4.RELEASE.jar:4.2.4.RELEASE]
    at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:99) [spring-tx-4.2.4.RELEASE.jar:4.2.4.RELEASE]
    at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:281) [spring-tx-4.2.4.RELEASE.jar:4.2.4.RELEASE]
    at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96) [spring-tx-4.2.4.RELEASE.jar:4.2.4.RELEASE]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) [spring-aop-4.2.4.RELEASE.jar:4.2.4.RELEASE]
    at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:655) [spring-aop-4.2.4.RELEASE.jar:4.2.4.RELEASE]
    at test.Dao$$EnhancerBySpringCGLIB$$df6a7e0b.mysqlWait(<generated>) [classes/:na]
    at test.Main.main(Main.java:16) [classes/:na]

important part of spring configuration is (do not expect something special):

<bean id="dataSource"
    class="org.springframework.jdbc.datasource.DriverManagerDataSource">
    <property name="driverClassName" value="com.mysql.jdbc.Driver" />
    <property name="url" value="jdbc:mysql://localhost:3306/so" />
    <property name="username" value="root" />
    <property name="password" value="" />
</bean>

<bean id="hibernateSessionFactory"
    class="org.springframework.orm.hibernate4.LocalSessionFactoryBean">
    <property name="dataSource" ref="dataSource" />
    <property name="packagesToScan" value="test" />
    <property name="hibernateProperties">
        <props>
            <prop key="hibernate.show_sql">true</prop>
        </props>
    </property>
</bean>

<tx:annotation-driven transaction-manager="txManager"/>

<bean id="txManager" class="org.springframework.orm.hibernate4.HibernateTransactionManager">
    <property name="sessionFactory" ref="hibernateSessionFactory" />
</bean>

Answer 3

I solved my problem using oracle.jdbc.ReadTimeout=60000 on application context. @Transactional annotation do not work on jdbc read timeout exception.