Re: Need help understanding pg_locks - Mailing list pgsql-hackers

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. +
diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
new file mode 100644
index d4a1d36..9119929
*** a/doc/src/sgml/catalogs.sgml
--- b/doc/src/sgml/catalogs.sgml
***************
*** 6964,6970 ****
        <entry><type>text</type></entry>
        <entry></entry>
        <entry>
!        Virtual ID of a transaction, or null if the object is not a
         virtual transaction ID
        </entry>
       </row>
--- 6964,6970 ----
        <entry><type>text</type></entry>
        <entry></entry>
        <entry>
!        Virtual ID of a transaction lock, or null if the lock object is not a
         virtual transaction ID
        </entry>
       </row>
***************
*** 6973,6979 ****
        <entry><type>xid</type></entry>
        <entry></entry>
        <entry>
!        ID of a transaction, or null if the object is not a transaction ID
        </entry>
       </row>
       <row>
--- 6973,6979 ----
        <entry><type>xid</type></entry>
        <entry></entry>
        <entry>
!        ID of a transaction lock, or null if the lock object is not a transaction ID
        </entry>
       </row>
       <row>