Announcement Announcement Module
Collapse
No announcement yet.
Transaction / Query Timeout Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Transaction / Query Timeout

    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.

    Code:
    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 * .....]
    Code:
    @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)
    Code:
    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?

  • #2
    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:

    Code:
        <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

    Comment


    • #3
      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?

      Originally posted by Grzegorz Grzybek View Post
      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:

      Code:
          <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

      Comment


      • #4
        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

        Comment

        Working...
        X