Re: Query timeouts using tomcat jdbc pool

2014-07-23 Thread Vasily Kukhta
Thank you, I have changed timeBetweenEvictionRunsMillis value and it is
working properly now. Another problem has appeared: although the timeout is
handled correctly, no exception is thrown. I thought an SQLException would
be thrown if query takes too long. Maybe pool does not throw exceptions at
all in that situation?

Than you!


2014-07-21 20:40 GMT+04:00 Daniel Mikusa dmik...@gopivotal.com:

 On Mon, Jul 21, 2014 at 11:05 AM, Vasily Kukhta v.b.kuk...@gmail.com
 wrote:

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

 Have you taken thread dumps of your application when it hangs?  What do
 these show your threads are doing?


 
  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);
 

 It's probably not necessary to have all of these enabled.  I usually only
 see testOnBorrow and testWhileIdle enabled.  TestOnReturn is almost
 worthless, in my opinion.  TestOnConnect might be helpful to catch
 connection issues at boot, but generally a connection that is just created
 should be good unless you have configuration problems.


  pp.setMaxWait(2000);
  pp.setMinEvictableIdleTimeMillis(2);
 


  pp.setTimeBetweenEvictionRunsMillis(2);
 

 This might be high given your aggressive settings for
 removeAbandonedTimeout.  The timeBetweenEvictionRunsMillis setting dictates
 how often the pool looks for idle connections, abandoned connections, and
 how often it validates idle connections.  If you set this to 20 secs, using
 an abandoned timeout less than 20 seconds is probably not going to be
 accurate.

 For example, if the cleaner thread runs and your application has been
 holding a connection for 2999ms, it won't consider the connection
 abandoned.  However the cleaner thread won't run again for another 20 secs
 (based on the config value you used), thus the application can continue
 using that connection for way over the abandoned timeout you've configured.


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

 Seems OK.


 
  pp.setMaxAge(3000);
 

 This seems pretty short and might limit how much pooling actually occurs.
  Also, since this is enforced when you return the connection to the pool
 it's not going to help with your current issue.  What are you trying to
 accomplish by setting this value so low?


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

 This is a pretty low value for abandoned timeout.  If you're hoping to use
 this to limit how long a query can executed, it's probably not the best
 approach. This will limit the amount of time that a connection can be
 checked out of the connection pool, and unless you also configure
 the ResetAbandonedTimer interceptor, it's going to limit the total time
 your application has to use the connection.  If you configure the
 ResetAbandonedTimer interceptor, it will function closer to the use case
 you've described but it's probably not the most efficient way to accomplish
 this task.

 Generally you'd set the remove abandoned timeout to reclaim connections
 that were not properly closed, not to reclaim connections where a query is
 stuck or running for a long time.  I think the jdbc driver can more easily
 handle killing stuck / long running queries.  See next comment.


 
 
 
 pp.setJdbcInterceptors(org.apache.tomcat.jdbc.pool.interceptor.QueryTimeoutInterceptor(queryTimeout=3));
 

 I'm a little surprised this isn't working for you.  By setting the
 statement's query timeout, you're telling the jdbc driver to limit how long
 each query can run.  If a query runs longer than 3 seconds, you should get
 an SQLTimeoutException.  Otherwise there's an issue / limitation with your
 jdbc driver.

 I would suggest setting up a test without the connection pool to validate
 that the jdbc driver and your method for slowing down the database is
 working correctly.  In theory this test should be something like make a
 connection, set the query timeout, 

Re: Query timeouts using tomcat jdbc pool

2014-07-23 Thread Filip Hanik
Vasily, the exception depends on where the timeout occurs.

If the timeout is triggered by the driver, because you hit the
setQueryTimeout limit
http://docs.oracle.com/javase/7/docs/api/java/sql/Statement.html#setQueryTimeout(int)
then yes, as per javadoc, it is up to the JDBC driver to throw an exception.


Filip




