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

Temp02 wrote:

> 1. Turning on statement logging for every query shows that the last
> transaction that was responsible for the 'idle in transaction' did in fact
> complete successfully. The code did not error during this processing and
> appeared to complete the transaction normally.

What do you mean by "complete the transaction normally" -- is the last
logged statement on that connection a COMMIT or ROLLBACK? Can you
provide statement logging for one of the connections that gets stuck in
idle-in-transaction up to the point it gets stuck?

> 2. The lock being held is a row lock that pertains to only one user, so we
> don't know why this event would cause the entire servlet to block and hold,
> as other threads from the pool should be available for other users which are
> not reliant on this row.

The locks you need to obtain depends on the query you are executing, so
it's hard to say.

> This to me seems like a jdbc driver issue, because
> Jetty is happy to service other servlets that require no database connection
> (indicating that Jetty is okay), and when we tried this without the use of
> any DB connection pool, the same situation occured. Can anyone validate if
> this seems logical?

The locks are all on the server side, and the JDBC driver knows nothing
about them. So I can't really see it being a JDBC driver bug, unless the
JDBC driver is getting transaction demarcation wrong.

> 3. Doing periodic 'ps' on the server frequently shows threads that remain
> 'idle in transaction' for up to about 3-5 secs before being normally
> cleared. Is this what we should expect even if we issue explicit con.commit
> statements after the statement closes? I would have not expected to see idle
> in transaction at all.

For a particular connection the lifecycle looks something like:

connection made from client
  mark connection as idle
receive BEGIN from client
  mark connection as busy
  start transaction
  send BEGIN result to client
  mark connection as idle in transaction
receive query from client
  mark connection as busy
  execute query
  send results to client
  mark connection as idle in transaction
[... more queries ...]
receive COMMIT or ROLLBACK from client
  mark connection as busy
  commit or rollback transaction
  send COMMIT or ROLLBACK result to client
  mark connection as idle

So it is normal to see connections that are "idle in transaction" for
short periods; these are connections that are waiting for the next query
from the client.

-O