Deadlock Problem - Mailing list pgsql-general

<fontfamily><param>Courier</param><x-tad-smaller>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

</x-tad-smaller></fontfamily>


<fontfamily><param>Courier</param>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

</fontfamily>
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