Thread: Feature: FOR UPDATE SKIP LOCKED
I’m been reading up on FOR UPDATE NOWAIT and it looks like it was added in 8.1.
How difficult is it to add FOR UPDATE SKIP LOCKED or something similar? (basically skip locked rows / oracle syntax)
More background here:
http://forge.mysql.com/worklog/task.php?id=3597
It would be quite useful to implement a database queue. Although FOR UPDATE NOWAIT and trying again can work as well as other techniques,
just skipping over the locks has its advantages (simplicity and zero wait)
"Jonathan Bond-Caron" <jbondc@gmail.com> writes:
> It would be quite useful to implement a database queue. Although FOR UPDATE
> NOWAIT and trying again can work as well as other techniques,
> just skipping over the locks has its advantages (simplicity and zero wait)
And disadvantages, such as complete lack of predictability or failure
detection.
regards, tom lane
On Wed, 2008-07-09 at 00:48 -0400, Tom Lane wrote: > "Jonathan Bond-Caron" <jbondc@gmail.com> writes: > > It would be quite useful to implement a database queue. Although FOR UPDATE > > NOWAIT and trying again can work as well as other techniques, > > > just skipping over the locks has its advantages (simplicity and zero wait) > > And disadvantages, such as complete lack of predictability or failure > detection. Well, it's not like SQL is completely predictable in general... think about ordering of results. Such a feature would definitely help queue like table processing, and the fact that it is predictably unpredictable should not be a surprise for anybody using such a feature... Cheers, Csaba.
Csaba Nagy wrote: > On Wed, 2008-07-09 at 00:48 -0400, Tom Lane wrote: >> "Jonathan Bond-Caron" <jbondc@gmail.com> writes: >>> It would be quite useful to implement a database queue. Although FOR UPDATE >>> NOWAIT and trying again can work as well as other techniques, >>> just skipping over the locks has its advantages (simplicity and zero wait) >> And disadvantages, such as complete lack of predictability or failure >> detection. > > Well, it's not like SQL is completely predictable in general... think > about ordering of results. Such a feature would definitely help queue > like table processing, and the fact that it is predictably unpredictable > should not be a surprise for anybody using such a feature... Especially if it returned an updated row count or supported the RETURNING clause, so you could find out after the fact what was or wasn't done. -- Craig Ringer
On Wed, 2008-07-09 at 16:23 +0800, Craig Ringer wrote: > Especially if it returned an updated row count or supported the > RETURNING clause, so you could find out after the fact what was or > wasn't done. Well, it is supposed to be used as "SELECT ... FOR UPDATE SKIP LOCKED", so you can in fact put the locked row ids in the target list. With a "LIMIT 1" appended would be the perfect way to check out the next queue item to process... Cheers, Csaba.
Csaba Nagy wrote: > On Wed, 2008-07-09 at 16:23 +0800, Craig Ringer wrote: >> Especially if it returned an updated row count or supported the >> RETURNING clause, so you could find out after the fact what was or >> wasn't done. > > Well, it is supposed to be used as "SELECT ... FOR UPDATE SKIP LOCKED", > so you can in fact put the locked row ids in the target list. With a > "LIMIT 1" appended would be the perfect way to check out the next queue > item to process... That makes sense. I was thinking of UPDATE ... SKIP LOCKED RETURNING instead, which could be handy in similar situations. -- Craig Ringer