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(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! > > > > > > > > > > > > > > >
