6

I am using Java Future class to execute a program which connect Oracle database. However sometimes the query is pretty slower than expected. I can cancel the future by future.cancel method.

Let's assume i cancelled the future thread via future.cancel. Will the query stops executing in the Oracle or Only the application thread will gets stopped/cancelled and Oracle query will keep running on the DB side.

We are submitting many future and some are pretty slow..Timeout doesn't end the submit but it can be set only for the future.get but the future still continues as per the java specification.

What would be the actual behavior in this case.

Thanks

Max Peng
  • 2,879
  • 1
  • 26
  • 43
Kathir
  • 2,733
  • 12
  • 39
  • 67
  • 1
    The Oracle DB has it's own execution thread that does not entirely controlled by the java program, after passing a query to the DB it will proceed in executing the query thus if the waiting program is terminated the executing query in the DB will continue. – Francis G Nov 06 '19 at 02:06
  • You are right..So how to stop both the application and DB thread. Is there a way to do it via Java? – Kathir Nov 06 '19 at 04:09
  • Francis, one of my collague said Oracle DB will also gets terminated when the future is cancelled. But we need to experiment. Are you sure that Oracle thread will still continues even when the future is cancelled. – Kathir Nov 06 '19 at 15:54

2 Answers2

0

You can try to send a termination query to the DB where you will use the PID of the query you want to terminate, try this link for the termination of query with different ways How to kill a running SELECT statement. Put the process to a thread then try add something like isInterrupted() or isAlive() to check the status of the thread, then send the query for termination of the running query if it was terminated. That's the best I can think of to terminate a running DB in a program.

Francis G
  • 1,040
  • 1
  • 7
  • 21
  • Francis, We are using hibernate. When future takes more time, can we end the hibernate session connected to the DB, so that the thread will be terminated in DB side as well. Please let me know what you think on this – Kathir Nov 06 '19 at 15:53
  • that's why I said you will need to put the process in a thread to be able to check if the thread was interrupted to make sure the termination process to the DB will be sent. – Francis G Nov 07 '19 at 00:49
0

Cancel on future will not terminate the Oracle query. Even worse, Completablefuture's cancel simply causes a CancellationException making it complete exceptionally. This will not stop the submitted task, or even try to interrupt it.

From the docs:

CompletableFuture.cancel(boolean mayInterruptIfRunning):

If not already completed, completes this CompletableFuture with a CancellationException. Dependent CompletableFutures that have not already completed will also complete exceptionally, with a CompletionException caused by this CancellationException.

mayInterruptIfRunning - this value has no effect in this implementation because interrupts are not used to control processing.

You could set a timeout on the db connection for read with OracleConnection.CONNECTION_PROPERTY_THIN_READ_TIMEOUT on your connection (Setting Network Timeout for JDBC connection). This does not kill Oracle's thread, but times out the statement read.

Or use Statement.cancel to try cancel running statement in the database. (When I call PreparedStatement.cancel() in a JDBC application, does it actually kill it in an Oracle database?). This is intended to kill database execution thread, but the db might not always honor it.

Beyond this I would suggest using a small FixedThreadPool executor with your CompletableFutures to control the number of parallel database threads.

EDIT

For doing the same on a hibernate session use Session#cancelQuery()

Community
  • 1
  • 1
Martin'sRun
  • 522
  • 3
  • 11
  • Martin, We are using hibernate. When future takes more time, can we end the hibernate session connected to the DB, so that the thread will be terminated in DB side as well. Please let me know what you think on this. – Kathir Nov 06 '19 at 15:53
  • Please update the original question, and tag it hibernate. Could you also share a code snippet on how the future is being created. Ending the session to kill the query seems overkill to me when you could simply call session.cancelQuery. Editing answer to include this. – Martin'sRun Nov 07 '19 at 04:48