Fredrik.HuitfeldtMadsen@xxxxxxxxxxxxxxxxxxxxxx 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
