[lug] MySQL work queue problem

[George Sexton]

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/