maria-developers team mailing list archive

[worklog-noreply@xxxxxxxxxxxx]

-----------------------------------------------------------------------
                              WORKLOG TASK
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
TASK...........: SKIP LOCKED
CREATION DATE..: Tue, 07 Sep 2010, 19:52
SUPERVISOR.....: 
IMPLEMENTOR....: 
COPIES TO......: 
CATEGORY.......: Server-RawIdeaBin
TASK ID........: 140 (http://askmonty.org/worklog/?tid=140)
VERSION........: WorkLog-4.0
STATUS.........: Un-Assigned
PRIORITY.......: 60
WORKED HOURS...: 0
ESTIMATE.......: 0 (hours remain)
ORIG. ESTIMATE.: 0

PROGRESS NOTES:



DESCRIPTION:

There are times when parallel thread scalability can be improved with SELECT
.... FOR UPDATE SKIP LOCKED. This feature, which is part of Oracle's RDBMS
implementation, prevents all the threads from being held up by a single thread
that has locked a row.

A perfect example is a queue with multiple consumers. If the queue has the
following columns: ID int autoincrement, State varchar(10), Work (varchar 255).
A consumer of the queue would run the following pseudocode to grab a queue
entry, reserve it, do the work, and delete the entry after the work completes:

begin
select ID, Work from Queue order by id limit 1 for update skip locked
<do work>
delete from Queue where ID=<ID from select>
commit

Without SKIP LOCKED the pseudocode has to be

begin
select ID, State, Work from Queue where State='Waiting' order by id limit 1 for
update skip locked
update Queue set State='Working' where ID=<ID from select>
commit
# This commit is needed to release any other consumers waiting for me to release
the lock
begin
#This could take quite a while
<do work>
delete from Queue where ID=<ID from select>
commit

Without the SKIP LOCKED, only once consumer thread at a time can be running from
the initial select to the first commit. This limits the scalability of the
queue. With SKIP LOCKED none of the consumer threads will be waiting for a lock
to be released by another consumer thread.

Anker


ESTIMATED WORK TIME

ESTIMATED COMPLETION DATE
-----------------------------------------------------------------------
WorkLog (v4.0.0)