Java: Killing a Thread doing DB operation

Question

I have a java thread which performs some heavy database operation. I am coding an api to kill this java thread. I am using executor framework for this, so once i get future, i call future.cancel() and then check for thread interruption.

Above logic works fine except the scenarions where thread is performing DB operation, which imples that killing a thread will first involve killing DB connection which means DBA intervention.

My aim is to create an api which doesn't need any intervention from support teams.

Any ideas on how to go about this will be extremely helpful.

CODE:

private void killBatches1() {
    if (killBatchRunning.compareAndSet(false, true)) {
    try{
        Iterator<Future<?>> futureIterator = futuresForBatch.iterator();
        while (futureIterator.hasNext()) {
            Future<?> future = futureIterator.next();
            if (future.cancel(true))
                ;
            futureIterator.remove();
        }
      }finally{
        killBatchRunning.set(false);
        }
    }

I have added the code. Question was more on logic so i had not pasted it and people have just pounded with downvotes!! — Lokesh
– Lokesh, Commented Feb 28, 2013 at 8:06
Can you explain: killing a thread will first involve killing DB connection which means DBA intervention? I don't see why interrupting a thread would "kill" your db connection... — assylias
– assylias, Commented Feb 28, 2013 at 8:11
So if my thread is performing a Database operation then i can't check for thread interuption to db operation is over. So only option is to kill db connection where we need DBA. — Lokesh
– Lokesh, Commented Feb 28, 2013 at 8:18
@Lokesh I am involved with same issue. Did you find a convincing solution? 2% of the data has records in lakhs/millions. So in spite of indexes, order by takes more time for this 2%. Every request has 10 inputs and each time, one of the input can come from this 2% data. My only thought out solution is to have an estimate of blocked out threads per second and increase the size of thread pool such that thread pool does not reach its limit and block subsequent requests. — user104309
– user104309, Commented Nov 15, 2017 at 18:15

vainolo · Accepted Answer · 2013-02-28 08:18:13Z

1

DON'T DO THAT!!! if your thread started DB operations and you kill it, you may cause problems in your database. Suppose your thread was updating the database, and you killed it. Did it finish the transaction? are the changes committed? If you kill the thread you will then have to do a rollback, which, since you are saying "heavy database operation", can be VERY EXPENSIVE! (I have experienced database rollbacks of many hours... and I came to hate them).

Technically, you could try to access the OS and search for the DB connection process and kill it, but as I said above, this is not only bad practice but dangerous.

So, my advice is to find some way to do the big database operation in small chunks, so you have better responsiveness and control.

answered Feb 28, 2013 at 8:18

vainolo

7,0074 gold badges26 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Lokesh Over a year ago

I agree with your point. We have a design where we never rollback and instead we deprecate all previously inserted records. So a db connection kill should be ok once we resstart the java thread after killing previous one.

Lokesh Over a year ago

The scenario is as following : We deal with million of records which are brought to main memory so we have 2 bottle necks. First what if sql is running slow for some reason or second thing can be if we want to stop current calcultions due to some bad data [we dont want to wait till whole calculation is complete]. In both cases we will need kill thread functionality but in first scenario DB operation is a problem.

vainolo Over a year ago

killing the thread because DB is running slow is not solving the problem, It's ignoring it. maybe you should bring less records for each thread and have more threads? maybe you have an indexing problem? What you are doing will work, bit IMHO it is not the proper way to do it.

Lokesh Over a year ago

So if sql is slow i agree DBA intervention is must to say run stats on tables but there can be many other scenario like dealing with bad data. We have very strict SLA's so say if a thread [which is a batch for us] has to be rerun for some reason then we dont want to wait for thread [batch] to finish, we just want to kill it immediately.

vainolo Over a year ago

But doing it programatically means that this is happening a lot. Maybe you can ask a question explaining your general architecture and asking how you can solve your problem?

Mikhail · Accepted Answer · 2013-02-28 08:29:25Z

0

Theoretically you could call thread interrupt(). If JDBC driver supports interruption, appropriate exception would be called, which should be caught in your code. If it does not, you can try to close connection. That should throw exception too. Anyway study this question in JDBC driver`s specification.

answered Feb 28, 2013 at 8:29

Mikhail

4,22317 silver badges32 bronze badges

Collectives™ on Stack Overflow

Java: Killing a Thread doing DB operation

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related