When I call PreparedStatement.cancel() in a JDBC application, does it actually kill it in an Oracle database?

Question

Hi All,

I have Java JDBC application running against an Oracle 10g Database. I set up a PreparedStatement to execute a query, and then call ps.executeQuery() to run it. Occasionally the query takes a long time, and I need to kill it. I have another thread access that PreparedStatement object, and call cancel() on it.

My question is, does this actually kill the query in the database? Or does it just sever it from the client, and the query is still running somewhere in the bowels of Oracle?

Thanks!

Answer 1

It depends on the driver you are using, both the driver and the database need to support the cancellation of a statement in order for it to work as you want. Oracle does support this feature, but you'd need to know more about the specific driver you are using to know for sure.

Alternatively, you could run a simple test by looking at the database after initiating and canceling a long running query.

Answer 2

Please note that what I say below is based on observations and inferences of Oracle in use, and is not based on any deep understanding of Oracle's internals. None of it should be considered authoritative.

What ninesided said in their first paragraph is correct. However, beware the test suggested. Not all long-running oracle queries are the same. It seems that queries are evaluated over two phases, first a phase that combines up sufficient data to know how to return the rows in the right order, and second a phase that returns the rows filling in the gaps that it didn't compute in the first phase. The division of work between the two phases is also affected by the settings of the cost-based optimizer. e.g. First-rows vs All-rows.

Now, if the query is in phase 1, the cancel request seems at best to be queued up to be applied at the end of phase 1, which means that the query continues operating.

In phase 2, rows are returned in bunches, and after each bunch, the cancel command can take effect, so assuming the driver supports the command, the cancel request will result in the query being killed.

The specification for the JDBC cancel command does not seem to say what should happen if the query does not stop running, and therefore the command may wait for confirmation of the kill, or may timeout and return with the query still running.

Answer 3

The answer is that it's a quality-of-implementation issue. If you look at the javadoc for Statement.cancel(), it says it'll happen "if both the DBMS and driver support aborting an SQL statement".

In my experience with various versions of Oracle JDBC drivers, Statement.cancel() seems to do what you'd want. The query seems to stop executing promptly when cancelled.