On Wed, Jul 23, 2014 at 10:04 AM, Vasily Kukhta v.b.kuk...@gmail.com
wrote:

 Thank you, I have changed timeBetweenEvictionRunsMillis value and it is
 working properly now. Another problem has appeared: although the timeout is
 handled correctly, no exception is thrown. I thought an SQLException would
 be thrown if query takes too long. Maybe pool does not throw exceptions at
 all in that situation?

 Than you!


 2014-07-21 20:40 GMT+04:00 Daniel Mikusa dmik...@gopivotal.com:

  On Mon, Jul 21, 2014 at 11:05 AM, Vasily Kukhta v.b.kuk...@gmail.com
  wrote:
 
   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...
  
 
  Have you taken thread dumps of your application when it hangs?  What do
  these show your threads are doing?
 
 
  
   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);
  
 
  It's probably not necessary to have all of these enabled.  I usually only
  see testOnBorrow and testWhileIdle enabled.  TestOnReturn is almost
  worthless, in my opinion.  TestOnConnect might be helpful to catch
  connection issues at boot, but generally a connection that is just
 created
  should be good unless you have configuration problems.
 
 
   pp.setMaxWait(2000);
   pp.setMinEvictableIdleTimeMillis(2);
  
 
 
   pp.setTimeBetweenEvictionRunsMillis(2);
  
 
  This might be high given your aggressive settings for
  removeAbandonedTimeout.  The timeBetweenEvictionRunsMillis setting
 dictates
  how often the pool looks for idle connections, abandoned connections, and
  how often it validates idle connections.  If you set this to 20 secs,
 using
  an abandoned timeout less than 20 seconds is probably not going to be
  accurate.
 
  For example, if the cleaner thread runs and your application has been
  holding a connection for 2999ms, it won't consider the connection
  abandoned.  However the cleaner thread won't run again for another 20
 secs
  (based on the config value you used), thus the application can continue
  using that connection for way over the abandoned timeout you've
 configured.
 
 
   pp.setValidationInterval(3000);
   pp.setValidationQuery(SELECT 1 FROM DUAL);
  
 
  Seems OK.
 
 
  
   pp.setMaxAge(3000);
  
 
  This seems pretty short and might limit how much pooling actually occurs.
   Also, since this is enforced when you return the connection to the pool
  it's not going to help with your current issue.  What are you trying to
  accomplish by setting this value so low?
 
 
   pp.setRemoveAbandoned(true);
   pp.setRemoveAbandonedTimeout(3);
  
 
  This is a pretty low value for abandoned timeout.  If you're hoping to
 use
  this to limit how long a query can executed, it's probably not the best
  approach. This will limit the amount of time that a connection can be
  checked out of the connection pool, and unless you also configure
  the ResetAbandonedTimer interceptor, it's going to limit the total time
  your application has to use the connection.  If you configure the
  ResetAbandonedTimer interceptor, it will function closer to the use case
  you've described but it's probably not the most efficient way to
 accomplish
  this task.
 
  Generally you'd set the remove abandoned timeout to reclaim connections
  that were not properly closed, not to reclaim connections where a query
 is
  stuck or running for a long time.  I think the jdbc driver can more
 easily
  handle killing stuck / long running queries.  See next comment.
 
 
  
  
  
 
 pp.setJdbcInterceptors(org.apache.tomcat.jdbc.pool.interceptor.QueryTimeoutInterceptor(queryTimeout=3));
  
 
  I'm a little surprised this isn't 

Re: Query timeouts using tomcat jdbc pool

2014-07-23 Thread Vasily Kukhta
So, it means that if the timeout is detected not using setQueryTimeout
method, but by the tomcat pool settings (setMaxAge or
setTimeBetweenEvictionRunsMillis), it means that no exception can be thrown
at all?

Vasily



2014-07-23 12:14 GMT+04:00 Filip Hanik fi...@hanik.com:

 Vasily, the exception depends on where the timeout occurs.

 If the timeout is triggered by the driver, because you hit the
 setQueryTimeout limit

 http://docs.oracle.com/javase/7/docs/api/java/sql/Statement.html#setQueryTimeout(int)
 then yes, as per javadoc, it is up to the JDBC driver to throw an
 exception.


 Filip




 On Wed, Jul 23, 2014 at 10:04 AM, Vasily Kukhta v.b.kuk...@gmail.com
 wrote:

  Thank you, I have changed timeBetweenEvictionRunsMillis value and it is
  working properly now. Another problem has appeared: although the timeout
 is
  handled correctly, no exception is thrown. I thought an SQLException
 would
  be thrown if query takes too long. Maybe pool does not throw exceptions
 at
  all in that situation?
 
  Than you!
 
 
  2014-07-21 20:40 GMT+04:00 Daniel Mikusa dmik...@gopivotal.com:
 
   On Mon, Jul 21, 2014 at 11:05 AM, Vasily Kukhta v.b.kuk...@gmail.com
   wrote:
  
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...
   
  
   Have you taken thread dumps of your application when it hangs?  What
 do
   these show your threads are doing?
  
  
   
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);
   
  
   It's probably not necessary to have all of these enabled.  I usually
 only
   see testOnBorrow and testWhileIdle enabled.  TestOnReturn is almost
   worthless, in my opinion.  TestOnConnect might be helpful to catch
   connection issues at boot, but generally a connection that is just
  created
   should be good unless you have configuration problems.
  
  
