Re: view row-level locks

Vivek Khera <vivek(at)khera(dot)org> writes:
> On Jul 11, 2008, at 4:24 AM, Richard Huxton wrote:
>> If you just want to see if a lock has been taken (e.g. SELECT FOR
>> UPDATE) then that shows in pg_locks. If you want details on the
>> actual rows involved, then you probably want "pgrowlocks" mentioned
>> in Appendix F. Additional Supplied Modules.

> pg_locks tells you the page/tuple so you can select it with those
> values. Assuming they are page=132 and tuple=44 and relation=99 you
> can find the tuple thusly:

Although pg_locks does have columns that might make you think it shows
row locks, relying on it to do so is erroneous. A row lock shows in
pg_locks only transiently while it is being taken (or waited for).
Once the tuple is marked as locked on-disk, the pg_locks entry is
removed --- otherwise we'd soon run out of the limited space in the
in-memory locks table. So you really need contrib/pgrowlocks if
you are interested in looking at held rowlocks.

> What I need to see is which locks my other queries are waiting on. If
> pg_locks would show me which process is also blocking on this lock,
> I'd be a much happier man today (actually, last tuesday, when this was
> a problem for me to debug something).

Hmm? pg_locks will tell you about it, though you need to know how to
interpret it. For example:

(session 1)

regression=# create table t(f1 int);
CREATE TABLE
regression=# insert into t values(0);
INSERT 0 1
regression=# begin;
BEGIN
regression=# update t set f1=f1+1;
UPDATE 1
regression=#

(now start session 2, and do)

regression=# update t set f1=f1+1;
(blocks)

(now in session 3)

regression=# select * from pg_locks order by pid, granted desc;
locktype | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction | pid | mode | granted
---------------+----------+----------+------+-------+------------+---------------+---------+-------+----------+--------------------+------+------------------+---------
virtualxid | | | | | 1/4054 | | | | | 1/4054 | 4371 | ExclusiveLock | t
transactionid | | | | | | 4658 | | | | 1/4054 | 4371 | ExclusiveLock | t
relation | 16384 | 40014 | | | | | | | | 1/4054 | 4371 | RowExclusiveLock | t
virtualxid | | | | | 2/168 | | | | | 2/168 | 4373 | ExclusiveLock | t
transactionid | | | | | | 4659 | | | | 2/168 | 4373 | ExclusiveLock | t
relation | 16384 | 40014 | | | | | | | | 2/168 | 4373 | RowExclusiveLock | t
tuple | 16384 | 40014 | 0 | 1 | | | | | | 2/168 | 4373 | ExclusiveLock | t
transactionid | | | | | | 4658 | | | | 2/168 | 4373 | ShareLock | f
virtualxid | | | | | 3/8 | | | | | 3/8 | 4383 | ExclusiveLock | t
relation | 16384 | 10960 | | | | | | | | 3/8 | 4383 | AccessShareLock | t
(10 rows)

The first three of these are locks held by session 1 on its own xids
plus RowExclusiveLock (writer's lock) on table t. The next three are
the corresponding locks held by session 2. Next there is a tuple lock,
which is *held* not awaited by session 2 --- but that's just the
transient lock that it holds to prevent race conditions while it's
marking the tuple as locked. Since session 1 has already marked the
tuple as locked, session 2 has to wait for session 1's transaction to
end, and that's what the awaited ShareLock in the next row is for.
(The last two rows are from session 3, which is reading pg_locks.)

So in short, if you see someone blocked on ShareLock for someone
else's transaction ID, look for a tuple lock held by the first someone.
That will tell you what the contention is really about.

regards, tom lane