Caused by: com.ibm.websphere.ce.j2c.ConnectionWaitTimeoutException: Connection not available, Timed out waiting for 180008

Question

I am running my multithreaded Struts application on IBM WAS 7.0 and getting the below exception during peak loads. Initially I configured max connections as 50 and connection wait time as 180 sec , but when I got this error I changed max connections to 70 and connection wait time as 600 sec after doing a lot of googling to fix this issue, but still getting this error sometimes during peak loads....

Caused by: com.ibm.websphere.ce.j2c.ConnectionWaitTimeoutException: Connection not available, Timed out waiting for 180008

Please can anyone help me on that.

Answer 1

Thanks for the clarification! The message you are seeing normally indicates that the connection pool has maxed out, and there are many reasons why this can occur.

If you are still seeing the issue after increasing the max connection pool size, you may have a connection leak issue. Often times, if the application is not properly closing out connections, it will fill up the connection pool no matter how high you increase the max connection size. A good indication of a connection leak is if the connection pool does not recover on its own and the server needs to be restarted. The application should be following a get / use / close pattern in order to avoid a connection leak issue.

Another common cause of the connection pool maxing out is if there are performance issues on the database side. If the database is not configured properly to handle the load from WAS, it can lead to connection pool issues. If there are long running queries, this can cause connections to stay open longer on the WAS side as well.

In addition, if your application is not specifically designed to use shared connections and the WAS connection pool is using shared connections, it could contribute to the connection pool maxing out. By default, the WAS connection pool is set to shared. If your application is not specifically designed to take advantage of shared connections, it may not be necessary to use shared connections. You can try to change to unshared connections to see if it makes a difference. In order for this to help, your application has to be calling close on the connection. Changing the connections to unshared can be done by setting the globalConnectionTypeOverride custom property to unshared. This can be done by following the instructions on the link below ('Control connection pool sharing' section):

http://www14.software.ibm.com/webapp/wsbroker/redirect?version=matt&product=was-nd-dist&topic=tdat_conpoolman

This link states the following:

* Control connection pool sharing.
  You can use the defaultConnectionTypeOverride or globalConnectionTypeOverride connection pool custom property for a particular connection factory or data source to control connection sharing:
      o The defaultConnectionTypeOverride property changes the default sharing value for a connection pool. This property enables you to control connection sharing for direct queries. If resource references are configured for this data source or connection factory the resource reference's configurations take precedence over the defaultConnectionTypeOverride property settings. For example, if an application is doing direct queries and unshared connections are needed, set the defaultConnectionTypeOverride property to unshared.
      o The value specified for the globalConnectionTypeOverride custom property takes precedence over all of the other connection sharing settings. For example if you set this property to unshared, all connection requests are unshared for both direct queries and resource reference lookups. This property provides you with a quick way to test the consequences of moving all connections for a particular data source or connection factory to unshared or shared without changing any resource reference setting.

  If you specify values for both the defaultConnectionTypeOverride and the globalConnectionTypeOverride properties, only the values specified for the globalConnectionTypeOverride property are used to determine connection sharing type.
  To add these new custom properties to the settings for a data source or connection factory connection pool, a new connection pool custom property must be created. To add one of these properties to a data source, use the administrative console. Click Resources > JDBC > Data sources > select your data source from the list Additional properties > Connection pool properties > Connection pool custom properties > New. For other J2C or JMS connection factories, navigate to the connection factory definition in the administrative console. Then select Additional Properties > Connection pool > Connection pool custom properties > New. Now specify defaultConnectionTypeOverride or globalConnectionTypeOverride in the Name field and shared or unshared in the Value field.
  Important: The properties must be set in the Connection pool custom properties and not the general Custom propeties on the data source or connection factory.

You can also review the following link for more information on these properties:

http://www14.software.ibm.com/webapp/wsbroker/redirect?version=matt&product=was-nd-dist&topic=rdat_conpoolcustprops

The following link provides some background information on Unshared vs Shared connections:

http://www14.software.ibm.com/webapp/wsbroker/redirect?version=matt&product=was-nd-dist&topic=conshrnon

Answer 2

There may be a couple of different issues that could cause the Connection Timeout value to not take effect.

You will want to first check the resources.xml file at the scope where your datasource is configured. Make sure that the Connection Timeout value is really set to 600. WAS will use this config file to set the connection pool values when the server (JVM) is restarted. You should see something like the following for your datasource:

<connectionPool xmi:id="ConnectionPool_10000025" connectionTimeout="180" maxConnections="10" minConnections="1" reapTime="180" unusedTimeout="1800" agedTimeout="0" purgePolicy="EntirePool" numberOfSharedPoolPartitions="0" numberOfUnsharedPoolPartitions="0" numberOfFreePoolPartitions="0" freePoolDistributionTableSize="0" surgeThreshold="-1" surgeCreationInterval="0" testConnection="false" testConnectionInterval="0" stuckTimerTime="0" stuckTime="0" stuckThreshold="0"/>

If the value is not set to 600 here, that explains why the connections are still timing out after 180 seconds. You should also be able to verify what the max number of connections is set to. You would want to use the admin console or wsadmin to make the changes again and restart the server to verify if the changes take effect. It is not normally recommended to change these values manually.

If the value is set to 600, you will want to check that a datasource with the same name is not configured at multiple scopes. If it is configured at multiple scopes, you want to make sure the values are changed for all the scopes. The application server will use the connection pool settings for the datasource configured at the lowest scope. If you have the datasource configured at both the server and cluster scope, the server scoped datasource will be used. From lowest scope to highest scope, the order is: Server < Cluster < Node < Cell.

If you don't have the same datasource configured at multiple scopes and the value is set to 600, there could be some other issue when the server is being started that is not allowing the datasource configuration to be picked up correctly.