pp.setMaxWait(2000);
pp.setMinEvictableIdleTimeMillis(2);
   
  
  
pp.setTimeBetweenEvictionRunsMillis(2);
   
  
   This might be high given your aggressive settings for
   removeAbandonedTimeout.  The timeBetweenEvictionRunsMillis setting
  dictates
   how often the pool looks for idle connections, abandoned connections,
 and
   how often it validates idle connections.  If you set this to 20 secs,
  using
   an abandoned timeout less than 20 seconds is probably not going to be
   accurate.
  
   For example, if the cleaner thread runs and your application has been
   holding a connection for 2999ms, it won't consider the connection
   abandoned.  However the cleaner thread won't run again for another 20
  secs
   (based on the config value you used), thus the application can continue
   using that connection for way over the abandoned timeout you've
  configured.
  
  
pp.setValidationInterval(3000);
pp.setValidationQuery(SELECT 1 FROM DUAL);
   
  
   Seems OK.
  
  
   
pp.setMaxAge(3000);
   
  
   This seems pretty short and might limit how much pooling actually
 occurs.
Also, since this is enforced when you return the connection to the
 pool
   it's not going to help with your current issue.  What are you trying to
   accomplish by setting this value so low?
  
  
pp.setRemoveAbandoned(true);
pp.setRemoveAbandonedTimeout(3);
   
  
   This is a pretty low value for abandoned timeout.  If you're hoping to
  use
   this to limit how long a query can executed, it's probably not the best
   approach. This will limit the amount of time that a connection can be
   checked out of the connection pool, and unless you also configure
   the ResetAbandonedTimer interceptor, it's going to limit the total time
   your application has to use the connection.  If you configure the
   ResetAbandonedTimer interceptor, it will function closer to the use
 case
   you've described but it's probably not the most efficient way to
  accomplish
   this task.
  
   Generally you'd set the remove 

Re: Query timeouts using tomcat jdbc pool

2014-07-23 Thread Filip Hanik
maxAge is not a timeout setting. It simply means the connection gets
retired(closed) instead of returned to the pool after a certain amount of
time

timeBetweenEvictionRunsMillis is not a timeout either. It is the interval
that the thread checks for timeouts, but not query, connection checkout
time out.

removeAbandonedTimeout - is the timeout you're looking for. This is the
timeout for a connection usage. That is the time between the
DataSource.getConnection() call and the Connection.close() call.

And whether an exception is thrown here, depends on the driver. the pool
simply calls close() on the driver connection, if that yields an exception
if another thread is executing a query or not, depends on the driver itself.

See
http://tomcat.apache.org/tomcat-7.0-doc/jdbc-pool.html


On Wed, Jul 23, 2014 at 10:56 AM, Vasily Kukhta v.b.kuk...@gmail.com
wrote:

 So, it means that if the timeout is detected not using setQueryTimeout
 method, but by the tomcat pool settings (setMaxAge or
 setTimeBetweenEvictionRunsMillis), it means that no exception can be thrown
 at all?

 Vasily



 2014-07-23 12:14 GMT+04:00 Filip Hanik fi...@hanik.com:

  Vasily, the exception depends on where the timeout occurs.
 
  If the timeout is triggered by the driver, because you hit the
  setQueryTimeout limit
 
 
 http://docs.oracle.com/javase/7/docs/api/java/sql/Statement.html#setQueryTimeout(int)
  then yes, as per javadoc, it is up to the JDBC driver to throw an
  exception.
 
 
  Filip
 
 
 
 
  On Wed, Jul 23, 2014 at 10:04 AM, Vasily Kukhta v.b.kuk...@gmail.com
  wrote:
 
   Thank you, I have changed timeBetweenEvictionRunsMillis value and it is
   working properly now. Another problem has appeared: although the
 timeout
  is
   handled correctly, no exception is thrown. I thought an SQLException
  would
   be thrown if query takes too long. Maybe pool does not throw exceptions
  at
   all in that situation?
  
   Than you!
  
  
   2014-07-21 20:40 GMT+04:00 Daniel Mikusa dmik...@gopivotal.com:
  
