Query timeouts using tomcat jdbc pool

[Vasily Kukhta]

Hello, dear tomcat users!

I am developing high-load application using tomcat jdbc connection pool and
Oracle database. It is very important to ensure my app to have very small
DB query timeouts (no longer than 3 seconds) to prevent long-running
queries or database slowness from blocking all my application. To simulate
long-running queries I have put the DB in QUIESCE state using ALTER SYSTEM
QUIESCE RESTRICTED statement.

But it looks like the timeout values have no impact - when i begin to test
my application, it hangs...

Here is my jdbc pool configuration:

    String connprops =
"oracle.net.CONNECT_TIMEOUT=3000;oracle.jdbc.ReadTimeout=3000;"
                        + "oracle.net.READ_TIMEOUT=3000";


                pp.setConnectionProperties(connprops);

                pp.setDriverClassName("oracle.jdbc.OracleDriver");

                pp.setTestOnBorrow(true);
                pp.setTestOnConnect(true);
                pp.setTestOnReturn(true);

                pp.setTestWhileIdle(true);

                pp.setMaxWait(2000);
                pp.setMinEvictableIdleTimeMillis(20000);
                pp.setTimeBetweenEvictionRunsMillis(20000);

                pp.setValidationInterval(3000);
                pp.setValidationQuery("SELECT 1 FROM DUAL");

                pp.setMaxAge(3000);
                pp.setRemoveAbandoned(true);
                pp.setRemoveAbandonedTimeout(3);


pp.setJdbcInterceptors("org.apache.tomcat.jdbc.pool.interceptor.QueryTimeoutInterceptor(queryTimeout=3)");
                dataSource = new DataSource();
                dataSource.setPoolProperties(pp);


That's how i work with connections (pretty simple):

    Connection conn = dataSource.getConnection();
            Statement stmt = null;
            ResultSet rs = null;

            try {
                stmt = conn.createStatement();

                rs = stmt.executeQuery(/*some select query*/);


                if (rs.next()) {

                    result = rs.getInt(1);

                    /*process the result*/

                }

                rs.close();
                stmt.close();
                conn.close();

            }
            catch(Exception e) {
                logger.error("Exception: " + e.getMessage(), e);
            }finally {
                if (conn != null) {

                        if(rs!=null)
                        rs.close();
                    if(stmt!=null)
                    stmt.close();
                        conn.close();

                }
            }

Any ideas?

Thanks in advance!