multithreading - 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.

Answer 1

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.