When I Call Preparedstatement.Cancel() in a Jdbc Application, Does It Actually Kill It in an Oracle Database

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

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.

Stop or terminate long running query in JDBC

Possibly bit off, but I would propose using JDBC Timeout instead. It has the added benefit of actually terminating gracefully instead of by force.

With modern app servers you can usually define JDBC timeout from datasource configuration.

How can I abort a running JDBC transaction?

As mentioned by james, Statement.cancel() will cancel the execution of a running Statement (select, update, etc). The JDBC docs specifically say that Statement.cancel() is safe to run from another thread and even suggests the usage of calling it in a timeout thread.

After canceling the statement, you're still stuck with the job of rolling back the transaction. That is not documented as being safe to run from another thread. The Connection.rollback() should happen in the main thread doing all the other JDBC calls. You can handle that after the canceled Statement.execute...() call completes with a JDBCException (due to the cancel).

jdbc prepared statement with oracle NUMBER type

setLong() cannot take a BigInteger. If you truly have values exceeding the range of long in your database, then you may need to use setBigDecimal(), as there is no setBigInteger(), and your variable would have to be of type BigDecimal. If long encompasses the range of values in your database, then just use long and setLong().

java thread hangs while using executorService executing a oracle query

This problem has been solved~
after several real time executes , out dba monitor the db server side, and find that the query time is 210s which execeeds the query-out-time(180s), but the cancel request of the stamentent may not be effective and the db server may not response to that request . so the thread which porform the original query hangs. the similar situation see [When I call PreparedStatement.cancel() in a JDBC application, does it actually kill it in an Oracle database?

the solution can been done by following:
add sqlprofile to the target table，and make sql using skip scan to short the query time.

but the deepest reason why hanging will occur is still unknown.

---

Related Topics