[lug] MySQL work queue problem

George Sexton gsexton at mhsoftware.com
Fri Oct 6 16:27:46 MDT 2006



Jason Vallery wrote:
> 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've used this technique when writing my own key generation algorithms. 
In essence you're letting the lock manager of the database handle your 
synchronization/locking issues.

Another approach would be to use your language's synchronization, 
semaphores, mutexes, etc to handle locking to prevent more than one 
thread from updating the database at the same time.
>
> I did notice a weird syntax on the MySQL website that I don't quite 
> understand:
>
> select ... for update;
This is cursor syntax for writing stored procedures. Typically, say with 
Transact-SQL you would do something like:

declare cursor MyCursor as
select field1 from table for update
open cursor MyCursor
while @@FETCH_STATUS<>-1
begin
    fetch next from myCursor into @myVariable

    update current of MyCursor set field1=new value
end
close cursor MyCursor
deallocate cursor MyCursor


>
> -J
>
>
> On 10/6/06, *George Sexton* <gsexton at mhsoftware.com 
> <mailto: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 <http://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 <http://lug.boulder.co.us>
>     port=6667 channel=#colug
>
>
> ------------------------------------------------------------------------
>
> _______________________________________________
> 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/




More information about the LUG mailing list