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

Temp02 wrote:

> 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?

You might want to turn on statement logging on the DB side. Once you
identify the row that is locked, you should be able to backtrack through
the logs and find a connection that locked that row but has not
subsequently committed or rolled back.

Note that there is no query that "causes" idle-in-transaction. The
connection is *idle*; it is not processing a query.

> 2. Is the idle in transaction a "normal" event for all update
> transactions?

It doesn't really have anything to do with update transactions; it's
just that you only see problems if it is an update transaction that goes
idle, as it will be holding locks. Idle in transaction just means that
the connection is in a transaction (at the JDBC level, autocommit is off
and a query has been issued) and the backend is waiting for a new query
to arrive.

> Should the database release the lock immediately after the
> commit, in all instances?

Yes.

> 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?

The usual cause is that your application is not closing (via
commit/rollback) a transaction it has started. There are many possible
reasons for this, but failing to deal with errors is a common one. I'd
suggest checking your application code to make sure that transactions
are always closed, even in the face of exceptions or other failures.

Alternatively, you might have an application/db deadlock happening
(thread 1 acquires DB lock; thread 2 acquires Java lock; thread 1 blocks
waiting for thread 2 to release the Java lock; thread 2 blocks waiting
for the transaction started by thread 1 to complete and release the DB
lock; everything stops).

Sending the JVM a SIGQUIT (causing a thread dump) might help if the
responsible thread really is blocked and hasn't just leaked the
connection or forgotten to close the transaction.

-O