Spring more debugging insights for threads and database connections
Using Java spring 2.5 (Tomcat container) and HikariCP and connection pooling system, How can I know at each moment how many Java threads are working and each thread has how many database connection ? And if possible I would like to have log for each thread and database connection separately. Like seeing for example thread A at the moment has 3 open connections to the database and these are queries being executed from each of those connections.
When you work with a connection pool like HikariCP, there are physical and virtual database connections. The connection pool opens the physical connections as required and hands out virtual connections that are wrappers to the physical connections. Virtual connections should be used for a short time only and closing them returns the physical connection to the pool.
In that sense each thread should never block more than one virtual connection. To find cases where a thread does not return a virtual connection, you can use the JDBC connection leak detection in JProfiler. It will show you virtual connections that have been open for a long time and are candidates for connection leaks. If you record CPU data, you can also see the stack trace where each such connection was requested by the thread.
To analyze connection usage in detail, the "Events" view of the JDBC probe can show you all executed statements but also which threads open and close physical database connections together with timestamps.
Disclaimer: My company develops JProfiler