On Mon, Jul 21, 2014 at 11:05 AM, Vasily Kukhta 
 v.b.kuk...@gmail.com
wrote:
   
 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...

   
Have you taken thread dumps of your application when it hangs?
  What
  do
these show your threads are doing?
   
   

 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);

   
It's probably not necessary to have all of these enabled.  I usually
  only
see testOnBorrow and testWhileIdle enabled.  TestOnReturn is almost
worthless, in my opinion.  TestOnConnect might be helpful to catch
connection issues at boot, but generally a connection that is just
   created
should be good unless you have configuration problems.
   
   
 pp.setMaxWait(2000);
 pp.setMinEvictableIdleTimeMillis(2);

   
   
 pp.setTimeBetweenEvictionRunsMillis(2);

   
This might be high given your aggressive settings for
removeAbandonedTimeout.  The timeBetweenEvictionRunsMillis setting
   dictates
how often the pool looks for idle connections, abandoned connections,
  and
how often it validates idle connections.  If you set this to 20 secs,
   using
an abandoned timeout less than 20 seconds is probably not going to be
accurate.
   
For example, if the cleaner thread runs and your application has been
holding a connection for 2999ms, it won't consider the connection
abandoned.  However the cleaner thread won't run again for another 20
   secs
(based on the config value you used), thus the application can
 continue
using that connection for way over the abandoned timeout you've
   configured.
   
   
 pp.setValidationInterval(3000);
 pp.setValidationQuery(SELECT 1 FROM DUAL);

   
Seems OK.
   
   

 pp.setMaxAge(3000);

   
This seems pretty short and might 

Query timeouts using tomcat jdbc pool

2014-07-21 Thread 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(2);
pp.setTimeBetweenEvictionRunsMillis(2);

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!


Re: Query timeouts using tomcat jdbc pool

2014-07-21 Thread Daniel Mikusa
On Mon, Jul 21, 2014 at 11:05 AM, Vasily Kukhta v.b.kuk...@gmail.com
wrote:

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


Have you taken thread dumps of your application when it hangs?  What do
these show your threads are doing?



 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);


It's probably not necessary to have all of these enabled.  I usually only
see testOnBorrow and testWhileIdle enabled.  TestOnReturn is almost
worthless, in my opinion.  TestOnConnect might be helpful to catch
connection issues at boot, but generally a connection that is just created
should be good unless you have configuration problems.


 pp.setMaxWait(2000);
 pp.setMinEvictableIdleTimeMillis(2);



 pp.setTimeBetweenEvictionRunsMillis(2);


This might be high given your aggressive settings for
removeAbandonedTimeout.  The timeBetweenEvictionRunsMillis setting dictates
how often the pool looks for idle connections, abandoned connections, and
how often it validates idle connections.  If you set this to 20 secs, using
an abandoned timeout less than 20 seconds is probably not going to be
accurate.

For example, if the cleaner thread runs and your application has been
holding a connection for 2999ms, it won't consider the connection
abandoned.  However the cleaner thread won't run again for another 20 secs
(based on the config value you used), thus the application can continue
using that connection for way over the abandoned timeout you've configured.


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


Seems OK.



 pp.setMaxAge(3000);


This seems pretty short and might limit how much pooling actually occurs.
 Also, since this is enforced when you return the connection to the pool
it's not going to help with your current issue.  What are you trying to
accomplish by setting this value so low?


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


This is a pretty low value for abandoned timeout.  If you're hoping to use
this to limit how long a query can executed, it's probably not the best
approach. This will limit the amount of time that a connection can be
checked out of the connection pool, and unless you also configure
the ResetAbandonedTimer interceptor, it's going to limit the total time
your application has to use the connection.  If you configure the
ResetAbandonedTimer interceptor, it will function closer to the use case
you've described but it's probably not the most efficient way to accomplish
this task.

