[Zope] Zope hangs with 'idle in transaction'

[Dieter Maurer]

Yuan HOng wrote at 2005-10-27 18:18 +0800:
> ...
>I have a site running with Zope 2.7.6 under Linux. The site access a
>PostgreSQL database through ZSQL and psycopg 1.1.18. Only read access
>is used, nothing is written to the database.
>
>The one thing that annoys me very much is, with time passing, Zope
>will eventually hang and has to be restarted. When I use ps -Af to
>view the data connection status, I discovered that whenever Zope
>hangs, I can found all zope database connection in the status 'idle in
>transaction', like below:
>
>postgres 13605   799  0 16:22 ?        00:00:07 postgres: zopeapp
>backupdb 127.0.0.1 idle in transaction
>postgres 13611   799  0 16:22 ?        00:00:06 postgres: zopeapp
>backupdb 127.0.0.1 idle in transaction
>postgres 13614   799  0 16:22 ?        00:00:00 postgres: zopeapp
>backupdb 127.0.0.1 idle in transaction
>postgres 14078   799  0 16:26 ?        00:00:05 postgres: zopeapp
>backupdb 127.0.0.1 idle in transaction

I saw something like this when more than a single database
connection was used in the same transaction -- it is a typical
database deadlock: some connection locked some resource and later
on in the transaction a second connection tried to access the locked
resource -- deadlock.

Avoid using more than one conntection to the same database in a single
transaction!

-- 
Dieter