| From: | Claudio Freire <klaussfreire(at)gmail(dot)com> |
|---|---|
| To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
| Cc: | postgres performance list <pgsql-performance(at)postgresql(dot)org> |
| Subject: | Re: Blocking excessively in FOR UPDATE |
| Date: | 2011-11-03 19:53:41 |
| Message-ID: | CAGTBQpYK6-ycWr0WXRwz39Bv30sCbDe68CC3MbhUp9-jwu_V=g@mail.gmail.com |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-performance |
On Thu, Nov 3, 2011 at 4:29 PM, Claudio Freire <klaussfreire(at)gmail(dot)com> wrote:
> Next time I find it blocking, I will check pg_locks directly and post
> the output.
Here it is, two instances of the query, while blocked:
select * from pg_locks where pid = 22636;
locktype | database | relation | page | tuple | virtualxid |
transactionid | classid | objid | objsubid | virtualtransaction | pid
| mode | granted
---------------+----------+----------+------+-------+-------------+---------------+---------+-------+----------+--------------------+-------+------------------+---------
transactionid | | | | | |
360992199 | | | | 89/22579344 | 22636 |
ExclusiveLock | t
virtualxid | | | | | 89/22579344 |
| | | | 89/22579344 | 22636 |
ExclusiveLock | t
relation | 16398 | 5552020 | | | |
| | | | 89/22579344 | 22636 |
AccessShareLock | t
relation | 16398 | 5552020 | | | |
| | | | 89/22579344 | 22636 |
RowExclusiveLock | t
relation | 16398 | 5552019 | | | |
| | | | 89/22579344 | 22636 |
AccessShareLock | t
relation | 16398 | 5552019 | | | |
| | | | 89/22579344 | 22636 |
RowExclusiveLock | t
relation | 16398 | 5525292 | | | |
| | | | 89/22579344 | 22636 |
RowShareLock | t
relation | 16398 | 5525292 | | | |
| | | | 89/22579344 | 22636 |
RowExclusiveLock | t
relation | 16398 | 5552008 | | | |
| | | | 89/22579344 | 22636 |
AccessShareLock | t
relation | 16398 | 5552008 | | | |
| | | | 89/22579344 | 22636 |
RowExclusiveLock | t
(10 rows)
select * from pg_locks where pid = 22618;
locktype | database | relation | page | tuple | virtualxid |
transactionid | classid | objid | objsubid | virtualtransaction | pid
| mode | granted
---------------+----------+----------+------+-------+-------------+---------------+---------+-------+----------+--------------------+-------+------------------+---------
virtualxid | | | | | 159/2706505 |
| | | | 159/2706505 | 22618 |
ExclusiveLock | t
relation | 16398 | 5551986 | | | |
| | | | 159/2706505 | 22618 |
AccessShareLock | t
transactionid | | | | | |
360992478 | | | | 159/2706505 | 22618 |
ExclusiveLock | t
relation | 16398 | 5552008 | | | |
| | | | 159/2706505 | 22618 |
AccessShareLock | t
relation | 16398 | 5552008 | | | |
| | | | 159/2706505 | 22618 |
RowExclusiveLock | t
relation | 16398 | 5526310 | | | |
| | | | 159/2706505 | 22618 |
RowShareLock | t
relation | 16398 | 5552020 | | | |
| | | | 159/2706505 | 22618 |
AccessShareLock | t
relation | 16398 | 5552020 | | | |
| | | | 159/2706505 | 22618 |
RowExclusiveLock | t
relation | 16398 | 5526308 | | | |
| | | | 159/2706505 | 22618 |
AccessShareLock | t
relation | 16398 | 5552019 | | | |
| | | | 159/2706505 | 22618 |
AccessShareLock | t
relation | 16398 | 5552019 | | | |
| | | | 159/2706505 | 22618 |
RowExclusiveLock | t
relation | 16398 | 5525296 | | | |
| | | | 159/2706505 | 22618 |
RowShareLock | t
relation | 16398 | 5525292 | | | |
| | | | 159/2706505 | 22618 |
RowShareLock | t
relation | 16398 | 5525292 | | | |
| | | | 159/2706505 | 22618 |
RowExclusiveLock | t
(14 rows)
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Mario Weilguni | 2011-11-03 20:07:59 | Re: Performance Problem with postgresql 9.03, 8GB RAM,Quadcore Processor Server--Need help!!!!!!! |
| Previous Message | Claudio Freire | 2011-11-03 19:29:02 | Re: Blocking excessively in FOR UPDATE |