Tracing Database Connection Pool Leaks

Connection pools are pretty common when you want to connection a Java application to a database. Rather than each thread opening a dedicated connection to the database connections are held in a centralized pool. This has a couple of big advantages:

1. If the connections are kept open it obviates the overhead of setting up/tearing down a TCP/IP connection and authenticating the connection with the database
2. If the connection is acquired for the period when the thread wants to communicate with the database the application can support many more threads than database connections. For example if there are 50 connections and each thread only uses the database for 20% of its execution time then, in theory, it can support 250 simultaneous users.

If connections are not returned to the connection pool they will be kept open and the available connections will leak away. At some point Threads will either block waiting for connections to be available or will generate an exception, perhaps after a few retries. Some pool's for example Oracle's Datasource (ojdbc) have options to close connections after a certain time (Abandoned Connection Timeout property) but this is palliative.

There are a number of reasons why connections don't get returned

1. The programmer simple forgot to return the connection
2. There is a deadlock, the thread is waiting on some resource having first acquired a connection is blocked by other threads in a similar predicament
3. An Exception has occurred and the code to close the connection has been skipped.

These conditions can all be hard to find. One solution is to trace who is acquiring and release locks. In Java the java.lang.Thowable class can be used to programmatically obtain a stack trace. If this is done in the get connection method then a record can be kept of who is acquiring connections. When the pool is exhausted this list can be printed and the code investigated for leaks.

The class TraceConnection declares a ConcurrentHashMap to store connection/stack trace pairs.

public class TraceConnection {
	int counter = 0;
	private ConcurrentHashMap<Connection, Throwable> connectionMap = new ConcurrentHashMap<Connection, Throwable>();

AddConnection increments a counter of the total connections allocated and records the stack trace of the callee. This method should be called whenever you ask the pool for a connection. If any of your code by-passes this method (for example third party packages like OSWorkflow) then the counter will not record the correct value. The Apache DBCP pool provides a method that will tell you how many connections are in use and this should be used in preference.

public void addConnection( Connection c ) {
  counter++;
  connectionMap.putIfAbsent( c, new Throwable() );
}

Remove a connection from the list, called whenever you release a connection.

public void removeConnection( Connection c ) {
  counter--;
  connectionMap.remove( c );
}

PrintOpenConnections should be called whenever you run out of free connections. It will print stack traces of all the threads that have opened connections to a depth specified by the parameter (this will be set to a figure around 5 or 6 and prevents too much stack information from being printed.

public void printOpenConnections(int depth) {
  int i = 1;
 
  log.debug("THE FOLLOWING CONNECTIONS ARE CURRENTLY OPEN: ");
  for (Iterator iter = map.iterator(); iter.hasNext();)
    Map.Entry entry = (Map.Entry)iter.next();
    log.debug("[" + i++ + "]:");
    printCallers(entry.getValue(), depth);
  }
}
 
private void printCallers(Throwable ex, int l) {
  if (null == ex) {
    log.debug("Null stack trace reference! Bailing...");
    return;
  }
 
  StackTraceElement[] stackElements = ex.getStackTrace();
  if (stackElements.length < l) {
    return;
  }
 
  for (int i = 1; i < l; i++) {
    log.debug(" " + stackElements[i].getLineNumber() + " in "
      + stackElements[i].getClassName() + "."
      + stackElements[i].getMethodName() + "()");
  }
}