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?