[lug] MySQL work queue problem
jason at vallery.net
Fri Oct 6 12:55:32 MDT 2006
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
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.
-------------- next part --------------
An HTML attachment was scrubbed...
More information about the LUG