I am having some difficulty with MySQL and returning a unique row to a thread. I want it so the thread will search for a row from the table where the bit (see below) is false and only one row is returned. But I don't want the other threads to return the same result should there might be some race condition where the same result is returned to the thread; because the thread will then do lots of processing off the back of this result and I don't want duplication.
Background:
I have a MySQL database that contains 3 columns (id, text, bit).
The id is auto-incremented.
I have a multi-threaded Ruby application that reads, updates and inserts rows into the table.
Pseudo code for the thread is as follows:
select a row from the table where the bit is false
do some processing with the text returned from that row
insert more rows with bit set to false
I have tried a simple test with a multi-threaded script that uses the following:
SELECT id, text FROM table WHERE bit =FALSE LIMIT 1 FOR UPDATE
But each thread returns the same row. I have disabled autocommit as per the recommendation. Since I am omitting any commit I would expect the other threads to have a different result since the row is locked.
Am I missing something or should I be looking at using another method?
Best Answer
You can add another column that tracks whether or not a particular row is 'checked out' by your application. This might be a good place for a stored proc. Other threads would then select the first row where 'bit' is false and 'checked_out' is false.
Another option is to dedicate a single thread to pulling the id's and distributing them to worker threads.
It sounds like you might be implementing a queue, in which case you may also want to research the various queue/messaging systems.
Further options...
If you know you'll always have a fixed number of threads, you can add a mod condition to your query. eg, "where (bit = false) and (id % 4 = 0)". This runs the risk of missing items if one of the threads stops or falls behind.
Yet another thing to look at is perhaps consider only doing your work in a single thread. Your MySQL server will only handle so much, so you should profile to see if you are actually getting more done with multiple threads. So if the threads are just doing basic db read & writes, there may not be an advantage. On the other hand, if what you are doing is processor intensive, something like image processing, the threads will likely help.