当我在 JDBC 应用程序中调用 PreparedStatement.cancel() 时，它是否真的在 Oracle 数据库中杀死了它？

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

提问by Evan

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.

我有针对 Oracle 10g 数据库运行的 Java JDBC 应用程序。我设置了一个 PreparedStatement 来执行查询，然后调用 ps.executeQuery() 来运行它。有时查询需要很长时间，我需要杀死它。我有另一个线程访问 PreparedStatement 对象，并在其上调用 cancel()。

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?

我的问题是，这实际上会杀死数据库中的查询吗？或者它只是将它从客户端切断，而查询仍在 Oracle 内部的某个地方运行？

Thanks!

谢谢！

回答by Alohci

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.

请注意，我下面所说的内容是基于对 Oracle 使用中的观察和推断，并非基于对 Oracle 内部的任何深入了解。没有一个应该被认为是权威的。

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.

Nineside 在他们的第一段中说的是正确的。但是，请注意测试建议。并非所有长时间运行的 oracle 查询都是相同的。似乎查询是在两个阶段进行评估的，第一个阶段组合了足够的数据以知道如何以正确的顺序返回行，第二个阶段返回行填充它在第一阶段。两个阶段之间的工作分工还受到基于成本的优化器的设置的影响。例如第一行与所有行。

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.

现在，如果查询处于阶段 1，则取消请求似乎最多排队等待在阶段 1 结束时应用，这意味着查询将继续运行。

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.

在阶段2中，行成束返回，每束返回后，取消命令才能生效，因此假设驱动程序支持该命令，取消请求将导致查询被杀死。

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.

JDBC 取消命令的规范似乎没有说明如果查询不停止运行会发生什么，因此该命令可能会等待终止的确认，或者可能会超时并在查询仍在运行的情况下返回。

回答by John M

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

答案是这是一个实施质量问题。如果您查看Statement.cancel()的javadoc，它会说“如果 DBMS 和驱动程序都支持中止 SQL 语句”就会发生。

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.

根据我使用各种版本的 Oracle JDBC 驱动程序的经验， Statement.cancel() 似乎可以满足您的需求。查询似乎在取消时立即停止执行。

回答by ninesided

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.

这取决于您使用的驱动程序，驱动程序和数据库都需要支持取消语句，以便它按您的需要工作。Oracle 确实支持此功能，但您需要了解更多有关您正在使用的特定驱动程序的信息才能确定。

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

或者，您可以通过在启动和取消长时间运行的查询后查看数据库来运行一个简单的测试。