Transaction / Query Timeout

[MEGASILVER]

Hi guys, I would like to check on timeout with jdbcTemplate. Please advise if I am wrong.

I am trying to have a timeout for a very heavy query (select * from ....) But when I set the jdbcTemplate.setQueryTimeout, it does not have any effect.

I also tried using the transaction annotation on the method itself, but it seems that there is no effect either.

11:14:52,676 [TEST] DEBUG [saction.support.AbstractPlatformTransactionManager:371] - Creating new transaction with name [testPackage.getInfo]: 

PROPAGATION_REQUIRED,ISOLATION_DEFAULT,timeout_1
11:14:53,348 [TEST] DEBUG [ework.jdbc.datasource.DataSourceTransactionManager:202] - Acquired Connection [jdbc:oracle:thin:@localhost:1521:xe, UserName=userTest, 

Oracle JDBC driver] for JDBC transaction
11:14:53,348 [TEST] DEBUG [ework.jdbc.datasource.DataSourceTransactionManager:219] - Switching JDBC Connection [jdbc:oracle:thin:@localhost:1521:xe, 

UserName=userTest, Oracle JDBC driver] to manual commit
11:14:53,348 [TEST] DEBUG [org.springframework.jdbc.core.JdbcTemplate:436] - Executing SQL query [select * .....]

@Transactional(timeout = 1)
public void getInfo() {
 .....
}

I am expecting a timeout of 1 second to happen like the following (BUT IT DOES NOT, ONLY ON EXCEPTION)

DEBUG [ework.jdbc.datasource.DataSourceTransactionManager:273] - Rolling back JDBC transaction on Connection [jdbc:oracle:thin:@localhost:1521:xe, UserName=, Oracle JDBC driver]
DEBUG [ework.jdbc.datasource.DataSourceTransactionManager:314] - Releasing JDBC Connection [jdbc:oracle:thin:@localhost:1521:xe, UserName=, Oracle JDBC driver] after transaction
DEBUG [rg.springframework.jdbc.datasource.DataSourceUtils:312] - Returning JDBC Connection to DataSource

So basically I do not see a timeout and the method seems to run forever. Anyone can please advise?

[Grzegorz Grzybek]

Hello

For Oracle driver I've found the following configuration to work. It is Tomcat's JDBC pool configuration, but properties may be set on the native connection also:

    <Resource auth="Container" driverClassName="oracle.jdbc.OracleDriver"
      factory="org.apache.tomcat.jdbc.pool.DataSourceFactory" maxAge="600000"
      initialSize="0" jmxEnabled="true" logAbandoned="true" maxActive="15" maxIdle="15" maxWait="30000" minEvictableIdleTimeMillis="60000" minIdle="1"
      name="jdbc/ds" password="xxx" removeAbandoned="false" removeAbandonedTimeout="600" testOnBorrow="true" testOnReturn="false" testWhileIdle="false"
      timeBetweenEvictionRunsMillis="30000" type="javax.sql.DataSource" defaultAutoCommit="false"
      url="jdbc:oracle:thin:@localhost:1521:xe"
      username="xxx" validationInterval="60000" validationQuery="select 1 from dual" connectionProperties="oracle.net.CONNECT_TIMEOUT=10000;oracle.jdbc.ReadTimeout=50000"/>

The most important part is: connectionProperties="oracle.net.CONNECT_TIMEOUT=1 0000;oracle.jdbc.ReadTimeout=50000".

Se the documentation here: http://docs.oracle.com/cd/E17904_01/...oracle_rac.htm.

Also be sure, to use the latest Oracle driver from http://www.oracle.com/technetwork/da...ex-091264.html. Current version is 11.2.0.3.

regards
Grzegorz Grzybek

[MEGASILVER]

Thanks for the reply! But I am using WebSphere Server, hence the pool is controlled at its end. Guess I have to set it at the pool settings?

Hello

For Oracle driver I've found the following configuration to work. It is Tomcat's JDBC pool configuration, but properties may be set on the native connection also:

    <Resource auth="Container" driverClassName="oracle.jdbc.OracleDriver"
      factory="org.apache.tomcat.jdbc.pool.DataSourceFactory" maxAge="600000"
      initialSize="0" jmxEnabled="true" logAbandoned="true" maxActive="15" maxIdle="15" maxWait="30000" minEvictableIdleTimeMillis="60000" minIdle="1"
      name="jdbc/ds" password="xxx" removeAbandoned="false" removeAbandonedTimeout="600" testOnBorrow="true" testOnReturn="false" testWhileIdle="false"
      timeBetweenEvictionRunsMillis="30000" type="javax.sql.DataSource" defaultAutoCommit="false"
      url="jdbc:oracle:thin:@localhost:1521:xe"
      username="xxx" validationInterval="60000" validationQuery="select 1 from dual" connectionProperties="oracle.net.CONNECT_TIMEOUT=10000;oracle.jdbc.ReadTimeout=50000"/>

The most important part is: connectionProperties="oracle.net.CONNECT_TIMEOUT=1 0000;oracle.jdbc.ReadTimeout=50000".

Se the documentation here: http://docs.oracle.com/cd/E17904_01/...oracle_rac.htm.

Also be sure, to use the latest Oracle driver from http://www.oracle.com/technetwork/da...ex-091264.html. Current version is 11.2.0.3.

regards
Grzegorz Grzybek

[Grzegorz Grzybek]

Hi

Thanks for the reply! But I am using WebSphere Server, hence the pool is controlled at its end. Guess I have to set it at the pool settings?

Probably - I haven't tried it. Usually available driver properties are listed in WebSphere's console, on datasource configuration page, but I've seen only loginTimeout property...

regards
Grzegorz Grzybek