CODEWITHSUNDEEP
javamultithreading
sura2k
sura2k

Reputation: 7517

Java - Stop a long running thread which accesses database

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.

Upvotes: 12

Views: 9196

Answers (5)

Jatin
Jatin

Reputation: 31724

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.

Upvotes: 12

Nathan Hughes
Nathan Hughes

Reputation: 96394

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.

Upvotes: 9

amicngh
amicngh

Reputation: 7899

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.

Upvotes: 3

Alpesh Gediya
Alpesh Gediya

Reputation: 3794

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;
                       }
                   }

            }
         }

Upvotes: 0

Brian Agnew
Brian Agnew

Reputation: 272307

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.

Upvotes: 2

Related Questions