[lug] MySQL work queue problem

[Jason Vallery]

Hey All,

I know this isn't exactly the best place to ask this question, but I know
several of you out there do a bit of work with MySQL so I am sure someone
has the answer.

I've got an application that uses a MySQL table (Using the MyISAM engine) to
store a work queue for several worker threads.  Basically the idea is that I
have a number of threads that constantly monitor the table.  There is a
status column in the table that contains an integer.  There are four
possible values:

0 =  Not processed
1 =  Processed
2 =  In process
9 =  Processing failed

The worker threads monitor the table for any rows that have a status of 0.
They grab the oldest record with a status of 0 and then update the status to
2 "in process".  The thread then completes the work and updates the row to 1
"processed" unless it fails.

The problem is however that because I have so many threads I occasionally
run into the situation where the SELECT statements are executed so close
together that the UPDATE statement to change the status from 0 to 2 doesn't
happen before another thread gets the same job.  This has lead to several
issues for me (which I don't really need to get into).  I need to come up
with some way that MySQL can hand off the row to my thread and immediately
update the status column to 2 so that the same row doesn't get assigned to
more than one thread.

I'm pretty sure I could do this if I changed the table engine to InnoDB
because it directly supports row level locking.  This introduces several
other issues for my app though (mainly around performance) so I would prefer
to stick with MyISAM.

Thanks all in advance for any suggestions.

-Jason
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.lug.boulder.co.us/pipermail/lug/attachments/20061006/3a6b93fb/attachment.html>