[lug] MySQL work queue problem
jason at vallery.net
Fri Oct 6 14:35:39 MDT 2006
Thanks, that is an interesting way to approach the problem. I'd initially
hoped that I could have the select statement do the update, but this should
work equally well.
I did notice a weird syntax on the MySQL website that I don't quite
select ... for update;
On 10/6/06, George Sexton <gsexton at mhsoftware.com> wrote:
> The problem is in your where clause. You need to use something like:
> update table set status_field=1 where status_field=0 and rec_id=34
> if rowcount_updated()>0
> then this thread was the one that got it.
> do the processing.
> end if
> If a second thread tries the update statement, the row count will be 0
> because the status field has already been changed. That's assuming that
> MySQL can actually atomically update records...
> Jason Vallery wrote:
> > 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
> > ------------------------------------------------------------------------
> > _______________________________________________
> > Web Page: http://lug.boulder.co.us
> > Mailing List: http://lists.lug.boulder.co.us/mailman/listinfo/lug
> > Join us on IRC: lug.boulder.co.us port=6667 channel=#colug
> George Sexton
> MH Software, Inc.
> Voice: +1 303 438 9585
> URL: http://www.mhsoftware.com/
> Web Page: http://lug.boulder.co.us
> Mailing List: http://lists.lug.boulder.co.us/mailman/listinfo/lug
> Join us on IRC: lug.boulder.co.us port=6667 channel=#colug
-------------- next part --------------
An HTML attachment was scrubbed...
More information about the LUG