Problem Statement:
One of the application is showing the below error message while accessing the application. The application is raising this bug for every three or four months.
Ok, what is the bug here?
Cannot get a connection, pool exhausted error.
How the problem was identified?
First of all, I came to know that the problem is related to connection pooling, by observing the above specified error message details in log file. I wanted to replicate the problem to identify the origin of the bug. Then, I have gone through the project to verify the settings related to connection pooling. I have identified an entry maxActive related to Connection Pooling as part of DB configuration settings. The entry specifies the maximum number of connections that are available in connection pool. To identify the bug, I have minimized its value from 200 to 5. Then, I have tested the each and every operation at least 6 times. Out of all operations, I found the above error at one operation. The operation is throwing error as the operation is not getting an open connection. So, I felt that the operation might be missed the close_connection statement.
Then, I have gone through the code in that specific flow. I found the closeConnection statement, but, it is referring the class/global variable. In fact, the usage of class/global variable is very less in that java program. Then, I removed the class/global variable and created the local variables when ever required.
Solution provided:
Quick solution provided:
*) Restart the web/application server. [and/or]
*) Enable removeAbandoned flag which is related to Connection Pooling as part of DB configuration settings.
<!-- To configure a DBCP DataSource so that abandoned dB connections are removed and recycled add the following paramater to the ResourceParams configuration for your DBCP DataSource Resource: -->
<parameter>
<name>removeAbandoned</name>
<value>true</value>
</parameter>
<!--Use the removeAbandonedTimeout parameter to set the number of seconds a dB connection has been idle before it is considered abandoned. -->
<parameter>
<name>removeAbandonedTimeout</name>
<value>60</value>
</parameter>
Suggested solution:
Removed the class/global variable and created the local variable.
