Thread: Feature: FOR UPDATE SKIP LOCKED

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@decibel.org
Give your computer some brain candy! www.distributed.net Team #1828