Thread: Lock Modes (Documentation)
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. -- Thomas F. O'Connell Database Architecture and Programming Co-Founder Sitening, LLC http://www.sitening.com/ 110 30th Avenue North, Suite 6 Nashville, TN 37203-6320 615-469-5150 615-469-5151 (fax)
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