Re: Idle in TX / Java process hang's in the vicinity of JDBC

On Thu, 29 Jul 2010, David Kerr wrote:

> We've got an issue where an ETL process just sort of stalled on us last
> night.
>
> It left a dreaded "Idle in Transaction" in the database. Although
> fortunatly it didn't appear to have any tables locked.
>
> Stack trace:
> java.net.SocketInputStream.socketRead0(Native Method)
> java.net.SocketInputStream.read(SocketInputStream.java:129)
> org.postgresql.core.VisibleBufferedInputStream.readMore(VisibleBufferedInputStream.java:135)
> org.postgresql.core.VisibleBufferedInputStream.ensureBytes(VisibleBufferedInputStream.java:104)
> org.postgresql.core.VisibleBufferedInputStream.read(VisibleBufferedInputStream.java:186)
> org.postgresql.core.PGStream.Receive(PGStream.java:445)
> org.postgresql.core.PGStream.ReceiveTupleV3(PGStream.java:350)
> org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1306)
> org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:192)
> - locked org(dot)postgresql(dot)core(dot)v3(dot)QueryExecutorImpl(at)18493c9
> org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:451)
> org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:336)
> org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:235)

The stacktrace indicates that Statement.executeQuery was called and the
JDBC driver started receiving the results from the server. The driver is
in the middle of reading a particular row and is waiting for more data
from the server. From what you're saying the server thinks it is done,
but the driver does not, indicating loss of protocol synchronization.
That's tough to debug after the fact, without a reproducible case or
network traffic dump. If you still have this connection stuck in this
state you could try poking around with a debugger to say what values the
ReceiveTupleV3 routine has for l_size and already stored in answer.

> We were wondering what the:
> org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:192)
> - locked org(dot)postgresql(dot)core(dot)v3(dot)QueryExecutorImpl(at)18493c9
>
> was refering to.
>

This is just indicating that this routine is synchronized. This is done
to prevent multiple threads from trying to communicate with the server
simultaneously and does not indicate a problem.

Kris Jurka