How is Oracle's JDBC query timeout implemented?

Question

Hi,

I was curious as to how the Oralce JDBC thin client implement query timeout. This can be set by calling java.sql.Statement's setQueryTimeout(int seconds) method.

Is this implemented in the driver itself on the client side? Is a new thread spawned and joined? Or does the JDBC driver simply send a parameter to Oracle, and then it enforces the timeout?

After the timeout is reached, which resources on the client and database are released, and which hang around? Does Oracle continue to run the query even though the client abandoned it or is it terminated? Is there still a cursor object on the client side?

Thank you

Answer 1

I do know that the query does not continue on the server side when the timeout is reached. There is some intention/signal option sent to the server either before or after the timeout is reached to indicate it the server should stop. I have verified this by looking on the server in various V$ tables to see if the query is running. (V$SESSION, V$SQL, etc)

Answer 2

Tanel Poder wrote an article on how a Cancel works through the OCI (Oracle Call Interface). I guess something similar is done for JDBC. If you are using the thick driver, through OCI, you could try tracing the session (through settings sqlnet.ora) and see what gets recorded.

Answer 3

When a query actually timesout when using the setTimeOut method, a SQL exception with the Oracle error code ORA-01013 - user requested cancel of current operation is thrown from the oracle server.

This would mean that the operation has been cancelled gracefully (as far as oracle is concerned/as much oracle can) - because it is oracle sending this message.

Answer 4

According to Oracle JDBC FAQ

Statement timeout thread. This thread is created if you execute any statement with a timeout. Only one thread is created no matter how many statements or connections. This thread lasts the lifetime of the VM.