Can't throw the dreaded 'idle in transaction' - need help!

Hello,

Much like other people on this list, we are a Java based developer that uses PG7.4.6 for our database. For a long time we have had intermittent and generally non-reproducible 'idle in transaction' problems that would occur from time to time. Now we're trying to resolve this issue once and for all and I was hoping if someone could enlighten me on the current 'state of play' with this issue and how I might go about finding what's causing it.

Our application makes several threaded servlet connections to PG via Jetty and a managed connection pool. We have tried several pool managers of various complexity and even tried direct (per thread) connections to the DB. We have tried the 7.4 production version of the JDBC driver and the newer 8.0 beta version (type 2 and type 3) all with the same issue (although the 8.0 beta seems better, it still occurs regularly).

From what we can establish, a singular (non-blocked) transaction is updating a row via the pool and then holding the row locked (idle in transaction). Mostly the driver seems to clear these events, but regularly (within 3 seconds) another event will occur seeking to acquire a lock for update on the same row. When this happens, the pool allocates all it's threads (set to 20) and Jetty finally blocks waiting for the update to commit. It almost seems like the second update is issued before the first has a chance to clear it's locks and the driver can't recover ( or something like this), requiring a Jetty restart.

Our problem is in debugging this:

1. We don't know how we can find out exactly which statement caused the idle-in-transaction, all we know is which is waiting for the row via pg_stat_activity. How can we find this information?

2. Is the idle in transaction a "normal" event for all update transactions? Should the database release the lock immediately after the commit, in all instances? Should we assume that when we see an 'idle in transaction' that some error event has occurred that we're not seeing, like a failed query etc, that's causing the connection to remain open?

3. We've tried to reduce all large transactions and complex queries effectively to small transaction 'blocks' or simply leave auto.commit to true and issue only single line updates. But this hasn't helped.

Most importantly, we'd like to understand why this event would cause the pool or driver to lockup, as this may help us in avoiding this issue.

Any help would be most appreciated.

Kind regards,

Andrew.