Spring Transactional TimeOut

java spring transactional
33,371

Solution 1

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

Solution 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>
Share:
33,371
Uğur Yeşilyurt
Author by

Uğur Yeşilyurt

Updated on July 24, 2020

Comments

  • Uğur Yeşilyurt
    Uğur Yeşilyurt almost 4 years

    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 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?

Recents

Why Is PNG file with Drop Shadow in Flutter Web App Grainy?
How to troubleshoot crashes detected by Google Play Store for Flutter app
Cupertino DateTime picker interfering with scroll behaviour
Why does awk -F work for most letters, but not for the letter "t"?
Flutter change focus color and icon color but not works
How to print and connect to printer using flutter desktop via usb?
Critical issues have been reported with the following SDK versions: com.google.android.gms:play-services-safetynet:17.0.0
Flutter Dart - get localized country name from country code
navigatorState is null when using pushNamed Navigation onGenerateRoutes of GetMaterialPage
Android Sdk manager not found- Flutter doctor error
Flutter Laravel Push Notification without using any third party like(firebase,onesignal..etc)
How to change the color of ElevatedButton when entering text in TextField

Related

Transactional saves without calling update method
Showing a Spring transaction in log
Spring - @Transactional - What happens in background?
Spring @Transactional - isolation, propagation
Declarative transactions (@Transactional) doesn't work with @Repository in Spring
Spring @Transaction not starting transactions
Spring Propagation examples in layman's terms
Should I use OAuth (or what else) for the backend of a mobile app? - There is only *one* "third-party" application in such cases
Swagger @ApiParam annotation makes parameter annotated with @PathVariable non-required in Swagger UI
How to store and read SQL queries from properties or yml file in spring boot jdbc repository?