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

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. +

On Jul11, 2011, at 05:47 , Bruce Momjian wrote:

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.

For consistency, I guess it should say "lock object" instead of simply
"object" the description of all the columns up to (and including)
"objsubid", not only those of "virtualxid" and "transactionid".

I'd also slightly prefer "locked object" over "lock object", because
the lock itself probably isn't a standalone entity in the mind of
most users. And for people familiar with our locking infrastructure,
the actually correct term would be "lock tag" I believe.

In any case, +1 for improving the description there.

best regards,
Florian Pflug

Florian Pflug <fgp@phlo.org> writes:

On Jul11, 2011, at 05:47 , Bruce Momjian wrote:

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.

For consistency, I guess it should say "lock object" instead of simply
"object" the description of all the columns up to (and including)
"objsubid", not only those of "virtualxid" and "transactionid".

Yeah, I think this patch is going in the wrong direction altogether.
It would be better to modify the description of virtualtransaction
and pid to say that those are the "locking" entity.

regards, tom lane

On Jul11, 2011, at 17:11 , Tom Lane wrote:

Florian Pflug <fgp@phlo.org> writes:

On Jul11, 2011, at 05:47 , Bruce Momjian wrote:

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.

For consistency, I guess it should say "lock object" instead of simply
"object" the description of all the columns up to (and including)
"objsubid", not only those of "virtualxid" and "transactionid".

Yeah, I think this patch is going in the wrong direction altogether.
It would be better to modify the description of virtualtransaction
and pid to say that those are the "locking" entity.

Hm, we already kinda of say that. Both descriptions include the phrase
"... holding or awaiting this lock.". The column "mode" says
"... held or desired by this process", which I guess is similar enough
to make it clear that these are related.

Its the columns which refer to the locked object which simply say
"object", and thus leave it open if that means locked or a locking.

Could we split that table in two parts, one for the fields referring
to the locked object and one for the locking entity, or does that depart
too far from the way we document other system catalogs and views?

If splitting it into two parts is too radical, how about adding a column
"Refers To" which says either "Locked Object" or "Locking Entity"?

best regards,
Florian Pflug

Florian Pflug <fgp@phlo.org> writes:

On Jul11, 2011, at 17:11 , Tom Lane wrote:

Yeah, I think this patch is going in the wrong direction altogether.
It would be better to modify the description of virtualtransaction
and pid to say that those are the "locking" entity.

Hm, we already kinda of say that. Both descriptions include the phrase
"... holding or awaiting this lock.". The column "mode" says
"... held or desired by this process", which I guess is similar enough
to make it clear that these are related.

Its the columns which refer to the locked object which simply say
"object", and thus leave it open if that means locked or a locking.

Could we split that table in two parts, one for the fields referring
to the locked object and one for the locking entity, or does that depart
too far from the way we document other system catalogs and views?

Then you'd have to join them, which would not be an improvement from
anybody's standpoint.

Maybe we could just add a paragraph above the "pg_locks Columns" table
that says explicitly that virtualtransaction and pid describe the entity
holding or awaiting the lock, and the others describe the object being
locked? Any way you slice it, putting this information into the
per-column table is going to be repetitive.

regards, tom lane

Tom Lane wrote:

Florian Pflug <fgp@phlo.org> writes:

On Jul11, 2011, at 05:47 , Bruce Momjian wrote:

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.

For consistency, I guess it should say "lock object" instead of simply
"object" the description of all the columns up to (and including)
"objsubid", not only those of "virtualxid" and "transactionid".

Yeah, I think this patch is going in the wrong direction altogether.
It would be better to modify the description of virtualtransaction
and pid to say that those are the "locking" entity.

OK, so as I understand it, in pg_locks:

Column | Type | Modifiers
--------------------+----------+-----------
locktype | text |
database | oid |
relation | oid |
page | integer |
tuple | smallint |
virtualxid | text |
transactionid | xid |
classid | oid |
objid | oid |
objsubid | smallint |

virtualtransaction | text |
pid | integer |
mode | text |
granted | boolean |

It is the last four that are related to the "locking entity". I don't
see a way of improving the description of the last four columns:

