[lug] MySQL work queue problem

[Jason Vallery]

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
understand:

select ... for update;

-J


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...
URL: <http://lists.lug.boulder.co.us/pipermail/lug/attachments/20061006/674b4a18/attachment.html>