Thread: Re: SELECT FOR UPDATE NOWAIT
> > Rod Taylor wrote: > -- Start of PGP signed section. > > On Fri, 2003-07-18 at 19:46, Paulo Scardine wrote: > > > My boss is asking for something like Oracle's "SELECT FOR UPDATE NOWAIT". > > > > > > Is there any such feature? If no, should I look forward into implementing > > > this? Any advice? > > > > Lookup STATEMENT_TIMEOUT and set it to a very short time. > > Some people have said they want to distinguish between a slow query > (busy system) and waiting on a lock. I can particulary see wanting to > do a NOWAIT only on exclusive locks --- not sure how many really want > that, though. > I think I'm a quite attentive to the SQL and HACKERS list, and I see requests for a NOWAIT option at least once a month, and it's growing. Regards, Christoph
(Excuse my english, my native language is portuguese) > I think I'm a quite attentive to the SQL and HACKERS list, and I see > requests for a NOWAIT option at least once a month, and it's growing. > Regards, Christoph Yes. I have done a little google about "pgsql select for update nowait" and find lot of people looking for this feature. If there is a lock and NOWAIT is specified, then raise error instead of waiting for the lock to release. We have some long distance connections that get very busy sometimes and is hard to distinguish between network problems and locked rows. Do you see it as something hard to implement? Any advice? Is it just to add an "opt_nowait" to the "FOR UPDATE" clause in the parser and checking for this option when trying to get the lock later? TIA, -- Paulo Scardine ----- Original Message ----- From: "Christoph Haller" <ch@rodos.fzk.de> Sent: Wednesday, July 23, 2003 4:35 AM Subject: Re: [HACKERS] SELECT FOR UPDATE NOWAIT ... > > > Lookup STATEMENT_TIMEOUT and set it to a very short time. > > > > Some people have said they want to distinguish between a slow query > > (busy system) and waiting on a lock. I can particulary see wanting to > > > do a NOWAIT only on exclusive locks --- not sure how many really want > > that, though. > > --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.490 / Virus Database: 289 - Release Date: 16/6/2003
Paulo Scardine wrote: > (Excuse my english, my native language is portuguese) > > > I think I'm a quite attentive to the SQL and HACKERS list, and I see > > requests for a NOWAIT option at least once a month, and it's growing. > > Regards, Christoph > > Yes. I have done a little google about "pgsql select for update nowait" and > find lot of people looking for this feature. > > If there is a lock and NOWAIT is specified, then raise error instead of > waiting for the lock to release. We have some long distance connections that > get very busy sometimes and is hard to distinguish between network problems > and locked rows. > > Do you see it as something hard to implement? Any advice? > Is it just to add an "opt_nowait" to the "FOR UPDATE" clause in the parser > and checking for this option when trying to get the lock later? My guess is that we will implement it was a SET variable so it can control FOR UPDATE/LOCK/UPDATE/DELETE. Added to TODO: Add GUC variable to prevent waiting on locks -- 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, Pennsylvania19073
> My guess is that we will implement it was a SET variable so it can > control FOR UPDATE/LOCK/UPDATE/DELETE. > > Added to TODO: > > Add GUC variable to prevent waiting on locks Interesting. I have a lot of potentially dumb questions: - Is this easier to implement as a user variable than in the parser or is some kind of police (SQL compliance, etc.)? - These locks are grant by LockAcquire() or by higher level functions? - Where is the best place to put this? TIA, -- Paulo Scardine --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.490 / Virus Database: 289 - Release Date: 16/6/2003
"Paulo Scardine" <paulos@cimed.ind.br> writes:
> - Where is the best place to put this?
I think it would be a really *bad* idea to put it in LockAcquire; that
risks breaking things that you don't want broken.
Whether it's special syntax or a GUC variable, the restriction should
only apply to SELECT FOR UPDATE row locks, perhaps user-commanded LOCK
TABLE operations, and maybe one or two other places that are known to
be used only for user-written operations and not for system-initiated
ones. Those places would need to check whether to do a conditional
or unconditional lock.
regards, tom lane
Tom Lane wrote: > "Paulo Scardine" <paulos@cimed.ind.br> writes: > > - Where is the best place to put this? > > I think it would be a really *bad* idea to put it in LockAcquire; that > risks breaking things that you don't want broken. > > Whether it's special syntax or a GUC variable, the restriction should > only apply to SELECT FOR UPDATE row locks, perhaps user-commanded LOCK > TABLE operations, and maybe one or two other places that are known to > be used only for user-written operations and not for system-initiated > ones. Those places would need to check whether to do a conditional > or unconditional lock. My original idea was to have it apply only for exclusive locks. It seemed those were mostly the locks didn't want to wait for. Shared locking pg_class is something that you would want to wait for. -- 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, Pennsylvania19073
LockAcquire has a "dontWait" parameter, which do just what I want. The executor level calls "heap_open(relid, RowShareLock)" when doing "FOR UPDATE"s. Should we define something like RowShareLockNoWait, so heap_open() or other lower level functions can call LockAcquire() with dontWait set? By the way, is this kind of question on-topic for pgsql-hackers? TIA, -- Paulo Scardine ----- Original Message ----- From: "Bruce Momjian" <pgman@candle.pha.pa.us> To: "Tom Lane" <tgl@sss.pgh.pa.us> Cc: "Paulo Scardine" <paulos@cimed.ind.br>; <pgsql-hackers@postgresql.org> Sent: Wednesday, July 23, 2003 4:30 PM Subject: Re: [HACKERS] SELECT FOR UPDATE NOWAIT > Tom Lane wrote: > > "Paulo Scardine" <paulos@cimed.ind.br> writes: > > > - Where is the best place to put this? > > > > I think it would be a really *bad* idea to put it in LockAcquire; that > > risks breaking things that you don't want broken. > > > > Whether it's special syntax or a GUC variable, the restriction should > > only apply to SELECT FOR UPDATE row locks, perhaps user-commanded LOCK > > TABLE operations, and maybe one or two other places that are known to > > be used only for user-written operations and not for system-initiated > > ones. Those places would need to check whether to do a conditional > > or unconditional lock. > > My original idea was to have it apply only for exclusive locks. It > seemed those were mostly the locks didn't want to wait for. Shared > locking pg_class is something that you would want to wait for. > > -- > 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 > > ---------------------------(end of broadcast)--------------------------- > TIP 7: don't forget to increase your free space map settings > --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.490 / Virus Database: 289 - Release Date: 16/6/2003
Paulo Scardine wrote: > LockAcquire has a "dontWait" parameter, which do just what I want. > > The executor level calls "heap_open(relid, RowShareLock)" when doing "FOR > UPDATE"s. > Should we define something like RowShareLockNoWait, so heap_open() or other > lower level functions can call LockAcquire() with dontWait set? > > By the way, is this kind of question on-topic for pgsql-hackers? I think there are two issues with implementing nowait locking: If we have special syntax for FOR UPDATE, we will need it for other commands that need no wait behavior, and after a while they all carry around that cruft --- SET seems easier and more useful. Second, I don't think we want to carry around a NOWAIT boolean in all our structures --- a SET would control it easier. The SET can be checked right in the lock code, and I think having it control only exclusive locks would do almost everything we want. -- 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, Pennsylvania19073
> > Paulo Scardine wrote: > > LockAcquire has a "dontWait" parameter, which do just what I want. > > > > The executor level calls "heap_open(relid, RowShareLock)" when doing "FOR > > UPDATE"s. > > Should we define something like RowShareLockNoWait, so heap_open() or other > > lower level functions can call LockAcquire() with dontWait set? > > > > By the way, is this kind of question on-topic for pgsql-hackers? > > I think there are two issues with implementing nowait locking: > > If we have special syntax for FOR UPDATE, we will need it for other > commands that need no wait behavior, and after a while they all carry > around that cruft --- SET seems easier and more useful. > > Second, I don't think we want to carry around a NOWAIT boolean in all > our structures --- a SET would control it easier. The SET can be > checked right in the lock code, and I think having it control only > exclusive locks would do almost everything we want. > Sounds reasonable to me. You'll have my vote for the SET way. Regards, Christoph