Working with database threads in an embedded environment

As a rule, do not use Thread.interrupt() calls to signal possibly waiting threads that are also accessing a database, because Derby may catch the interrupt and close the connection to the database. Use wait and notify calls instead.

There are also special considerations when working with more than one database thread in an application, as described in Working with multiple threads sharing a single connection.

When queries, batches, and statements that wait for database locks run longer than expected, you can use interrupts to stop them. If you do, the connection will be closed and an exception will be thrown.

If you design an application whose database threads may see interrupts, you should plan for the following behavior:

• If a thread is interrupted and the interrupt status flag is not cleared before entering a Derby JDBC call, or if the thread is interrupted while inside a Derby JDBC call, the connection that is experiencing the interrupt will be terminated in the following situations:
  • If a query fetches rows from a database table after the interrupt has occurred
  • If the execution of a new element in a batched statement is attempted after the interrupt has occurred
  • If an interrupt is received while a transaction is waiting for a lock

  If the connection is terminated, the application thread will experience the following consequences:

  • The JDBC call will raise an SQLException with state "08000" ("Connection closed by unknown interrupt").
  • Outstanding transactional work on that connection will be rolled back, and all of its locks will be released.
  • The connection cannot be used to execute any further JDBC calls.

  On return from the JDBC call, the Thread.isInterrupted() method of the thread will return true, whether or not an exception terminating the connection was thrown. That is, even if Derby does not heed an interrupt, the flag will remain set on exit from the JDBC call.

• All other connections will remain open. This includes other connections which the interrupted thread may be using. These connections will remain open at least until the thread tries to use one of its other connections. If the thead has neglected to clear its interrupted status flag, this connection is also subject to termination as described above.
• The application should normally be prepared to catch the 08000 exceptions, discard the dead connection, clear the interrupted status of the thread, and then restart the transaction in a new connection.