How to track/log connections in tomcat dbcp pool and detect code that does not return connection to the pool

Question

Somewhere in a large application, there is some piece of code that does not return a connection to the connection pool as it should. The result is that that the pool reaches quickly the maximum connections.

This can be worked around by setting it to remove abandoned connections, but it reduces performance.

How can I enable logging in tomcat dbcp to show when connections are borrowed and returned?

Answer 1

Logging connection borrowing and returning

I asked this question to provide my own answer. Probably not a lot of people have this problem, but it was a real challenge tracking down the code that left the connections open. I have put together the solution described here in a small github project: https://github.com/chronakis/dbcp-conn-log. You can go there or continue for a short description here.

Tomcat DBCP does not seem to have built logging, after inspecting the source. The best way I found was to use AspectJ to weave a logging method around the methods that get the connection from the pool and the code that returns the connection to the pool. The logging methods, print a short convenient call trace that shows the part of the code that opens and returns the connections as follows:

+++ getConnection(52d02201): MyDAOSQL.getConnection(69) > MyDAOSQL.getCustomerByName(568) > ...
--- retConnection(52d02201): MyDAOSQL.getCustomerByName(568) > CustomerController.getCustomer(67) > ...
+++ getConnection(7100721a): MyDAOSQL.getConnection(69) > MyDAOSQL.getBasket(568) > ...
--- retConnection(7100721a): MyDAOSQL.getBasket(568) > CustomerController.getBasket(67) > ...

Assuming you are using the java.sql.DataSource in the context xml, the methods that are getting and returning the connections are:

Get: org.apache.tomcat.dbcp.dbcp2.PoolingDataSource.getConnection
Return: org.apache.tomcat.dbcp.dbcp2.PoolingDataSource.PoolGuardConnectionWrapper.close

Knowing this, it is straightforward to weave logging methods around these methods and compile it into the code using AspectJ maven plugin as shown the project here: I have put together the those files in a small github project: https://github.com/chronakis/dbcp-conn-log

The output from the logging tool makes it easy to spot where in the code connections don't close.

Logging the actual sql activity

If you want further detail, you can use something like p6spy (search in github) to track the JDBC layer down to the sql queries. Installing it especially with maven is pretty simple.