Kill the long running queries after timeout

Question

We have application which contains the apache web server as entry point and application will run on another server. So in apache we configured the timeout as 40 secs, but in the application some query is taking more time to fetch the records which is more than 40 secs, due to that reason apache is throwing 5xx error. But the query is fetching the records from the DB after we got response from apache web server.

How to kill that query(or transaction) after 40 secs(i.e apache timeout)?

Thanks in advance.

Have you looked at the various timeouts you can configure via your connection pool/`DataSource`/query string or at the JDBC level? — billc.cn, Oct 07 '15 at 15:13
Normally if you google the name of your JDBC driver or connection pool and the word "timeout", you'll find the documentation that tells how to set these. For example, many JDBC driver has additional connection string parameters for timeouts. — billc.cn, Oct 07 '15 at 16:09

score 2 · Answer 1 · answered Oct 07 '15 at 15:06

2

You can use Spring's AsyncTaskExecutor.

Extended interface for asynchronous TaskExecutor implementations, offering an overloaded execute(Runnable, long) variant with a start timeout parameter as well support for Callable. Note: The Executors class includes a set of methods that can convert some other common closure-like objects, for example, PrivilegedAction to Callable before executing them.

answered Oct 07 '15 at 15:06

Andres

10,561
4
45
63

2

It does not stop long query in database. – sibnick Oct 07 '15 at 17:19
You may also have to define a timeout for your db operation. That´s usually done in the DB. – Andres Oct 07 '15 at 18:07

score 1 · Accepted Answer · answered Oct 09 '15 at 06:59

We can use Hibernate Transaction managers setDafaultTimeout method to specify the timeout to expire.

      HibernateTransactionManager transactionManager = new HibernateTransactionManager();
      transactionManager.setDefaultTimeout(int timeoutinSecs);

then hibernate will throw transaction timeout expired exception. According our requirement we can handle that exception.

score 0 · Answer 3 · answered Oct 07 '15 at 15:15

0

One way to stop the database query is Statement.cancel()

answered Oct 07 '15 at 15:15

ControlAltDel

33,923
10
53
80

score 0 · Answer 4 · answered Oct 07 '15 at 15:23

0

It depends from your environment. You can set transaction timeout in JTA, or you can use database specific features, e.g. postgres session parameter: statement_timeout

JTA timeout is not very useful if you have one long query, because JTA can't interrupt JDBC call.

EDIT

The JDBC API now has statement timeout: java.sql.Statement.setQueryTimeout(int timeout), but not all drivers are support it.

answered Oct 07 '15 at 15:23

sibnick

3,995
20
20

setQueryTimeOut(int time) this one we need to apply to each query. I have nearly 5-10 queries do we need to set the timeout for each query using this method or is their any generic way to add all those queries – SMKReddy Oct 07 '15 at 15:38
Usually it is possible to configure on datasource level e.g. https://tomcat.apache.org/tomcat-7.0-doc/jdbc-pool.html has queryTimeout attribute – sibnick Oct 07 '15 at 16:55

score 0 · Answer 5 · answered Sep 27 '17 at 15:18

0

You can vary well implement your own query handler and kill the query with whatever conditions you want.

Automatically killing long running fetch(Select) queries (MySql)

answered Sep 27 '17 at 15:18

Aftab

938
1
9
20

Kill the long running queries after timeout

5 Answers5