Stop a Java thread which calls an Oracle procedure

Question

Hello,

On a Spring/Hibernate web application, I'm using a Work Queue, built with Java Threads. The Threads's run() method calls an Oracle procedure, which can last a bunch of minutes/hours. All the Work Queue's threads are stored in a list.

I would like to build an interface (JSP), where I could display a list of the running jobs, and allow the users to "kill" a job.

I can't use the Thread.stop() method, which is deprecated. I also already tried the different methods with interrupt(), but I did not succeed in stopping the thread during the Oracle call.

• Do you think it's possible to stop a thread during an Oracle call ?
• Otherwise, should i look for another way to do this work queue (without threads) ?

Thanks a lot !

Answer 1

You need to invoke Statement.cancel() from the web application, as indicated in this answer on SO. This might not make for a great implementation, as access to the Statement object needs to be made available across two threads at a bare minimum, and this is not necessarily a good programming practice.

EDIT: In case I wasn't clear enough, you need to store references to the Statement objects that are executing the queries, and make them available to the cancel operations (that will execute in different threads) if users choose to cancel the execution of the jobs. If this approach fails (usually due to an incorrect assumption of the thread-safety of the Statement object), you can interrupt the thread executing the statement and cancel the execution of the statement from the original thread.

Answer 2

Problem is that interrupt doesn't signal Oracles JDBC driver to hold up. To my knowledge, killing the thread with the deprecated Thread.stop() is your only way unless you redesign the solution somewhat and not even that may guarantee that the operation in the database actually stopped. It may still be running unless you cancel it by exposing the statement to the UIs control logic and after killing the thread you would have trouble sending the ROLLBACK command to the database.

My tip is to either let it run but flagging to the thread that it should do a ROLLBACK when its done eventually done.

The other is to make your procedure not run for such a long time and add intermediary steps that allows you to stop the thread cleanly.

Answer 3

"should i look for another way to do this work queue (without threads) "

It sounds to me like you're re-inventing the database job functionality which Oracle already provides.

There is the DBMS_SCHEDULER which was introduced in Oracle 10g, which is rather sophisticated. In earlier versions there is only DBMS_JOB, which is still pretty good: it will do a better job (ho ho) of running stored procedures in the background than your current implementation.

Answer 4

This is more of a backup to other answers.

The big problem you will have is that while it is possible to kill the Java thread, the pl/sql block may carry on executing on the server for sometime afterwards.

I've had this issue with C code in the past and your option is either to try sending a 'cancel' to the server and wait for it to be acknowledged (which may be never) or to 'do your best', terminate the running thread anyway, and let Oracle clean-up later when it detects the client process has gone.

APCs answer is good - this sounds like an ideal case for Oracle's background job scheduler, which offers a nice view over executing tasks, and an API for starting/stopping/etc.

A small advantage here is that you are not tying up connections from your JDBC connection pool with long running transactions.

Where I think you may still have a problem stopping a running task in the middle. The Oracle documentation for DBMS_JOB says there is no way to stop a job once started.

One solution I've done is to have my background job periodically check for an 'exit' condition (i.e. does a row exist in table) - but that only works if you have a loop.