Lock Modes (Documentation)

Thomas F. O'Connell wrote:

I thought about posting to pgsql-docs, but since this might require
comment from developers, I thought -general might be a better
starting point.

Anyway, I've occasionally run into monitoring situations where it
would be immediately helpful to know the built-in SQL statements that
generate given table-lock modes.

For instance, doesn't UPDATE implicitly mean that an ACCESS SHARE
lock will be taken if there are foreign keys involved (at least in
versions prior to 8.1)? Are there any other scenarios where a given
SQL command might take a lock of one of these forms as a result of
what it does under the hood? Maybe UPDATE is the only one since it's
implicitly a SELECT, DELETE, and INSERT all rolled into one.

I'd love to see 12.3 <http://www.postgresql.org/docs/8.0/static/
explicit-locking.html> document this more thoroughly, but I don't
know enough about the underlying locking requirements of each step of
each SQL command to know when locks might implicitly be acquired.
Even if UPDATE is the only special case, it seems like it'd be worth
mentioning.

Actually, pre-8.1 used ROW EXCLUSIVE for foreign keys because we didn't
have ROW SHARE until 8.1. I actually can't find out how we are doing
that in the code, however. Analyzing the code is probably the only way
to get this detailed lock information.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

On Wed, Nov 02, 2005 at 05:59:15PM -0500, Bruce Momjian wrote:

Actually, pre-8.1 used ROW EXCLUSIVE for foreign keys because we didn't
have ROW SHARE until 8.1. I actually can't find out how we are doing
that in the code, however. Analyzing the code is probably the only way
to get this detailed lock information.

Would it be feasable to have the lock manager spew out info about lock
aquisition and release? Not only would it make getting this information
easy, but I suspect it could be a useful debugging tool.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461

Jim C. Nasby wrote:

On Wed, Nov 02, 2005 at 05:59:15PM -0500, Bruce Momjian wrote:

Actually, pre-8.1 used ROW EXCLUSIVE for foreign keys because we didn't
have ROW SHARE until 8.1. I actually can't find out how we are doing
that in the code, however. Analyzing the code is probably the only way
to get this detailed lock information.

Would it be feasable to have the lock manager spew out info about lock
aquisition and release? Not only would it make getting this information
easy, but I suspect it could be a useful debugging tool.

Something like log_locks? That would be valuable.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Bruce Momjian <pgman@candle.pha.pa.us> writes:

Jim C. Nasby wrote:

Would it be feasable to have the lock manager spew out info about lock
aquisition and release? Not only would it make getting this information
easy, but I suspect it could be a useful debugging tool.

Something like log_locks? That would be valuable.

A moment's thought would suggest that the output would be too voluminous
to be of any use whatever to ordinary users. The capability already
exists at the developer level (see LOCK_DEBUG).

regards, tom lane

On Wed, Nov 02, 2005 at 06:30:38PM -0500, Bruce Momjian wrote:

Jim C. Nasby wrote:

On Wed, Nov 02, 2005 at 05:59:15PM -0500, Bruce Momjian wrote:

Actually, pre-8.1 used ROW EXCLUSIVE for foreign keys because we didn't
have ROW SHARE until 8.1. I actually can't find out how we are doing
that in the code, however. Analyzing the code is probably the only way
to get this detailed lock information.

Would it be feasable to have the lock manager spew out info about lock
aquisition and release? Not only would it make getting this information
easy, but I suspect it could be a useful debugging tool.

Something like log_locks? That would be valuable.

Maybe you should ask the keeper of the TODO to add it... ;P
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461

On Wed, Nov 02, 2005 at 07:12:36PM -0500, Tom Lane wrote:

Bruce Momjian <pgman@candle.pha.pa.us> writes:

Jim C. Nasby wrote:

Would it be feasable to have the lock manager spew out info about lock
aquisition and release? Not only would it make getting this information
easy, but I suspect it could be a useful debugging tool.

Something like log_locks? That would be valuable.

A moment's thought would suggest that the output would be too voluminous
to be of any use whatever to ordinary users. The capability already
exists at the developer level (see LOCK_DEBUG).

Ok, so I guess what we really need is just a docs TODO that mentions
LOCK_DEBUG.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461