----------------------------------------------------------------------- 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)