Java - Stop a long running thread which accesses database

Question

I start couple of threads, but I do not have their references to stop by signalling or something. For example, I can't pass a variable like running=false to those threads, because I do not have their references, but have their names.

I'm using a ThreadGroup and always I have the reference of it. So I can do something like this. Seems to be it doesn't work.

    Thread[] threads = new Thread[threadGroup.activeCount()];
    int count = threadGroup.enumerate(threads);
    for(int i = 0; i < count; i++){
        threads[i].interrupt();
    }

This is a sample of my thread.

    public void run{

         try{
             //myDAO.getRecords();
             //this takes 30seconds to 60
             //returns about 3 millions of records

         }catch(Exception e){
             //log
         }
    }

When this thread is executing, I want to stop it in the middle. Anyway batabase query is running, but I want to stop getting results.

Still I'm getting results even I call interrupt(). Are there any other ways to do this OR have I done anything wrong ? Ultimately the task is to cancel a long running sql query from Java.

score 12 · Accepted Answer · answered Jul 23 '13 at 14:29

Calling interrupt for a thread that is say waiting for the query output has no affect as most of the JDBC drivers are immune to the status. It will still remain blocked and the query will keep executing.

Calling cancel will kill the connection and the thread executing the query in the database. For once in a while it is ok but it also kills the connection. This can create serious problems and will soon turn out to be the bottleneck.

An alternative but a working solution would be to get the the ID of the thread executing the procedure/query (on the database side) and to call:

KILL QUERY id;

KILL QUERY terminates the statement that the connection is currently executing, but leaves the connection itself intact.

To know the ID, right In the procedure, have the first line as: SELECT CONNECTION_ID();. This Id can be used to terminate it.

Note that described query kill approach is MySQL-specific. Here is Oracle trick: http://stackoverflow.com/questions/466963/is-it-possible-to-kill-a-single-query-in-oracle-without-killing-the-session — Vadzim, Jun 08 '15 at 13:35

score 9 · Answer 2 · edited May 23 '17 at 11:52

9

If your DAO is using JDBC and you want to stop a query that is in progress, you could have another thread call cancel on the Statement:

void cancel() throws SQLException

Cancels this Statement object if both the DBMS and driver support aborting 
an SQL statement. This method can be used by one thread to
cancel a statement that is being executed by another thread.

Throws:
    SQLException - if a database access error occurs or this method is 
    called on a closed Statement 
    SQLFeatureNotSupportedException - if the JDBC driver does not support
    this method

You might have the run method delegate the DAO call to another thread, and have it listen for interruption and call cancel.

Here's a post where somebody's using a Spring JdbcTemplate to cancel a query. So it is working for someone out there (using MySQL).

Also see this answer describing how queries get canceled in Oracle.

edited May 23 '17 at 11:52

Community

1
1

answered May 16 '13 at 14:11

Nathan Hughes

94,330
19
181
276

I have no idea about this, but I'm going to look into it. – sura2k May 16 '13 at 14:22
BEWARE. cancel also kills the connection. If you are using it a lot, then it will degrade the performance – Jatin Jul 23 '13 at 14:24
@Jatin: letting a query run when it's not needed is also bad for performance. But it is a good thing to be aware of that the connection will be lost. – Nathan Hughes Jul 23 '13 at 14:33
@NathanHughes a better thing would be to use `Kill_query id` (if at all you manage to know the `id`). This does not kill the connection and only instructs the db thread to stop executing the query (more mentioned in below answer). I came across this situation and this looks the best approach – Jatin Jul 23 '13 at 15:45

amicngh · Answer 3 · 2013-05-17T05:28:29.540

3

Still I'm getting results event I call interrupt(). Are there any other ways to do this OR have I done anything wrong ?

When your thread got interrupted you need to check in run() whether your thread pass isInterrupted() condition .

I think interrupt is the best way to achieve this because an interrupt will unblock some blocking IO and synchronization requests. A bespoke solution cannot do this.

edited May 17 '13 at 05:28

answered May 16 '13 at 14:18

amicngh

7,831
3
35
54

I'm doing it right now, but I thought there might be a better way. – sura2k May 16 '13 at 14:21
2

I'm not sure about this at all, but I would guess that `interrupt` would not unblock a database connection. I know it does not unblock a socket connection. – toto2 May 16 '13 at 14:54

score 2 · Answer 4 · answered May 16 '13 at 14:03

2

Your thread code needs to catch the InterruptedException, and set the interrupted flag on your thread. See this JavaSpecialist newsletter for more info.

  try {
    // ... 
  } 
  catch (InterruptedException ex) {
    Thread.currentThread().interrupt(); // very important
    break;
  }

The thread to interrupt must not be computationally-bound. That is, it should be performing network IO, sleeping etc. in order to catch and respond to the InterruptedException. A loop such as while(1) {} won't be interruptable.

answered May 16 '13 at 14:03

Brian Agnew

268,207
37
334
440

It gives me this `Unreachable catch block for InterruptedException. This exception is never thrown from the try statement body` compilation error. – sura2k May 16 '13 at 14:11
I guess your DAO catches exceptions (and most likely won't handle this scenario properly) – Brian Agnew May 16 '13 at 14:14
`IntrruptedException` can be caught if there is a `sleep()` call. – sura2k May 16 '13 at 14:17
1

Exactly. InterruptedException only actually interrupts a small number of things (like sleep). In general, you need to use the **isInterrupted()** method to detect interrupts. – Edward Falk May 16 '13 at 15:53

score 0 · Answer 5 · answered May 16 '13 at 15:04

Below code will interrupt thread running indefinately ,after interruption thread will forced to stop.

         @Override public void run() 
           {   // infinite loop to process

                   while(true) // your variable 
                   {                              
                      // We've been interrupted: no more processing.
                      if(Thread.currentThread().isInterrupted()){
                         return;
                       }
                   }

            }
         }

Java - Stop a long running thread which accesses database

5 Answers5

Linked

Related