Re: Need help understanding pg_locks

On Jul10, 2011, at 06:01 , Bruce Momjian wrote:
> Can someone help me understand pg_locks? There are three fields related
> to virtual and real xids:
>
> virtualtransaction | text |
> transactionid | xid |
> virtualxid | text |
>
> Our docs say 'virtualtransaction' is:
>
> Virtual ID of the transaction that is holding or awaiting this lock
>
> This field was clear to me.
>
> and 'transactionid' is documented as:
>
> ID of a transaction, or null if the object is not a transaction ID
>
> In my testing it was the (non-virtual) xid of the lock holder. Is that
> correct? Can it be a waiter?

'transactionid' is locked (or waited for) xid, just as 'relation' is
the oid of a locked or waited for pg_class entry.

What you saw was probably the lock each transaction hold on its own xid
(if it has one, that is). There can be waiters on locks of type
'transactionid' - e.g. a transaction which tries to update a tuple
modified by transaction Y will wait on Y's xid until Y commits or rolls
back, and then take appropriate action.

> 'virtualxid' is documented as:
>
> Virtual ID of a transaction, or null if the object is not a
> virtual transaction ID
>
> In my testing this field is for locking your own vxid, meaning it owned
> by its own vxid.

Its the virtual-xid version of 'transactionid', i.e. the virtual xid
which is locked or being waited for.

Again, each transaction hold a lock on its own vxid, so that is was
you saw. Waiters on 'virtualxid' are much less common, but for example
CREATE INDEX CONCURRENTLY does that.

> Clearly our documentation is lacking in this area and I would like to
> clarify it.

It seems that we should put a stronger emphasis on which fields of
pg_locks refer to the locked (or waited for) object, and which to the
lock holder (or waiter).

AFAICS, currently all fields up to (but excluding) 'virtualtransaction'
describe the locked objects. Depending on 'locktype', some fields are
always NULL (like 'relation' for locktype 'virtualxid').

All later fields (virtualtransaction, pid, mode, granted) describe the
lock holder or waiter.

best regards,
Florian Pflug