30

Is there a way to prematurely abort a transaction? Say, I have sent a command to the database which runs five minutes and after four, I want to abort it.

Does JDBC define a way to send a "stop whatever you are doing on this connection" signal to the DB?

sblundy
  • 60,628
  • 22
  • 121
  • 123
Aaron Digulla
  • 321,842
  • 108
  • 597
  • 820

4 Answers4

38

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).

John M
  • 13,053
  • 3
  • 27
  • 26
  • 2
    Here is oracle-specific behaviour on calling `Statement.cancel()`: http://stackoverflow.com/a/659063/603516 – Vadzim May 29 '15 at 15:28
17

I am guessing what you want to do is prevent your application blocking on long running queries / transactions. To this end, JDBC supports the concept of a query time out. You can set the query timeout using this:

java.sql.Statement.setQueryTimeout(seconds)

And handle the SQLException thrown by the execute() method by rolling back the transaction (of course, that would only work if you have autocommit set to false, and your JDBC driver supports Statement.cancel()).

Dmitry Mitskevich
  • 4,946
  • 2
  • 15
  • 8
Gowri
  • 1,323
  • 9
  • 11
  • I think this is a better and simpler approach, since it allows the tread to "clean itself", versus having an outside thread do it (which always involves a new set of issues). – Faustas Sep 04 '14 at 15:14
8

Check out Statement.cancel().

james
  • 244
  • 1
  • 1
-5

No, you can't abort it using standard JDBC.

You might try to check if your particular RDBMS define some extension to suppot it.

Marko
  • 30,263
  • 18
  • 74
  • 108
  • 4
    I've had vendors tell me this. If a particular JDBC driver doesn't implement Statement.cancel(), that's a quality-of-implementation problem. It's there and documented for that use case. – John M Nov 17 '08 at 22:40