deadlock debug methodology

["antiochus antiochus" <antiochus.usa@xxxxxxxxx>]

Hi All,

 

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

 

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?