The purpose of this article is to highlight the root cause of JDBC related performance issues that I have seen in multiple J2EE applications using struts framework. In many cases the root cause is the use of "shareable" connections, which is the default configuration in WebSphere.
The difference between shareable and unshareable connection is very nicely described in the following article.
http://www.ibm.com/developerworks/websphere/library/techarticles/0506_johnsen/0506_johnsen.html
Quoting from the above mentioned article an important point to note is that: -
"When the application closes a shareable connection, the connection is not truly closed, nor is it returned to the free pool. Rather, it remains in the Shared connection pool, ready for another request within the same LTC for a connection to the same resource."
This has serious impact if we are not using global or JTA transactions in our struts application. In that situation, WebSphere will create an LTC for each action forward. Let us take the following example of a simple search-results use case: -
1. A search request comes to the SearchAction.
2. SearchAction invokes "SearchHistoryDAO" to save the search parameters.
2.a) SearchHistoryDAO retrieves a connection from the pool, executes an insert and returns the connection back to the pool. (This takes 20ms)
2.b) SearchAction forwards the control to "SearchReultsAction"
3. SearchResultsAction invokes the search web service to perform the search and get the results. (This takes 3 seconds)
4. SearchResultsAction finally forwards the control to results.jsp to display the results to the user.
5. Control returns to SearchResultsAction
6. Control returns to SearchAction
In the above use case, if the connections are unshareable, then the connection fetched from the pool in step 2.a will be returned to the pool in around 20 ms in the same step. Thus it will be available for other threads to use in no time. "Connection use time" in this case will be 20 ms.
However, if the connections are shareable then the connection, even if closed will not be returned to the pool until the LTC of SearchAction is completed. That happens in step 6. So, the connection will not be available for other threads for around 3 seconds. The "connection use time" in this case will be more than 3 seconds.
This severely limits the number of connections available in the pool at any given time and does not allow the application to scale, maxing out the connection pool. With this configuration, there are many connections doing nothing while other threads (or the same one) wait for a connection.
This problem is compounded if each action in the chain performs a DB operation, and we have 3 or more actions in a chain. In this scenario, each HTTP request will require more than 1 simultaneous connection to process the request.
This issue manifests in the following way when "shareable" connections are used:-
1. Hung threads & deadlocks in Websphere
This situation occurs if the "ConnectionWaitTimeOut" parameter is left to the default in WebSphere and more than 1 action in an action chain requests a DB connection.
This parameter defines the time a thread will wait to obtain a connection from the pool before timing out, and the default is half an hour. Let us take an example where there are 10 connections in a pool. Now, if we send 10 simultaneous requests to the application server, each request will grab one connection and exhaust the pool. Then each action in the thread forwards the control to the next action in chain. That action will try to get a connection but none is available and so it will wait for half an hour to get it. However, no one will release it because it is held in the LTC of the previous action which will complete only when the current action waiting for the connection completes causing a deadlock.
2. JDBC Connection Wait Timeout exceptions
Incase the default value is changed from 30 minutes to a more reasonable 5-10 seconds, then deadlocks will not happen. In this case, at least one transaction will fail with a “ConnectionWaitTimeoutException” and deadlock will not happen.
3. Non-existent JDBC connection leaks
As described above, with shareable connections under load conditions the connection pool generally maxes out and we may start getting SQLException with the error " Unable to get a PooledConnection from the DataSource". This exception generally indicates that either the pool is too small, or there is a connection leak. Assuming that the pool size is set to a reasonable number, many times developers believe that this might be happening because of a connection leak and start reviewing code to find that "leak" wasting a lot of time and resources when there is no leak at all.
If you are facing any of the above mentioned issues, then it is highly recommended that you switch to the unshareable connections by configuring the same in web.xml as shown below.
<resource-ref>
<description>Database</description>
<res-ref-name>java:comp/env/jdbc/MyDS</res-ref-name>
<res-type>javax.sql.DataSource</res-type>
<res-auth>Container</res-auth>
<res-sharing-scope>Unshareable</res-sharing-scope>
</resource-ref>
