Re: Need help understanding pg_locks

Florian Pflug wrote:
> 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.

Thank you. I think my confusion is that virtualtransaction is the lock
holder/waiter, and the other two are actual locks. The attached doc
patch clarifies that. I had actually realized this a few weeks ago and
forgot, meaning this is pretty confusing.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +