Debugging Connection leaks

One of the common problems that we uncover during performance testing is JDBC Connection leak. When a J2EE project is using normal JDBC code instead of OR-Mapping framework such Hibernate or Spring there is a good chance that they run into issues that connection was opened but not closed so there is a connection leak.

The code works fine in development environment but in performance environment after some time you start seeing this error Timed Out waiting for connection. This shows problem that some part of your business logic is trying to open a database connection but it is not able to get it.[7/2/09 9:53:21:411 PDT] 00000085 AdapterUtil > toSQLException Entry com.ibm.websphere.ce.j2c.ConnectionWaitTimeoutException: Connection not available, Timed out waiting for 180004 at com.ibm.ejs.j2c.FreePool.createOrWaitForConnection(FreePool.java:1470) at com.ibm.ejs.j2c.PoolManager.reserve(PoolManager.java:2141) at com.ibm.ejs.j2c.ConnectionManager.allocateMCWrapper(ConnectionManager.java:843) at com.ibm.ejs.j2c.ConnectionManager.allocateConnection(ConnectionManager.java:582) at com.ibm.ws.rsadapter.jdbc.WSJdbcDataSource.getConnection(WSJdbcDataSource.java:431) at com.ibm.ws.rsadapter.jdbc.WSJdbcDataSource.getConnection(WSJdbcDataSource.java:400) at com.wpcertification.jdbc.ConnectionLeakServlet.doGet(ConnectionLeakServlet.java:41) at javax.servlet.http.HttpServlet.service(HttpServlet.java:743) at javax.servlet.http.HttpServlet.service(HttpServlet.java:856) at com.ibm.ws.webcontainer.servlet.ServletWrapper.service(ServletWrapper.java:966) at com.ibm.ws.webcontainer.servlet.ServletWrapper.handleRequest(ServletWrapper.java:478) at com.ibm.ws.wswebcontainer.servlet.ServletWrapper.handleRequest(ServletWrapper.java:463) at com.ibm.ws.webcontainer.webapp.WebApp.handleRequest(WebApp.java:3107) at com.ibm.ws.webcontainer.webapp.WebGroup.handleRequest(WebGroup.java:238) at com.ibm.ws.webcontainer.WebContainer.handleRequest(WebContainer.java:811) at com.ibm.ws.wswebcontainer.WebContainer.handleRequest(WebContainer.java:1425) at com.ibm.ws.webcontainer.channel.WCChannelLink.ready(WCChannelLink.java:92) at com.ibm.ws.http.channel.inbound.impl.HttpInboundLink.handleDiscrimination(HttpInboundLink.java:465) at com.ibm.ws.http.channel.inbound.impl.HttpInboundLink.handleNewInformation(HttpInboundLink.java:394) at com.ibm.ws.http.channel.inbound.impl.HttpInboundLink.ready(HttpInboundLink.java:274) at com.ibm.ws.tcp.channel.impl.NewConnectionInitialReadCallback.sendToDiscriminators(NewConnectionInitialReadCallback.java:214) at com.ibm.ws.tcp.channel.impl.NewConnectionInitialReadCallback.complete(NewConnectionInitialReadCallback.java:113) at com.ibm.ws.tcp.channel.impl.AioReadCompletionListener.futureCompleted(AioReadCompletionListener.java:152) at com.ibm.io.async.AbstractAsyncFuture.invokeCallback(AbstractAsyncFuture.java:213) at com.ibm.io.async.AbstractAsyncFuture.fireCompletionActions(AbstractAsyncFuture.java:195) at com.ibm.io.async.AsyncFuture.completed(AsyncFuture.java:136) at com.ibm.io.async.ResultHandler.complete(ResultHandler.java:193) at com.ibm.io.async.ResultHandler.runEventProcessingLoop(ResultHandler.java:725) at com.ibm.io.async.ResultHandler$2.run(ResultHandler.java:847) at com.ibm.ws.util.ThreadPool$Worker.run(ThreadPool.java:1498)

The IBM Support Assistant tool provides "Database connection pool analyzer" that you can use to debug the connection pool problems. Follow these steps to work with the tool.

First enable the JDBC Connection pool related trace by enabling trace for the following trace stringWAS.database=finest:WAS.j2c=finest:RRA=finest

Run your load test case and let it generate the com.ibm.websphere.ce.j2c.ConnectionWaitTimeoutException: error.

Start the IBM Support assistant and then go to Launch Activity -< Analyzer Problem -< Tools.

Select the Database Connection Pool Analyzer for IBM WebSPhere Application Server and click on launch button. It will ask you for name of the trace file, so select the trace file from your app server that has Connection pool related trace.

It will open the tool with information on all the data sources in your application server and show how each of them is doing, things like no. of connections used,...

In my environment i have only one data source /jdbc/sampleDB, select it and it will show usage graph for that connection along with the MVC wrapper information.

Now select one of the Never Released messages and it will display the stack trace of the connection was never released. Did you notice that it is displaying the stack of calls that were made to get that connection. Now i know that the connection that i am getting in com.wpcertification.jdbc.ConnectionLeakServlet.doGet(ConnectionLeakServlet.java:41) is never released, so i can make changes to fix that code.