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

Thankyou for your feedback.

Further to this issue, this is what we found:

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.

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

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.

Overall I don't really understand why this is happening or where we should
now look. Stepping line by line through the statements between that produced
under normal circumstances and that which is produced during this lock
situation, seems identical. We don't use any Java locking with the
application we leave it all up to the DB. Any thoughts on where I could go
from here?

Kind regards,

Andrew.

----- Original Message -----
From: "Oliver Jowett" <oliver(at)opencloud(dot)com>
To: "Temp02" <temp02(at)bluereef(dot)com(dot)au>
Cc: <pgsql-jdbc(at)postgresql(dot)org>
Sent: Monday, November 22, 2004 5:28 PM
Subject: Re: [JDBC] 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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faqs/FAQ.html