CODEWITHSUNDEEP
javaapachehibernatejpahikaricp
SMKReddy
SMKReddy

Reputation: 107

Kill the long running queries after timeout

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.

Upvotes: 4

Views: 5566

Answers (5)

Aftab
Aftab

Reputation: 938

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)

Upvotes: 0

SMKReddy
SMKReddy

Reputation: 107

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.

Upvotes: 1

sibnick
sibnick

Reputation: 4305

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.

Upvotes: 0

ControlAltDel
ControlAltDel

Reputation: 35096

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

Upvotes: 0

Andres
Andres

Reputation: 10725

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.

Upvotes: 2

Related Questions