Which Java thread is responsible for a PostgreSQL database process? - 【StackMirror】|java|database|linux|postgresql|jdbc

Given a JVM connecting to a PostgreSQL database (on Linux), is there a way to find out which thread in Java is responsible for a process in the database?

NB: I'm debugging a resource-leak bug, which shows up as some idle-in-transaction processes in the database. It would be useful to match these up with the thread responsible, as the thread name will narrow down the search.

2012-04-04 23:22
by Daniel Winterstein

Surely there's a connection to the database, which might potentially be used by multiple threads (e.g. via connection pooling) - Jon Skeet 2012-04-04 23:24

In many applications, connections will be explicitly assigned to a thread. Connections should not generally be used by multiple threads at the same time (the implementation may make this possible, but many do not). Sure, it can be more efficient to have a connection pool that reuses threads appropriately, but programs in the wild often do not. Sometimes it's just easier to have assigned connections. Why complicate the question - Edmund 2012-04-05 00:29

You could set application_name (with the SET statement) to something that you can match up with the Java thread name.

2012-04-04 23:48
by kgrittn

A useful trick is to send a SIGQUIT signal to the Java process, e.g.

kill -QUIT your_process_id

Which will not kill it, but will tell it to print a thread dump. This will list all the threads, with a backtrace of what they are currently doing. Unfortunately this won't usually contain something as obvious as "conn.setAutoCommit(false)", but it will tell you where each thread is.

You can then find those points in the code and attempt to trace back to see if any are preceeded by transaction begins that are unmatched by commits/rollbacks.

Another thing that is useful when the application is being written is to have a common place where connections are created and controlled (i.e. yet another abstraction layer!). This becomes a good place to put statements for logging when connections are created and assigned to threads, and when a transaction is begun on one of them.

2012-04-05 00:40
by Edmund

Re logging & an abstraction layer, the BoneCP connection pool comes with some useful debugging support, including config.setCloseConnectionWatch() -- which creates a helper thread to watch for un-released connections (comes with a performance warning) - Daniel Winterstein 2012-04-06 09:01