Deadlock Problem

Hello,

I am in trouble with more and more deadlock problems. We are
programming a web application with multiple users editing content at
the same time. Multiple times a day PostgreSQL runs into a deadlock,
which can only be resolved by killing some of the clients. Here is an
example from the pg_locks table:

SELECT l.mode, l.granted, l.pid, l.transaction, d.datname, r.relname
FROM pg_locks l, pg_database d, pg_class r WHERE d.oid=l.database AND
r.oid=l.relation;
mode | granted | pid | transaction | datname
| relname
------------------+---------+-------+-------------+--------------------
+--------------------------
AccessShareLock | t | 12708 | | p247_website_1_1_0
| pg_locks
AccessShareLock | t | 12708 | | p247_website_1_1_0
| pg_class
AccessShareLock | t | 12714 | | p247_website_1_1_0
| upd_dependency_id
AccessShareLock | t | 12714 | | p247_website_1_1_0
| preset
AccessShareLock | t | 12726 | | p247_website_1_1_0
| preset
AccessShareLock | t | 12714 | | p247_website_1_1_0
| file
AccessShareLock | t | 12726 | | p247_website_1_1_0
| node_pkey
AccessShareLock | t | 12714 | | p247_website_1_1_0
| descr_node
AccessShareLock | t | 12726 | | p247_website_1_1_0
| systemuser_usergroup_rel
AccessShareLock | t | 12726 | | p247_website_1_1_0
| permgroup_permission_rel
AccessShareLock | t | 12726 | | p247_website_1_1_0
| usergroup_permgroup_rel
AccessShareLock | t | 12726 | | p247_website_1_1_0
| account_permission_grant
AccessShareLock | t | 12726 | | p247_website_1_1_0
| permission
AccessShareLock | t | 12726 | | p247_website_1_1_0
| account_permgroup_grant
AccessShareLock | t | 12714 | | p247_website_1_1_0
| environment
AccessShareLock | t | 12726 | | p247_website_1_1_0
| environment
AccessShareLock | t | 12726 | | p247_website_1_1_0
| systemuser
AccessShareLock | t | 12726 | | p247_website_1_1_0
| account
AccessShareLock | t | 12714 | | p247_website_1_1_0
| account
AccessShareLock | t | 12714 | | p247_website_1_1_0
| nodetype
AccessShareLock | t | 12726 | | p247_website_1_1_0
| nodetype
AccessShareLock | t | 12714 | | p247_website_1_1_0
| upd_templatefile
RowExclusiveLock | t | 12726 | | p247_website_1_1_0
| upd_template
AccessShareLock | t | 12726 | | p247_website_1_1_0
| upd_template
RowExclusiveLock | t | 12714 | | p247_website_1_1_0
| upd_template
AccessShareLock | t | 12714 | | p247_website_1_1_0
| upd_template
AccessShareLock | t | 12714 | | p247_website_1_1_0
| upd_pagevalue
AccessShareLock | t | 12714 | | p247_website_1_1_0
| descr_upd_page
RowExclusiveLock | t | 12714 | | p247_website_1_1_0
| upd_page
AccessShareLock | t | 12714 | | p247_website_1_1_0
| upd_page
RowExclusiveLock | t | 12726 | | p247_website_1_1_0
| upd_page
AccessShareLock | t | 12726 | | p247_website_1_1_0
| upd_page
RowExclusiveLock | t | 12714 | | p247_website_1_1_0
| upd_dependency
AccessShareLock | t | 12714 | | p247_website_1_1_0
| upd_dependency
RowExclusiveLock | t | 12726 | | p247_website_1_1_0
| node
AccessShareLock | t | 12726 | | p247_website_1_1_0
| node
RowExclusiveLock | t | 12714 | | p247_website_1_1_0
| node
RowShareLock | t | 12714 | | p247_website_1_1_0
| node
AccessShareLock | t | 12714 | | p247_website_1_1_0
| node
AccessShareLock | t | 12714 | | p247_website_1_1_0
| descr_node_en
(40 rows)

I expected deadlocks to be reported by PostgreSQL after the configured
timeout with an error message, but this one is just hanging up all
clients.

We are running PostgreSQL 7.3.6. Client software is written in Perl
using the DBI interface.

I would really appreciate any comments where to search for the problem.

Matthias Schmitt

magic moving pixel s.a. Phone: +352 54 75 75 - 0
Technoport Schlassgoart Fax : +352 54 75 75 - 54
66, rue de Luxembourg URL : http://www.mmp.lu
L-4221 Esch-sur-Alzette