What is the difference beween these settings?

Statement timeout vs. statement leak timeout vs. connection timeout in JDBC?

In a nutshell:

  • Statement timeout must be less than statement leak timeout.
  • Statement leak timeout must be less than connection timeout.

Example from GlassFish:

From the GF documentation:

http://docs.oracle.com/cd/E18930_01/html/821-2418/giyck.html#ghqrx

An abnormally long running JDBC query executed by an application may leave it in a hanging state unless a timeout is explicitly set on the statement. Setting a statement timeout guarantees that all queries automatically time out if not completed within the specified period. When statements are created, the queryTimeout is set according to the statement timeout setting. This works only when the underlying JDBC driver supports queryTimeout for Statement, PreparedStatement, CallableStatement, and ResultSet.

If statements are not closed by an application after use, it is possible for the application to run out of cursors.

When selecting a value for Statement Leak Timeout In Seconds, make sure that:

  • It is less than the Connection Leak Timeout; otherwise, the connection could be closed before the statement leak is recognized.
  • It is greater than the Statement Timeout; otherwise, a long running query could be mistaken as a statement leak.