Handling Connection Errors

A problem with pooled connections is errors on the underlying database connection can disrupt the operation of the pool or the application. The pool implementation provides mechanisms for reducing the impact of these errors.

Validate on Allocation

One issue that can disrupt the operation of an application is if the connection returned from the pool is actually unusable; for example, it may have been closed by the underlying driver due to a network problem. As a result, the first time the application tries to use the connection, the operation will fail, potentially leading to complete rollback of the transaction.

To avoid this, the connector can be configured to execute an SQL statement on the connection before returning it to the application. If this fails, then the connection is immediately discarded and the next one in the pool is considered.

To enable this check, use the element in the datasource configuration. As this is potentially executed for every connection allocation, this should be a low-overhead operation.

An example of this configuration for an Oracle database could be:

  
    MyOracleDS
    jdbc:oracle:thin:@host:1521:sid
    oracle.jdbc.driver.OracleDriver
    scott
    tiger
    SELECT 1 FROM SYS.DUAL
  

Detecting Fatal Exceptions

Certain SQLExceptions thrown by the JDBC driver indicate a fatal error condition under which all future operations on the Connection will fail. Further, even though such a SQLException may cause the current transaction to be rolled back, the Connection will be returned to the pool and may be returned again to the application.

Unfortunately, there is no standard way of telling which SQLExceptions indicate these fatal errors, and, further, the actual implementation varies between databases and even drivers. To allow the detection of these conditions, the JCA adaptor allows an Exception Sorter to be associated with the datasource. If a SQLException is thrown, then it will be passed to the Exception Sorter to determine if it should be considered fatal; is so, then the connection will immediately be discarded from the pool.

An Exception Sorter is an implementation of the interface:

package org.jboss.resource.adapter.jdbc;
public interface ExceptionSorter
{
    boolean isExceptionFatal(java.sql.SQLException e);

}

which is associated with a datasource using the configuration element. For example, for Oracle:

  
    MyOracleDS
    jdbc:oracle:thin:@host:1521:sid
    oracle.jdbc.driver.OracleDriver
    scott
    tiger
    
      org.jboss.resource.adapter.jdbc.vendor.OracleExceptionSorter
    
  

The following pre-configured implementations are available: