[Zope] Strangeness with Zope and Postgres

[Chris Kratz]

For months now, we have been noticing that every once in awhile zope just
appears to freeze up.  I go and look at the zope processes and they are all
sleeping.  The only way to fix the problem was to restart zope, hence I
didn't think about the fact that it might be the database.  I have been
monitoring it and assumed it was something funny in Zope, but something that
was mentioned on this list made me start thinking that it could be something
funny happening with the database.

We use postgres as our dbms and our site is heavily database instensive.
Sure enough, when ever the site appears to freeze, I go and look at the
database threads and one thread has a status of "UPDATE WAITING" and the the
others are sitting in the normal idle in transaction status.  The server is
idle (2% or less cpu) and the drives are as normal, silent.  The server is
doing absolutely nothing.

I also found out how to lock it up every time.  I have a specific page that
does some intensive database work including some updates to some preference
tables as well as some reads of some large tables, etc.  If I go to that
page and then hit F5 twice in succession, it locks up the system every time
requiring a zope restart.

Now I am starting to think that we are running into a concurrency issue with
postgres and transactions.  I was wondering if anyone with a bit more
experience might be able to shed some more light on the situation and/or
where I might look to further debug the situation.

My hunch is that the following happens:
1. First request comes in.
2. User presses F5 before the first transaction finished.
3. Browser drops first connection and reconnects.
4. Zope starts to write to the socket, notices that the browser is no longer
connected.
5. Zope aborts the transaction.
6. BUT, the database never get's a rollback and simply waits in the
transaction for ever.
7. Unfortunately, this must also cause some locks that don't allow any other
thread to do any work.
8.  Restarting Zope closes those db connections and postgres roles the
transaction back.

Does anyone have any further suggestions on how to debug this further or to
stop the problem?

-Chris

RH 7.2
Zope 2.4.1
ZPoPyDA
Postgres 7.1.3
------------------------------
Chris Kratz
chris.kratz@vistashare.com