What does J2CA0045E mean for WebSphere Application Server?

Body

I wanted to take some time to explain error "J2CA0045E: Connection not available while invoking method createOrWaitForConnection (ConnectionWaitTimeoutException)." This exception occurs when your JDBC connection pool has reached the maximum number of database connections you allow, and subsequent database connection request from your application cannot be serviced before your datasource configured connection timeout value expires.

When you configure a WebSphere Application Server managed datasource, you set two parameters that can directly affect this condition, the total number of connections you will allow the datasource to open (MAX connections), and in a case where all available connections are already active or in use, how long you will wait for one to complete and become available to service a new connection request (Connection Timeout).

In many cases, the default MAX connections value of 10 is simply too small to keep up with application demands, and the error can be eliminated by increasing the MAX connections allowed. Note that the WebSphere Application Server does not open database connections that your application does not request, so configuring a MAX value of 50 does not mean you are automatically going to generate 50 database connections, this is the number that you will allow to accumulate per your application. For example, if your application typically utilizes 20 database connections, then regardless of a MAX value of 25 or 250, you will still only average 20 open connections. However, you should also be aware of any maximum connection restrictions set at your database server. You never want your WebSphere Application Server to attempt to exceed the number of connections that your database server will allow.

If raising the MAX connection parameter on your failing datasource does not resolve this exception, or the MAX is already beyond what you consider a reasonable number, then chances are that your application is not properly utilizing the connection type specified. The WebSphere Application Server provides for two types of database connections, shared connections (default), and unshared. The basic difference between these types is that a shared connection, once obtained, will remain associated to the transaction that obtained it for the life of the transaction. This means that even if the application issues a close connection, the connection remains in the active connection pool, owned by the transaction, and will not be returned to the free pool for reuse until the transaction ends. This is for performance reasons, so that should that transaction require another connection, it does not have to go through the overhead of creating or obtaining a connection again.

With unshared connections, the database connection will be returned to the free pool, available for reuse, when the application makes the connection close request, regardless of the transaction state. If your local transactions are not making multiple connection request on the datasource, then there is no performance gain by using shared connections. In fact, the obverse could be the case. If your transactions are long running or suspending to do other work, you are needlessly holding on to database connections that could otherwise be returned to the free pool to be reused. So staying with the default connection type shared can adversely affect performance and be the cause of the ConnectionWaitTimeoutExceptions if your application was not specifically designed to utilize them properly or even require them.

This condition is quite easy to see in a WebSphere Application server diagnostic trace (*=info:WAS.j2c=all:RRA=all). Every time there is a change in connection status, a new snapshot of a connection pool is written to the trace. If your active pool shows database connections with handle counts = 0, this indicates the application has already issued a close connection, but the connection remains in the active pool waiting for transaction end. In that same pool snapshot, if you observe a "waiter" count, this is the number of connection request that your application has made that are waiting to be serviced, and if not serviced before the connection timer pops, will result in ConnectionWaitTimeoutExceptions.

[4/8/14 16:24:51:151 EST] 0000002e PoolManager   3   reserve(), Pool contents ==> PoolManager name:jdbc/MyDatasource
PoolManager object:1811246069
Total number of connections: 2 (max/min 2/0, reap/unused/aged 180/1800/0, connectiontimeout/purge 180/EntirePool)
              (testConnection/interval false/0, stuck timer/time/threshold 0/0/0, surge time/connections 0/-1)
The waiter count is 4
Shared Connection information (shared partitions 200)
    com.ibm.ws.Transaction.JTA.TransactionImpl@70217021#tid=3686  ...State:STATE_TRAN_WRAPPER_INUSE Thread Id: 0000002e Handle count 0
    com.ibm.ws.Transaction.JTA.TransactionImpl@4f8f4f8f#tid=3687       ...State:STATE_TRAN_WRAPPER_INUSE Thread Id: 0000004b Handle count 0
  Total number of connection in shared pool: 2

Free Connection information (free distribution table/partitions 25/1)
  No free connections

UnShared Connection information
  No unshared connections

There is an article written by one of our Staff Software Engineers in IBM WebSphere Application Server Connection Management that discusses the WebSphere Application Server connection behavior in some detail here: Default behavior of managed connections in WebSphere Application Server

Switching connection types is quite easy thanks to a custom property introduced by development in APAR PK75717.

To use this feature, create a new custom property in your datasource configuration, globalConnectionTypeOverride, and set the value to unshared. The details of the APAR can be seen here: PK75717: ADD CUSTOM PROPERTIES TO CONTROL CONNECTION SHARING

NOTE: If you do not see shared connections with handle counts = 0, then the application has not closed the connection(s) and switching to unshared connections will not resolve the problem. A handle count > 0 indicates that either the application is still using the connection or neglected to close it.

title image (modified) credit: (cc) Some rights reserved by geralt