What method can be used for the investigation of Database Connection usage in Java?

database, java, jdbc, multithreading

Solution

Not a specific tool, but rather a debugging technique for tracking down which code is responsible for open connections or other resources.

I am assuming you are using a consistent method on the java side to get a db connection (pooled or not doesn't matter).

The idea is to create a very light wrapper class around your connection factory/pool or whatever it is. The wrapper will implement whatever jdbc interface makes sense so you can swap it in for your normal connection object but most methods will just transparently call/return the underlying connection. The simplest way would be to extend whatever your DBConnection class is and just override the specific methods you need to spy on (not forgetting to call `super.method()` where appropriate). Depending on your implementation you may need to override a factory as well as the connection.

If you are using some sort of IoC framework (e.g. spring) you should be able to easily swap out the connection/factory class at a config level. Now all your java code will be using your new db connection wrapper.

If you are using a pool, then calling `connection.close()` usually just returns the object to the pool instead of destroying the connection. So this technique works for both normal connection leak and "not returned to pool (pool exhausted)" leak.

Now we just need to log the interesting bits and set a trap for leaked connections.

Stack trace to identify creator

In the constructor or factory method for your connection wrapper create a new `Throwable` object and store it as a local/member variable within your wrapper object for later. We use a `Throwable` because it is faster/cheaper than using `Thread.currentThread().getStackTrace()`. This `Throwable` records/implicates the caller that requested/made the connection.

Set the "trap"

Implement the `finalize` method in your wrapper class. This is a cleanup method called by the GC when the object is being destroyed because it is no longer used.

The `finalize` method should check "am I closed?". If already closed, then everything is fine... however if the connection is being GCed and it hasn't been closed... then this is a "leaked" connection.

Now the `Throwable` comes back into play. We can grab the `Throwable` and output a nice log message saying something like: "I'm a leaked connection and here is a stack trace implicating my creator."

Expanding the idea

This method can be adapted for a variety of situations. You can of course keep other types of data in your wrapper for troubleshooting your specific issue. For instance creation time. Then you can poll for long-lived connections and again implicate the creator. Or you can poll existing connections and parse the `Throwable` stack traces to get data on which code is using how many connections over time.

There is probably an off-the-shelf tool that can also do these types of things, but the amount of code required to apply this technique is very minimal in most cases (assuming you have an easy way to swap our your db connection/factory without search-replacing your whole codebase). However if you are in the needing to search-replace your code-base situation...this is a great opportunity to clean up your code to use dependency injection or at least a central factory where you can more easily test and control your database access.

Problem

Original source