Connection Pool Hangs in createOrWaitForConnection

This recipe provides 3 possible strategies for dealing with connection pool hangs.

Strategy 1: Increase connection pool size maximum to 2x+1 (x = thread pool size maximum)

When an application is using multiple, simultaneous connections in the same thread, ensure the connection pool size is at least one more than the maximum number of threads so that the threads should never run out of available connections in the pool.

If the application opens 3 or more simultaneous connections you may have to experiment and try 3x+1 or 4x+1 as necessary.

Monitor

From the command line execute the above command periodically to capture the number of open connections to the database port number on the same node the application server(s) are running on.

netstat -an | grep ESTABLISHED | grep <port#> | wc -l
Caveat

This increases the number of overall database connections from each individual application server. Make sure the database is configured and capable of handling the total number of connections for the sum of all JVMs.

Strategy 2: Disable "shareable" connections

Test setting globalConnectionTypeOverride=unshared to disable shareable connections.

Monitor

Check application and SystemOut.logs to see if any unexpected exceptions or logic errors occur.

Caveat

This will cause application problems for applications using container managed EJBs. Typically this strategy works for Web Container applications accessing databases directly through JDBC.

Strategy 3: Fix the application code

The previous two strategies are operational - run time changes to try to deal with an application that uses multiple simultaneous connections in the same thread request execution. The previous two strategies may not be operationally possible due to resource limitations in the environment. In this case the only way to fix the problem is to fix the application code to never have more than one connection open at a time within the same thread request execution.

Monitor

Javacore files to ensure that there are no threads stuck in createOrWaitForConnection.

Caveat

This may require extensive re-design of the application code and can be a time consuming fix.