deadlock debug methodology

------=_Part_650_13570022.1211466306146
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

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?

------=_Part_650_13570022.1211466306146
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

<div>Hi All,</div>
<div>&nbsp;</div>
<div>I have a deadlock situation, two transactions waiting on each other
to complete. Based on the details below,&nbsp;does anyone&nbsp;have
recommendations for me. Thanks.</div>
<div>&nbsp;</div>
<div>Regards,</div>
<div>&nbsp;</div>
<div>A.</div>
<div>&nbsp;</div>
<div>
<div>&nbsp;</div>
<div>I am using:<br>rpm -qa|grep
postgres<br>compat-postgresql-libs-4-2PGDG.rhel5_x86_64<br>postgresql-server-8.2.6-1PGDG.rhel5<br>postgresql-8.2.6-1PGDG.rhel5<br>postgresql-devel-8.2.6-1PGDG.rhel5<br>postgresql-libs-8.2.6-1PGDG.rhel5</div>

<div>&nbsp;</div></div>
<div>I set &#39;deadlock_timeout = 1h&#39; in order to have time to
inspect pg_locks.</div>
<div><br>The locks are:</div>
<div>&nbsp;</div>
<div>db0=# select * from pg_locks where not granted;<br>&nbsp;&nbsp;
locktype&nbsp;&nbsp;&nbsp; | database | relation | page | tuple |
transactionid | classid | objid | objsubid | transaction | pid&nbsp;
|&nbsp;&nbsp; mode&nbsp;&nbsp;&nbsp; |
granted<br>---------------+----------+----------+------+-------+---------------+---------+-------+----------+-------------+------+-----------+---------<br>
&nbsp;transactionid
|&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
|&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
|&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
|&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1407
|&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
|&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
|&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
|&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1404 | 8303 | ShareLock |
f<br>&nbsp;transactionid
|&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
|&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
|&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
|&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1404
|&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
|&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
|&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
|&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1407 | 8277 | ShareLock |
f<br>
(2 rows)</div>
<div>&nbsp;</div>
<div>Each transaction seems to be waiting on a row-level lock the other
has acquired. The tuples are:</div>
<div>&nbsp;</div>
<div>db0=# select * from pg_locks where
locktype=&#39;tuple&#39;;<br>&nbsp;locktype | database | relation | page |
tuple | transactionid | classid | objid | objsubid | transaction |
pid&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp; mode&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |
granted<br>----------+----------+----------+------+-------+---------------+---------+-------+----------+-------------+------+---------------+---------<br>
&nbsp;tuple&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp; 16384
|&nbsp;&nbsp;&nbsp; 16576 |&nbsp;&nbsp; 38 |&nbsp;&nbsp;&nbsp;&nbsp; 6
|&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
|&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
|&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
|&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
|&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1407 | 8277 | ExclusiveLock |
t<br>&nbsp;tuple&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp; 16384
|&nbsp;&nbsp;&nbsp; 16576 |&nbsp;&nbsp; 38 |&nbsp;&nbsp;&nbsp;&nbsp; 5
|&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
|&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
|&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
|&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
|&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1404 | 8303 |
ShareLock&nbsp;&nbsp;&nbsp;&nbsp; | t<br>
(2 rows)</div>
<div><br>The corresponding rows are:</div>
<div>&nbsp;</div>
<div>db0=# select id from tt where ctid =
&#39;(38,6)&#39;;<br>&nbsp;id&nbsp; <br>-----<br>&nbsp;600 <br>(1
row)</div>
<div>db0=# select id from tt where ctid =
&#39;(38,5)&#39;;<br>&nbsp;id&nbsp; <br>-----<br>&nbsp;611<br>(1
row)</div>
<div><br>Note that the id column is defined as &#39;id serial primary
key&#39;.</div>
<div>&nbsp;</div>
<div>The two queries in effect in each transaction are found using:</div>
<div>&nbsp;</div>
<div>select current_query from pg_stat_activity where procpid =
8303;<br>select current_query from pg_stat_activity where procpid =
8277;</div>
<div>&nbsp;</div>
<div>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</div>
<div>&nbsp;</div>
<div>update tt where ....</div>
<div>&nbsp;</div>
<div>will always lock rows in a consistent order (can someone confirm that
it is necessarily the case).</div>
<div><br>Therefore, it is not clear to me how this deadlock situation
arises.</div>
<div><br>Does anyone have a recommendation?</div>
<div>&nbsp;</div>

------=_Part_650_13570022.1211466306146--