Re: Database connections seemingly hanging

Fredrik(dot)HuitfeldtMadsen(at)schneider-electric(dot)com writes:
> We have a setup where 2 JBoss (5.1) servers communicate with 1 instance of
> PgPool (3.04), which again communicates with 2 Postgresql (8.4) servers.
> The JBoss servers host some Java code for us and as part of that they run
> some quartz jobs.

> These jobs are triggered right after startup and as part of that we get
> what seems to get stuck. At least when we can see in the database that
> when inspecting pg_locks, there exists a virtual transaction that has all
> desired locks granted but seems to be stuck. When we inspect
> pg_stat_activity, it seems that the process is still waiting for the query
> (SELECT ... FOR UPDATE) to finish.

> The locking transaction is described here: http://pastebin.com/3pEn6vPe

What that shows is several sessions running SELECT FOR UPDATE, but none
of them seem to be waiting. What else is going on? In particular, are
there any idle-in-transaction sessions? Also, would any of these
SELECTs return enough rows that the sessions might be blocked trying to
send data back to their clients? That wouldn't show as waiting = true,
though I think you could detect it by strace'ing the backends to see if
they are stopped in a send() kernel call.

> We suspect that a connection to the database acquires its locks but
> somehow does not return to the application. If this is true, it would
> either be a postgresql or a pgpool problem. We would appreciate any help
> in further debugging or resolving the situation.

It seems like a good guess would be that you have a deadlock situation
that cannot be detected by the database because part of the blockage is
on the client side --- that is, client thread A is waiting on its
database query, that query is waiting on some lock held by client thread
B's database session, and thread B is somehow waiting for A on the
client side. It's not too hard to get into this type of situation when
B is sitting on an open idle-in-transaction session: B isn't waiting for
the database to do anything, but is doing something itself, and so it's
not obvious that there's any risk. Thus my question about what idle
sessions there might be. This does usually lead to a visibly waiting
database session for client A, though, so it's probably too simple as an
explanation for your issue. We have seen some harder-to-debug cases
where the database sessions weren't visibly "waiting" because they were
blocked on client I/O, so maybe you've got something like that.

Another line of thought to pursue is possible misuse of pgpool. If
pgpool doesn't realize you're inside a transaction and swaps the
connection to some other client thread, all kinds of confusion ensues.

Also, I hope you're running a reasonably recent 8.4.x minor release.
A quick look through the commit logs didn't show anything about deadlock
fixes in the 8.4 branch, but I might have missed something that was
fixed a long time ago.

regards, tom lane