Re: Blocking excessively in FOR UPDATE

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)

In response to

Responses

Browse pgsql-performance by date

  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