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