How to force close a database connection in java before quit

Question

I have a multiple threads java app, and each thread could use database connection to query data. The app sometimes needs to restart by kill -1. But before the quit, we need to close all the connections. If one thread is busy working on a query, can we force close it in another threads?

Answer 1

The javadoc for Connection.close says:

"It is strongly recommended that an application explicitly commits or rolls back an active transaction prior to calling the close method. If the close method is called and there is an active transaction, the results are implementation-defined."

So simply calling close() on an active connection from another thread is a bad idea. Furthermore, it is unclear what happens to a thread whose transaction has be rolled back by another thread. I would expect it to get an SQLException, but it is unclear when that would happen.

Alternatives:

• @Basil Bourque suggests interrupting the thread that is using the connection. The problem is going to be identifying the correct thread to interrupt. It is also unclear what the JDBC driver will do, and whether the interrupted thread will deal with the consequences correctly.

• From Java 7 onwards, Connection has an abort(Executor) method that asynchronously closes the connection and releases its resources. The problem is that this could take a long time to complete. (The Executor is used to run a task that will do the work. Unfortunately, there is no standard way to tell when the task has completed.)

• If you have the Statement that is currently being executed, you could call cancel() on it. The javadoc says:

  "Cancels this Statement object if both the DBMS and driver support aborting an SQL statement. This method can be used by one thread to cancel a statement that is being executed by another thread.".

  If cancel is not supported, you get an exception.

---

In summary: this is probably something you would want to avoid having to do. But if it is essential that you do this, you will need to do some prototyping to figure out which approach works best for you application AND the database driver that you are using.

Answer 2

You asked:

can we force close it in another threads

No. Stopping threads is cooperative in Java.

Each thread carries an "interrupted" flag which can be set by another thread. Your code running inside that targeted thread must be written so as to check for that flag, and then react accordingly.

To set the flag, see Thread#interrupt. For threads managed by an executor service, see ExecutorService#shutdownNow.

To react, see Thread.interrupted and Thread#isInterrupted — one clears the flag while the other does not.

For example, in your code looping through a ResultSet, on every loop check for the interrupted flag.

while ( rs.next() ) {
    if( Thread.interrupted() ) { … handle interruption … }
    String sid = rs.getString( 1 ) ;
    String lid = rs.getString( 2 ) ;
    … do something with those values …
}

Search to learn more about stopping threads in Java.