http://developer.postgresql.org/pgdocs/postgres/view-pg-locks.html

What was unclear to me was that the earlier columns (illogically)
vaguely represented the locked object.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

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

Tom Lane wrote:

Florian Pflug <fgp@phlo.org> writes:

On Jul11, 2011, at 17:11 , Tom Lane wrote:

Yeah, I think this patch is going in the wrong direction altogether.
It would be better to modify the description of virtualtransaction
and pid to say that those are the "locking" entity.

Hm, we already kinda of say that. Both descriptions include the phrase
"... holding or awaiting this lock.". The column "mode" says
"... held or desired by this process", which I guess is similar enough
to make it clear that these are related.

Its the columns which refer to the locked object which simply say
"object", and thus leave it open if that means locked or a locking.

Could we split that table in two parts, one for the fields referring
to the locked object and one for the locking entity, or does that depart
too far from the way we document other system catalogs and views?

Then you'd have to join them, which would not be an improvement from
anybody's standpoint.

Maybe we could just add a paragraph above the "pg_locks Columns" table
that says explicitly that virtualtransaction and pid describe the entity
holding or awaiting the lock, and the others describe the object being
locked? Any way you slice it, putting this information into the
per-column table is going to be repetitive.

Frankly, whenever anyone says "object", they might as well call it
"thing". It seems to be a content-less word. Maybe just replace the
word "object" with "lock".

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

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

Bruce Momjian <bruce@momjian.us> writes:

Tom Lane wrote:

Maybe we could just add a paragraph above the "pg_locks Columns" table
that says explicitly that virtualtransaction and pid describe the entity
holding or awaiting the lock, and the others describe the object being
locked? Any way you slice it, putting this information into the
per-column table is going to be repetitive.

Frankly, whenever anyone says "object", they might as well call it
"thing". It seems to be a content-less word. Maybe just replace the
word "object" with "lock".

No, because that conflates the lock with the thing being locked.
Fuzzing that semantic difference isn't going to make it less confusing.

regards, tom lane

On Jul11, 2011, at 17:31 , Bruce Momjian wrote:

Tom Lane wrote:

Florian Pflug <fgp@phlo.org> writes:

On Jul11, 2011, at 17:11 , Tom Lane wrote:

Yeah, I think this patch is going in the wrong direction altogether.
It would be better to modify the description of virtualtransaction
and pid to say that those are the "locking" entity.

Hm, we already kinda of say that. Both descriptions include the phrase
"... holding or awaiting this lock.". The column "mode" says
"... held or desired by this process", which I guess is similar enough
to make it clear that these are related.

Its the columns which refer to the locked object which simply say
"object", and thus leave it open if that means locked or a locking.

Could we split that table in two parts, one for the fields referring
to the locked object and one for the locking entity, or does that depart
too far from the way we document other system catalogs and views?

Then you'd have to join them, which would not be an improvement from
anybody's standpoint.

Maybe we could just add a paragraph above the "pg_locks Columns" table
that says explicitly that virtualtransaction and pid describe the entity
holding or awaiting the lock, and the others describe the object being
locked? Any way you slice it, putting this information into the
per-column table is going to be repetitive.

Frankly, whenever anyone says "object", they might as well call it
"thing". It seems to be a content-less word. Maybe just replace the
word "object" with "lock".

I like that, as long as we make it ".. lock is/isn't *on* a ...", and not
just "... lock is/isn't a". After all, the lock very clearly isn't a
relation or xid or whatever - it's a, well, lock.

We'd then have
OID of the database in which the lock exists, or zero if the lock is on a
shared object, or null if the lock is on a transaction ID.

OID of the relation, or null if the lock is not on a relation or part of a
relation.

...

ID of a transaction, or null if the lock is not on a transaction ID

...

best regards,
Florian Pflug

Bruce Momjian <bruce@momjian.us> wrote:

OK, so as I understand it, in pg_locks:

Column | Type | Modifiers
--------------------+----------+-----------
locktype | text |
database | oid |
relation | oid |
page | integer |
tuple | smallint |
virtualxid | text |
transactionid | xid |
classid | oid |
objid | oid |
objsubid | smallint |

