Re: obtaining row locking information - Mailing list pgsql-hackers

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 :-(

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
|ftuple         |    48344 |    48369 |    0 |     2 |               |         |       |          |       14576 | 2501
|ExclusiveLock   | trelation      |    48344 |    48369 |      |       |               |         |       |          |
   14576 | 2501 | AccessShareLock | trelation      |    48344 |    48369 |      |       |               |         |
 |          |       14576 | 2501 | RowShareLock    | ttransactionid |          |          |      |       |
14576|         |       |          |       14576 | 2501 | ExclusiveLock   | trelation      |    48344 |    10339 |
|      |               |         |       |          |       14575 | 2503 | AccessShareLock | trelation      |    48344
|   48369 |      |       |               |         |       |          |       14575 | 2503 | AccessShareLock |
trelation     |    48344 |    48369 |      |       |               |         |       |          |       14575 | 2503 |
RowShareLock   | ttransactionid |          |          |      |       |         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.

> 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.
        regards, tom lane