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)

On Jul 8, 2008, at 7:49 PM, Goldeneye Solutions Information wrote:
> 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)

Patches welcome. :) You could likely use the patch that added NOWAIT
as a template and be pretty close to something. You should bounce the
idea off of -hackers first if you want to do this.

If you're not looking to hack the backend code, I'd suggest doing a
random OFFSET in your select. You'd need to first do a select to pick
a row, then try to actually lock it. You could also have an old
record stick around a long time that way, so I'd suggest forcibly
trying OFFSET 0 on some non-trivial number of attempts (say 10%). You
might be able to achieve the same effect by applying a function to
your random number that pushes it towards 0.
--
Decibel!, aka Jim C. Nasby, Database Architect decibel(at)decibel(dot)org
Give your computer some brain candy! www.distributed.net Team #1828