Resolution

You can use query timeout to limit JDBC query time.

• When using transaction, set <query-timeout> to a number of seconds in your datasource configuration. For example:

  <query-timeout>300</query-timeout>
  

  If <query-timeout> is not configured, it defaults to 0 (no timeout)

  If you want to sync query timeout with transaction timeout, you can set <set-tx-query-timeout> to true in your datasource configuration. See also https://access.redhat.com/site/solutions/27591.
  Please note that <set-tx-query-timeout> is not functional in JBoss EAP 4.x. It is functional since JBoss EAP 5.

• When not using transactions, set a per-query limit by using Statement.setQueryTimeout(int) programmatically in your application.

JBoss will not know if the database is deadlocked, running a long query, etc. It relies on JDBC driver's function. But it will provide a means of setting a time limit and seeing exceptions.

Just for reference, please also refer to Oracle® Database JDBC Developer's Guide and Reference for the details of limitation of Statement.setQueryTimeout. It says:

Using statement.cancel

The JDBC standard method Statement.cancel attempts to cleanly stop the execution of a SQL statement by sending a message to the database. In response, the database stops execution and replies with an error message. The Java thread that invoked Statement.execute waits on the server, and continues execution only when it receives the error reply message invoked by the other thread's call to Statement.cancel.

As a result, Statement.cancel relies on the correct functioning of the network and the database. If either the network connection is broken or the database server is hung, the client does not receive the error reply to the cancel message. Frequently, when the server process dies, JDBC receives an IOException that frees the thread that invoked Statement.execute. In some circumstances, the server is hung, but JDBC does not receive an IOException. Statement.cancel does not free the thread that initiated the Statement.execute.

When JDBC does not receive an IOException, Oracle Net may eventually time out and close the connection. This causes an IOException and frees the thread. This process can take many minutes. For information about how to control this time-out, see the description of the readTimeout property for OracleDatasource.setConnectionProperties. You can also tune this time-out with certain Oracle Net settings. See the Oracle Database Net Services Administrator's Guide for more information.

The JDBC standard method Statement.setQueryTimeout relies on Statement.cancel. If execution continues longer than the specified time-out interval, then the monitor thread calls Statement.cancel. This is subject to all the same limitations described previously. As a result, there are cases when the time-out does not free the thread that invoked Statement.execute.

The length of time between execution and cancellation is not precise. This interval is no less than the specified time-out interval but can be several seconds longer. If the application has active threads running at high priority, then the interval can be arbitrarily longer. The monitor thread runs at high priority, but other high priority threads may keep it from running indefinitely. Note that the monitor thread is started only if there are statements executed with non zero time-out. There is only one monitor thread that monitors all Oracle JDBC statement execution.

Statement.cancel and Statement.setQueryTimeout are not supported in the server-side internal driver. The server-side internal driver runs in the single-threaded server process; the Oracle JVM implements Java threads within this single-threaded process. If the server-side internal driver is executing a SQL statement, then no Java thread can call Statement.cancel. This also applies to the Oracle JDBC monitor thread.

Please also note that PostgreSQL JDBC driver 8.x/9.0/9.1 does not support Statement.setQueryTimeout therefore you can not use query timeout for PostgreSQL database. In version 9.2, it seems be supported. Please refer to https://access.redhat.com/site/solutions/66525 for details.

• It may be possible to leverage vendor specific settings such as the below, however, application code must be designed to catch exceptions arising from timeouts which originate in the underlying database/driver and connection validation should be enabled. See also How to set connection properties for an XA Datasource on JBoss.

<local-tx-datasource>
    ...
    <connection-property name="oracle.net.CONNECT_TIMEOUT">5000</connection-property>
    <connection-property name="oracle.jdbc.ReadTimeout">30000</connection-property>
</local-tx-datasource>