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(20000);
> > >
> >
> >
> > > pp.setTimeBetweenEvictionRunsMillis(20000);
> > >
> >
> > 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 alternative and less aggressive approach to all of this might be to
> use
> > the SlowQueryReport or SlowQueryReportJmx interceptors. These will watch
> > for slow queries (queries that take longer than the threshold you define
> to
> > execute) and report them. It does not actively close or clean up long
> > running connections, but it does provide you with a lot of helpful
> > information about the problem when a query does take a while to run.
> >
> > Dan
> >
> >
> >
> > > 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!
> > >
> >
>