Generally you'd set the remove abandoned timeout to reclaim connections
that were not properly closed, not to reclaim connections where a query is
stuck or running for a long time.  I think the jdbc driver can more easily
handle killing stuck / long running queries.  See next comment.




 pp.setJdbcInterceptors(org.apache.tomcat.jdbc.pool.interceptor.QueryTimeoutInterceptor(queryTimeout=3));


I'm a little surprised this isn't working for you.  By setting the
statement's query timeout, you're telling the jdbc driver to limit how long
each query can run.  If a query runs longer than 3 seconds, you should get
an SQLTimeoutException.  Otherwise there's an issue / limitation with your
jdbc driver.

I would suggest setting up a test without the connection pool to validate
that the jdbc driver and your method for slowing down the database is
working correctly.  In theory this test should be something like make a
connection, set the query timeout, run a query and wait for the exception.
 If that does not timeout, then it's definitely not going to timeout with
the connection pool because the jdbc driver handles the actual timing out
of the request.

If your test works, then you might want to look and see if anything else is
setting the timeout on the statement object.  The interceptor should set
the value when a new statement object is created.  If something else sets
the timeout, it would override the value set by the interceptor.

An 

Re: Query timeouts using tomcat jdbc pool

2014-07-21 Thread Christopher Schultz
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA256

Vasily,

On 7/21/14, 11:05 AM, Vasily Kukhta wrote:
 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.

It's important to note that nothing in the JDBC driver will prevent a
/series/ of queries from taking more than 3 seconds. For example:

 String connprops = 
 oracle.net.CONNECT_TIMEOUT=3000;oracle.jdbc.ReadTimeout=3000; +
 oracle.net.READ_TIMEOUT=3000;
 
 pp.setMaxWait(2000);
 
 pp.setJdbcInterceptors(org.apache.tomcat.jdbc.pool.interceptor.QueryTimeoutInterceptor(queryTimeout=3));

With
 
the above settings, issuing 10 queries can take up to 35 seconds:
up to 2 seconds to wait for a connection from the pool, up to 3
seconds to (re)connect to the database, then up to 3 seconds for each
of the 10 queries.

If you want to make sure that entire HTTP transactions don't take too
long then you'll have to keep track of the overall timeout yourself.

 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?

What query are you executing that should take longer than 3 seconds,
and therefore trigger the timeout?

- -chris
-BEGIN PGP SIGNATURE-
Version: GnuPG v1
Comment: GPGTools - http://gpgtools.org
Comment: Using GnuPG with Thunderbird - http://www.enigmail.net/

iQIcBAEBCAAGBQJTzVswAAoJEBzwKT+lPKRY8m8P/3bh/pKfuS1oSodKgpyQFIvz
3OXlaSLK5QZBLsL9EFJJDw6bpNPKYHd3GdpQRmGqJYtG98qoXBVztZJPckPZBQwH
6mWuMAdbhwYZFNJVoyG72EfcZAamv+z8n4+WMTR6kjD6XbmAjAQMhgItOEMMnfSR
xyhb+w/dbwFk33Qrwf8Jfhj9/LHNBg99AS0SY8EgKGUT4PQZJ0DR2jY90oVWTy/k
ZaHXQxjNM1eiZPXTZmprsq+NVaLegG6BrJy0kTWgPrDk3jilgMgoFWDHb4MNuGCR
iSuWd8i+Sy6Y5VgYQBF5+UQylys7an3rZmYNRCr0GNj4NK2o9ipkvhTvaoH79G/z
26kungun8AEsmbBnK/YqzVYRxH66Ysy2mudGJYuDvNo55vJ7NoMtNt3Is0tF3Tgk
A/QjG3p4qPFMLeOMhlBqUvSAjsYIWsSBy9/698nkgDvILXKB4yMhyn+/jtoWImvy
EkHIlrKPAtGkMoRPqIODisTCDyVXd2bBntUM6rWfB7LrI8Hp8PLIN3d3IDXl3Mzc
qlTQiL0z5q9oOgxpaIXccWvR0kY7gobipASjyNfi09BGXAvM7rigFIHj9BO28x46
38r6vV2Ok7tatDJM7C2Xxoec69ke7CgQx3KEaabbCNzv3jFBbhfxULBPVtSoqzkY
XuqS6VKmEWnYoHbwbfkv
=f7nf
-END PGP SIGNATURE-

-
To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org
For additional commands, e-mail: users-h...@tomcat.apache.org