Thread

Re: Need help understanding pg_locks

Bruce Momjian <bruce@momjian.us> — 2011-07-11T03:47:36Z

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@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

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