Wednesday, August 19, 2009

Interruptible JDBC Statements

I work for a client on a product that makes direct queries against databases via JDBC. A while ago, I added some code so that a user could stop the execution of a series of queries by clicking on a cancel button. Behind the scenes, it interrupts the thread executing the queries. Since that thread checks whether it's been interrupted by calling Thread.currentThread().isInterrupted() before starting to execute each query, no new queries will start once the cancel button was pushed. However, any query that's already started will run to completion before the code discovers that a request to cancel had been made. Recently, my client decided that we should make cancellation more granular and add the ability to stop a query in mid-stream. Looking at the JDBC docs, there's nothing to indicate that any of the relevant methods respond to interrupts (none throw InterruptedExceptions or claim to wrap them in a SQLException), so I had to come up with another way. I settled on an approach where I submit the query as a Callable to an ExecutorService and then block, waiting for the result via Future.get(). If the thread is interrupted while we're waiting, get() throws an InterruptedException, which gives us the chance to call Statement.cancel(). It's pretty simple and works nicely. :)
Here's the code in a somewhat abridged/condensed form (I create the ExecutorService elsewhere using Executors.newCachedThreadPool()):
final String sql = "...<some SQL>...";
final Statement statement = conn.createStatement();
Future<ResultSet> queryFuture =
 execService.submit(new Callable<ResultSet>() {
   @Override
   public ResultSet call() throws Exception {
     statement.execute(sql);
     return statement.getResultSet();
   }
 }
);

ResultSet rs;
try {
 rs = queryFuture.get();
} catch (InterruptedException e) {
 logger.info("Query interrupted - calling Statement.cancel()");
 statement.cancel();
 throw e;
} catch (ExecutionException e) {
 //code to handle or rethrow the exception
}
By the way, if any of the java.util.concurrent classes above are unfamiliar to you, I highly recommend Java Concurrency in Practice by Brian Goetz (et al.). It's an excellent book.

13 comments:

  1. Cute. I've wondered about how to do this nicely in the past.

    ReplyDelete
  2. Cool, now get that into Apache iBatis 3 :)

    ReplyDelete
  3. @Luke - glad you liked it!

    @Claus - now that iBATIS requires Java 5, that's an option without including the Doug Lea util.concurrent libraries. I haven't used iBATIS in years! How's it doing? (The product I refer to in the post might benefit from using it)

    ReplyDelete
  4. That's really neat. I actually had almost the same problem yesterday (JDBC statements blocking on a socket while waiting for a database event to occur). While I already have a solution, I'll try yours too - it looks much better than mine.

    Thanks,
    Stefan

    ReplyDelete
  5. Thanks Stefan. I hope my approach turns out to be as useful in your scenario as it was in mine. :)

    ReplyDelete
  6. @Matt - unfortunately it doesn't work in my case. The thread is blocked on a SocketInputStream, thus it is not interruptible :(

    ReplyDelete
  7. @Stefan - that's a shame. By the way, I didn't try to describe it in the post, but I'm also using a variant of this technique in an asynchronous setting. My main UI thread shouldn't block, so it creates/submits a Callable which in turn creates/submits the Callable that actually executes the query. It sounds complicated, but really isn't too bad.

    I'd be happy to provide me more details. If you're interested, please contact me at mpassell (at) grovehillsoftware [dot] com.

    ReplyDelete
  8. @Matt - hhm, maybe I'm dumb but I can't see how that would solve my problem. It's the JDBC driver who blocks on the socket waiting for either a database event to occur or to timeout after some specified timespan, 5 minutes say. It doesn't matter how cleverly the thread that runs this JDBC call is created because it won't respond to Thread.interrupt() calls. I want to take this thread down when I'm stopping my web app, that's the problem I attempted to solve cleanly with your technique. I see no way this could be done by layering Callables over Callables?

    ReplyDelete
  9. @Stefan - yeah, sorry. I guess I was responding to a different scenario from the one you were describing. Out of curiosity, I experimented with closing a Socket and/or its InputStream from another thread while the main thread was blocked on a read. I had mixed success with causing an IOException to occur (unblocking the thread). It seemed to work if I was reading directly from Socket.getInputStream(), but if I wrapped that in a BufferedInputStream or BufferedReader, calling close() didn't seem to have any effect. Have you tried any approaches along those lines?

    ReplyDelete
  10. @Matt - he, he, closing the socket from another thread, I actually considered that. Apart from being really ugly it wouldn't be that easy. As I said, it's the JDBC driver that does all these things, I do nothing more than calling an Oracle stored procdure (from the dbms_alert package) through a CallableStatement. How would I get access to the specific socket that is opened by the JDBC driver? I'm not aware of any way to do that.

    However, it's not all bad. As the thread is blocking for a database event to occur, I simply take the "poison pill" approach and signal such an event (embedding a special "stop-message") from another thread. This way the listening thread unblocks, looks at the event message and shutdowns itself if it is the "stop-message". This seems to work cleanly so far.

    What I don't like about this approach:
    a) I need the help of (depend on) the DB to shutdown the thread
    b) Other listeners (outside my WebApp) that may also listen on the same event must know my special "stop-message", so that they can ignore it (and we have lots of C processes in our app that may choose to listen on any event any day)

    ReplyDelete
  11. @Stefan - Alright. Since getting more sleep and looking over all of your comments, I now understand how and where I got led astray.

    My approach in the post relies on Statement.cancel() doing its job. The whole reason that we're pushing the JDBC operations to a worker thread is so that we can interrupt the main thread, since JDBC operations are not interruptible. Once the main thread has been interrupted, the hope is that the executing Statements are cancellable. In your situation, I gather that the Statement effectively isn't because it's blocked on I/O and doesn't respond to cancellation. That certainly would be a difficult problem to solve directly without altering the driver itself.

    Although your poison pill approach may not be ideal, it may be the best available solution. At least since you involve the DB in the process, it has the chance to clean things up before severing the connection. That obviously wouldn't be the case with my hacky socket/InputStream idea (aside from whether it's even possible to access the socket). I hope updating the code for the C processes is a possibility. If they don't know how to speak your new poison pill protocol, that could be a real problem...

    By the way, when I first put up the post, a colleague of mine let me know he was dealing with some issues similar to yours. I'll let him know about this comment thread and ask him to chime in with any ideas he's had.

    ReplyDelete
  12. @Matt - I agree, you've hit the mark with your analysis.

    ReplyDelete
  13. Matt .. if multiple users have submitted a request to run queries, how do you identify which thread to be interrupted for a specific user ?

    ReplyDelete