Putting timeouts on Prepared statements

Question

I am trying to use prepared statements which operate on a database located quite far away, there is considerable lag and unreliability involved in the network connection used to access this database. Downtimes of up to a minute are common. The problem is that in case of such a failure, if my program tries to execute any prepared statement, the whole thread goes into infinite wait. It never times out and just remains stuck, waiting for a response from the database.

I tried using the method setQueryTimeout() to explicitly put a timeout on the execution, but, seems there is some problem with this method wherein it cant work properly if the network fails.

Is there any alternative way around this ?

Answer 1

In my knowledge, there is no such alternative if the network itself fails.

The exact details of setQueryTimeout involve the JDBC driver being instructed to send an Out Of Band signal (atleast in the case of the Oracle JDBC driver) to the database to halt the execution of the Prepared Statement; this part is important, as it depends on the support built into the driver and the database. Following this, it is upto the database to schedule execution of this 'cancel' operation; this could take a while if things have to be rolled back or if other transactions have to be executed etc.

Given the first part of the nature of the implementation, it is quite unlikely that a "clean" implementation of a timeout feature can be established. You might want to investigate the use of a transaction manager here (JTA perhaps), but I'm not sure if will encounter another set of bizarre exceptions (think heuristic exceptions).

Addendum

Using a thread monitor that monitors the execution of other threads and kills the 'stuck' threads might be a bad idea. This SO question would help answer why such an activity should be avoided. This is also the tactic chosen by WebLogic Server.