Thread: Postresql 8.0 Beta 3 - SELECT ... FOR UPDATE
Hi
Was already implemented the timeout on the "SELECT ... FOR UPDATE" (non-blocking lock) and/or is possible known if the lock exist on the specified ROW before executing the SELECT?
Please note: ours need is the timeout/verify at the ROW level, not at the table level.
Is already OK? Is in the TODO list?
May you suggest an alternative method?
Thank you.
On Thursday 21 October 2004 06:44, you wrote:
> Hi
>
> Was already implemented the timeout on the "SELECT ... FOR UPDATE"
> (non-blocking lock) and/or is possible known if the lock exist on the
> specified ROW before executing the SELECT?
>
No.
> Please note: ours need is the timeout/verify at the ROW level, not at the
> table level.
>
> Is already OK? Is in the TODO list?
> May you suggest an alternative method?
>
> Thank you.
You would need a more extensive implementation of row level locks than
PostgreSQL currently offers. There have been discussions about this in the
past, but afaik no one is actively working on it. You can probably find more
info in the archives about it, also I believe it is on the TODO list, so you
might find some more detail by looking there.
--
Robert Treat
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
There is a statement_timeout that will control how long a statement can
execute before being cancelled. We have never agreed that controlling
how long we wait for an individual lock is valuable.
---------------------------------------------------------------------------
Robert Treat wrote:
> On Thursday 21 October 2004 06:44, you wrote:
> > Hi
> >
> > Was already implemented the timeout on the "SELECT ... FOR UPDATE"
> > (non-blocking lock) and/or is possible known if the lock exist on the
> > specified ROW before executing the SELECT?
> >
>
> No.
>
> > Please note: ours need is the timeout/verify at the ROW level, not at the
> > table level.
> >
> > Is already OK? Is in the TODO list?
> > May you suggest an alternative method?
> >
> > Thank you.
>
> You would need a more extensive implementation of row level locks than
> PostgreSQL currently offers. There have been discussions about this in the
> past, but afaik no one is actively working on it. You can probably find more
> info in the archives about it, also I believe it is on the TODO list, so you
> might find some more detail by looking there.
>
> --
> Robert Treat
> Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
>
-- 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
ronzo wrote: > Hi > > Was already implemented the timeout on the "SELECT ... FOR UPDATE" (non-blocking lock) and/or is possible known if thelock exist on the specified ROW before executing the SELECT? > > Please note: ours need is the timeout/verify at the ROW level, not at the table level. > > Is already OK? Is in the TODO list? > May you suggest an alternative method? We have discussed this at length and no one could state why having an timeout per lock is any better than using a statement_timeout. We can not do a NOWAIT on a single SELECT because there are alot of locks used even for a select and having them fail randomly would be useless. -- 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
On Wed, 2004-11-24 at 22:13 -0500, Bruce Momjian wrote: > > We have discussed this at length and no one could state why having an > timeout per lock is any better than using a statement_timeout. Actually, I hit one. I have a simple queue and a number of processes pulling jobs out of the queue. Due to transactional requirements, the database is appropriate for a first cut. Anyway, a statement_timeout of 100ms is usually plenty to determine that the job is being processed, and for one of the pollers to move on, but every once in a while a large job (4 to 5MB chunk of data) would find itself in the queue which takes more than 100ms to pull out. Not a big deal, just bump the timeout in this case. Anyway, it shows a situation where it would be nice to differentiate between statement_timeout and lock_timeout OR it demonstrates that I should be using userlocks... --
Rod Taylor wrote: > On Wed, 2004-11-24 at 22:13 -0500, Bruce Momjian wrote: > > > > We have discussed this at length and no one could state why having an > > timeout per lock is any better than using a statement_timeout. > > Actually, I hit one. > > I have a simple queue and a number of processes pulling jobs out of the > queue. Due to transactional requirements, the database is appropriate > for a first cut. > > Anyway, a statement_timeout of 100ms is usually plenty to determine that > the job is being processed, and for one of the pollers to move on, but > every once in a while a large job (4 to 5MB chunk of data) would find > itself in the queue which takes more than 100ms to pull out. > > Not a big deal, just bump the timeout in this case. > > Anyway, it shows a situation where it would be nice to differentiate > between statement_timeout and lock_timeout OR it demonstrates that I > should be using userlocks... Wouldn't a LOCK NOWAIT be a better solution? That is new in 8.0. -- 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
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Rod Taylor wrote:
>> Anyway, it shows a situation where it would be nice to differentiate
>> between statement_timeout and lock_timeout OR it demonstrates that I
>> should be using userlocks...
> Wouldn't a LOCK NOWAIT be a better solution? That is new in 8.0.
LOCK NOWAIT is only helpful if you can express your problem as not
wanting to wait for a table-level lock. When you're trying to grab a
row-level lock via SELECT FOR UPDATE, there isn't any provision for
NOWAIT.
The notion of a global lock_timeout setting is bogus IMHO, because
every proposed application of it has failed to consider the locks taken
internally by the system. But that objection wouldn't apply to a SELECT
FOR UPDATE NOWAIT command where the "no wait" behavior only applied to
the row lock being explicitly grabbed.
I thought I remembered someone working on such a thing just recently.
regards, tom lane
On Wed, 2004-11-24 at 22:47 -0500, Bruce Momjian wrote: > Rod Taylor wrote: > > On Wed, 2004-11-24 at 22:13 -0500, Bruce Momjian wrote: > > > > > > We have discussed this at length and no one could state why having an > > > timeout per lock is any better than using a statement_timeout. > > > > Actually, I hit one. > > > > I have a simple queue and a number of processes pulling jobs out of the > > queue. Due to transactional requirements, the database is appropriate > > for a first cut. > > > > Anyway, a statement_timeout of 100ms is usually plenty to determine that > > the job is being processed, and for one of the pollers to move on, but > > every once in a while a large job (4 to 5MB chunk of data) would find > > itself in the queue which takes more than 100ms to pull out. > > > > Not a big deal, just bump the timeout in this case. > > > > Anyway, it shows a situation where it would be nice to differentiate > > between statement_timeout and lock_timeout OR it demonstrates that I > > should be using userlocks... > > Wouldn't a LOCK NOWAIT be a better solution? That is new in 8.0. On a for update? --
Tom Lane wrote: > Bruce Momjian <pgman@candle.pha.pa.us> writes: > > Rod Taylor wrote: > >> Anyway, it shows a situation where it would be nice to differentiate > >> between statement_timeout and lock_timeout OR it demonstrates that I > >> should be using userlocks... > > > Wouldn't a LOCK NOWAIT be a better solution? That is new in 8.0. > > LOCK NOWAIT is only helpful if you can express your problem as not > wanting to wait for a table-level lock. When you're trying to grab a > row-level lock via SELECT FOR UPDATE, there isn't any provision for > NOWAIT. > > The notion of a global lock_timeout setting is bogus IMHO, because > every proposed application of it has failed to consider the locks taken > internally by the system. But that objection wouldn't apply to a SELECT > FOR UPDATE NOWAIT command where the "no wait" behavior only applied to > the row lock being explicitly grabbed. > > I thought I remembered someone working on such a thing just recently. Added to TODO: * Allow FOR UPDATE queries to do NOWAIT 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