virtualtransaction | text |
pid | integer |
mode | text |
granted | boolean |

It is the last four that are related to the "locking entity".

vaguely represented the locked object.

I think more accurately:

Information about the lock requester:

virtualtransaction, pid

Information about what is being locked:

database, relation, page, tuple, virtualxid, transactionid, classid,
objid, objsubid (where NULL means "not applicable to this lock)

Information about the lock itself:

locktype, mode, granted

-Kevin

Florian Pflug wrote:

On Jul11, 2011, at 17:31 , Bruce Momjian wrote:

Tom Lane wrote:

Florian Pflug <fgp@phlo.org> writes:

On Jul11, 2011, at 17:11 , Tom Lane wrote:

Yeah, I think this patch is going in the wrong direction altogether.
It would be better to modify the description of virtualtransaction
and pid to say that those are the "locking" entity.

Hm, we already kinda of say that. Both descriptions include the phrase
"... holding or awaiting this lock.". The column "mode" says
"... held or desired by this process", which I guess is similar enough
to make it clear that these are related.

Its the columns which refer to the locked object which simply say
"object", and thus leave it open if that means locked or a locking.

Could we split that table in two parts, one for the fields referring
to the locked object and one for the locking entity, or does that depart
too far from the way we document other system catalogs and views?

Then you'd have to join them, which would not be an improvement from
anybody's standpoint.

Maybe we could just add a paragraph above the "pg_locks Columns" table
that says explicitly that virtualtransaction and pid describe the entity
holding or awaiting the lock, and the others describe the object being
locked? Any way you slice it, putting this information into the
per-column table is going to be repetitive.

Frankly, whenever anyone says "object", they might as well call it
"thing". It seems to be a content-less word. Maybe just replace the
word "object" with "lock".

I like that, as long as we make it ".. lock is/isn't *on* a ...", and not
just "... lock is/isn't a". After all, the lock very clearly isn't a
relation or xid or whatever - it's a, well, lock.

We'd then have
OID of the database in which the lock exists, or zero if the lock is on a
shared object, or null if the lock is on a transaction ID.

OID of the relation, or null if the lock is not on a relation or part of a
relation.

...

ID of a transaction, or null if the lock is not on a transaction ID

OK, I went with this wording, using "lock object is on" terminology.
Applied patch attached --- adjustments welcomed.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

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

Bruce Momjian <bruce@momjian.us> writes:

OK, I went with this wording, using "lock object is on" terminology.
Applied patch attached --- adjustments welcomed.

I think you misunderstood the suggestion. This is not an improvement,
it's just more confusion.

regards, tom lane

Tom Lane wrote:

Bruce Momjian <bruce@momjian.us> writes:

OK, I went with this wording, using "lock object is on" terminology.
Applied patch attached --- adjustments welcomed.

I think you misunderstood the suggestion. This is not an improvement,
it's just more confusion.

Well, I thought the "lock on" wording helped avoid the confusion but
obviously I didn't understand more than that. We did have similar
confusion when we clarified the locking C code. For me, "object" was
the stumbler. Do you have any suggested wording? Everyone seems to
agree it needs improvement.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

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

On Jul13, 2011, at 17:44 , Tom Lane wrote:

Bruce Momjian <bruce@momjian.us> writes:

OK, I went with this wording, using "lock object is on" terminology.
Applied patch attached --- adjustments welcomed.

I think you misunderstood the suggestion. This is not an improvement,
it's just more confusion.

FWIW, I agree. First, "lock object" seems redundant - you might just as
well say simply "lock". This is different from "locked object" - there,
the noun "object" servers as a dummy that gives the adjective "locked"
something to refer to.

Also, it now sounds as if we were talking about the storage
location of the lock (as an entity in itself) in some of the sentences.

Here's an example

"Page number within the relation, or null if the lock object
is not on a tuple or relation page".

To me at least, that sounds as if the lock might somehow be stored
on a "relation page".

Maybe "on" is still too generic. What if we said "protects" instead?
That makes the intended relationship between the lock and the
tuple/relation/... much clearer. We'd then say

(A)
"Protected page number within the relation, or null if the lock
does not protect a tuple or relation page".

Another possibility is to make the relationship clearer by adding
the adjective "locked" before the locked thing, as in

(B)
"Locked page number within the relation, or null if the lock
is not on a tuple or relation page".

The latter also works "lock .. on .. " with
"locked object ... is ...", i.e.

(C)
"Locked page number within the relation, or null if the locked object
is not a tuple or relation page".

We could also get rid of the noun completely by saying

(D)
"Locked page number within the relation, or null if it isn't
a tuple or relation page that is locked".

I personally slightly favor (D).

best regards,
Florian Pflug

Florian Pflug wrote:

On Jul13, 2011, at 17:44 , Tom Lane wrote:

Bruce Momjian <bruce@momjian.us> writes:

OK, I went with this wording, using "lock object is on" terminology.
Applied patch attached --- adjustments welcomed.

I think you misunderstood the suggestion. This is not an improvement,
it's just more confusion.

FWIW, I agree. First, "lock object" seems redundant - you might just as
well say simply "lock". This is different from "locked object" - there,
the noun "object" servers as a dummy that gives the adjective "locked"
something to refer to.

I would personally prefer "lock" rather than "lock object".

Also, it now sounds as if we were talking about the storage
location of the lock (as an entity in itself) in some of the sentences.

Here's an example

"Page number within the relation, or null if the lock object
is not on a tuple or relation page".

To me at least, that sounds as if the lock might somehow be stored
on a "relation page".

Maybe "on" is still too generic. What if we said "protects" instead?
That makes the intended relationship between the lock and the
tuple/relation/... much clearer. We'd then say

(A)
"Protected page number within the relation, or null if the lock
does not protect a tuple or relation page".

Another possibility is to make the relationship clearer by adding
the adjective "locked" before the locked thing, as in

(B)
"Locked page number within the relation, or null if the lock
is not on a tuple or relation page".

Yes, I like this --- putting the "Locked at the front". The old code
says things like "Page number within the relation" which is kind of
generic.

The latter also works "lock .. on .. " with
"locked object ... is ...", i.e.

(C)
"Locked page number within the relation, or null if the locked object
is not a tuple or relation page".

We could also get rid of the noun completely by saying

(D)
"Locked page number within the relation, or null if it isn't
a tuple or relation page that is locked".

I personally slightly favor (D).

Me too. Others?

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

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

Bruce Momjian <bruce@momjian.us> writes:

Tom Lane wrote:

I think you misunderstood the suggestion. This is not an improvement,
it's just more confusion.

Well, I thought the "lock on" wording helped avoid the confusion but
obviously I didn't understand more than that. We did have similar
confusion when we clarified the locking C code. For me, "object" was
the stumbler. Do you have any suggested wording? Everyone seems to
agree it needs improvement.

Well, first, "lock object" is completely useless, it does not convey
more than "lock" does; and second, you've added confusion because the
very same sentences also use "object" to refer to the thing being
locked.

regards, tom lane

On 07/13/2011 12:31 PM, Tom Lane wrote:

Bruce Momjian<bruce@momjian.us> writes:

Tom Lane wrote:

I think you misunderstood the suggestion. This is not an improvement,
it's just more confusion.

Well, I thought the "lock on" wording helped avoid the confusion but
obviously I didn't understand more than that. We did have similar
confusion when we clarified the locking C code. For me, "object" was
the stumbler. Do you have any suggested wording? Everyone seems to
agree it needs improvement.

Well, first, "lock object" is completely useless, it does not convey
more than "lock" does; and second, you've added confusion because the
very same sentences also use "object" to refer to the thing being
locked.

Maybe "lock" for the lock itself and "lock target" for the thing locked,
or some such, would work.

I agree that "object" on its own is not a terribly helpful term. It's
too often shorthand for "whatever-it-is".

cheers

andrew

Florian Pflug wrote:

We could also get rid of the noun completely by saying

(D)
"Locked page number within the relation, or null if it isn't
a tuple or relation page that is locked".

I personally slightly favor (D).

I don't think we can use "Locked" here because the lock might not be
granted.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

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