Skip site navigation (1) Skip section navigation (2)

deadlock debug methodology question

From: "antiochus antiochus" <antiochus(dot)usa(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: deadlock debug methodology question
Date: 2008-05-22 15:34:59
Message-ID: 3ce17ad60805220834o561b7e1v61f3028d97eb2c3c@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-general
Hi All,

I have a deadlock situation, two transactions waiting on each other to
complete. Based on the details below, would anyone have recommendations for
me, please?

Regards,

A.


I am using:
rpm -qa|grep postgres
compat-postgresql-libs-4-2PGDG.rhel5_x86_64
postgresql-server-8.2.6-1PGDG.rhel5
postgresql-8.2.6-1PGDG.rhel5
postgresql-devel-8.2.6-1PGDG.rhel5
postgresql-libs-8.2.6-1PGDG.rhel5

I set 'deadlock_timeout = 1h' in order to have time to inspect pg_locks.

The locks are:

db0=# select * from pg_locks where not granted;
   locktype    | database | relation | page | tuple | transactionid |
classid | objid | objsubid | transaction | pid  |   mode    | granted
---------------+----------+----------+------+-------+---------------+---------+-------+----------+-------------+------+-----------+---------
 transactionid |          |          |      |       |          1407
|         |       |          |        1404 | 8303 | ShareLock | f
 transactionid |          |          |      |       |          1404
|         |       |          |        1407 | 8277 | ShareLock | f
(2 rows)

Each transaction seems to be waiting on a row-level lock the other has
acquired. The tuples are:

db0=# select * from pg_locks where locktype='tuple';
 locktype | database | relation | page | tuple | transactionid | classid |
objid | objsubid | transaction | pid  |     mode      | granted
----------+----------+----------+------+-------+---------------+---------+-------+----------+-------------+------+---------------+---------
 tuple    |    16384 |    16576 |   38 |     6 |               |
|       |          |        1407 | 8277 | ExclusiveLock | t
 tuple    |    16384 |    16576 |   38 |     5 |               |
|       |          |        1404 | 8303 | ShareLock     | t
(2 rows)

The corresponding rows are:

db0=# select id from tt where ctid = '(38,6)';
 id
-----
 600
(1 row)
db0=# select id from tt where ctid = '(38,5)';
 id
-----
 611
(1 row)

Note that the id column is defined as 'id serial primary key'.

The two queries in effect in each transaction are found using:

select current_query from pg_stat_activity where procpid = 8303;
select current_query from pg_stat_activity where procpid = 8277;

Careful inspection of these (unfortunately complex) queries seems to
indicate row-level locks are acquired in consistent order, assuming that any
command of the type

update tt where ....

will always lock rows in a consistent order (can someone confirm that it is
necessarily the case).

Therefore, it is not clear to me how this deadlock situation arises.

Does anyone have a recommendation?

Responses

pgsql-general by date

Next:From: Steve AtkinsDate: 2008-05-22 16:07:23
Subject: Re: Installed pgadmin3-1.4.3 with 8.3.1 database..errors
Previous:From: antiochus antiochusDate: 2008-05-22 14:25:06
Subject: deadlock debug methodology

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group