Re: using database for queuing operations?

> So you:
>
> 1. select for update, with the criteria outlined
> 2. Check the state (again) to see of we had that particular race condition.
> 3. If already processed or in processing, somebody else must already be
> working on it....go back to step 1
> 4, change the state
> 5. process the image
> 6. delete.
> 7 go to step 1.

You can also rely on the old trick that, having selected min(serial) you
know that:

update nameq set state = 'processing'
where serial = xxx and state = 'unprocessed';

Will execute atomically and will set a row count of 0 or 1. You still have
some racing going on with the selects, but only 1 process ever gets hold of
a row to process. I've done similar things where tests showed that
collisions would be relatively rare--the following could really be bad if
processing didn't take "much time" and you had "a lot" of processes
extracting queue items. Excuse the atrocious mix of pseudo-sql and pseudo-C
and commentary:

select serial from nameq
where state = 'unprocessed' order by serial limit 10;
for( i = 0; i < 10 && i < actual num rows selected; ++i )
{
curserial = currow.seral;
update nameq set state = 'processing'
where serial = curserial and state = 'unprocessed';
if( rowcount == 1 )
{
process row;
update nameq set state = 'processed' where serial = curserial;
break;
}
else
{
pause some brief random time to prevent lock-step race
fetch next row
}
}

--
Scott Ribe
scott_ribe(at)killerbytes(dot)com
http://www.killerbytes.com/
(303) 665-7007 voice