Thread

Re: obtaining row locking information

Tatsuo Ishii <t-ishii@sra.co.jp> — 2005-08-08T07:52:14Z

> Tatsuo Ishii <t-ishii@sra.co.jp> writes:
> > With a help from Bruce, I wrote a small function which returns row
> > locking information(see attached file if you are interested).
> 
> Scanning the whole table seems a bit slow :-(

Yes, but I couldn't find any other way.

> There is another possibility: in CVS tip, anyone who is actually blocked
> on a row lock will be holding a tuple lock that shows exactly what they
> are waiting for.  For example:
> 
> Session 1:
> 
> regression=# begin;
> BEGIN
> regression=# select * from int4_tbl where f1 = 123456 for update;
>    f1   
> --------
>  123456
> (1 row)
> 
> Session 2:
> 
> << same as above, leaving session 2 blocked >
> 
> Session 1:
> 
> regression=# select * from pg_locks;
>    locktype    | database | relation | page | tuple | transactionid | classid | objid | objsubid | transaction | pid  |      mode       | granted 
> ---------------+----------+----------+------+-------+---------------+---------+-------+----------+-------------+------+-----------------+---------
>  transactionid |          |          |      |       |         14575 |         |       |          |       14576 | 2501 | ShareLock       | f
>  tuple         |    48344 |    48369 |    0 |     2 |               |         |       |          |       14576 | 2501 | ExclusiveLock   | t
>  relation      |    48344 |    48369 |      |       |               |         |       |          |       14576 | 2501 | AccessShareLock | t
>  relation      |    48344 |    48369 |      |       |               |         |       |          |       14576 | 2501 | RowShareLock    | t
>  transactionid |          |          |      |       |         14576 |         |       |          |       14576 | 2501 | ExclusiveLock   | t
>  relation      |    48344 |    10339 |      |       |               |         |       |          |       14575 | 2503 | AccessShareLock | t
>  relation      |    48344 |    48369 |      |       |               |         |       |          |       14575 | 2503 | AccessShareLock | t
>  relation      |    48344 |    48369 |      |       |               |         |       |          |       14575 | 2503 | RowShareLock    | t
>  transactionid |          |          |      |       |         14575 |         |       |          |       14575 | 2503 | ExclusiveLock   | t
> (9 rows)
> 
> Session 2 (XID 14576) is blocked on session 1 (XID 14575) according to
> the first row of this output.  The second row shows the exact tuple
> that it is after.
> 
> This isn't an amazingly user-friendly way of displaying things, of
> course, but maybe somebody could make a function that would show it
> better using pg_locks as input.

If I understand correctly, it seems the above method does show a
locked row's TID which does not block someone else. That is a little
bit different from what I expcted.

> > I think it will be more usefull if actual xids are shown in the case
> > "locker" is a multixid. It seems GetMultiXactIdMembers() does the
> > job. Unfortunately that is a static funtcion, however. Is there any
> > chance GetMultiXactIdMembers() becomes public funtion?
> 
> No particular objection here.
--
Tatsuo Ishii