2

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?

  • You need the MySql Equivalent of TSQL's "READPAST" .. Unfortunately my google-foo is failing me – Morons Aug 27 '13 at 20:15
  • When is the bit set to true? – Mike Aug 30 '17 at 22:49
1

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.

  • A message queue would be a sensible approach. I was wondering if, when implemented I might have problems with multiple threads making the same query repeatedly and slowing the server down. This should help mitigate that. As a side note, the bit field was supposed to be "checked out" field. – Matt S Aug 27 '13 at 20:35
  • I dont think a true/false can handle both a selected and checked out status as there are 3 states - unprocessed, being processed, and processing done. Otherwise if the server goes down, you may miss items in the queue if they are prematurely marked as completed. Thus separating them into two columns, one for processing state, and for checked out state. – GrandmasterB Aug 27 '13 at 21:10
  • see edited answer for other approaches – GrandmasterB Aug 27 '13 at 21:15
  • I thought about the single thread approach but I don't think it is suitable because the processing the thread is required to do requires access to Internet resources where the latency is unknown therefore the thread could be waiting some considerable time. There was also a misunderstanding over the "checked_out", I didn't mean there were three states but I also misunderstood your comment. I understand now what you mean about a "checked out" and a "processed" flag. – Matt S Aug 28 '13 at 6:59
0

Here is an approach that might help you... separate your concerns...

You should have a separate Supervisor class that controls data reads. The database is for data-storage, you will find it simpler to let the application worry about every thing else.

The Supervisor class is a singleton and is responsible for managing which records have and have not been processed, and issuing unprocessed records to the worker threads. The Supervisor has the following methods that can only be accessed by one thread at a time:

  1. Get Unprocessed Record
  2. Create Record

GetUnproccessedRecord reads the next item from an internal queue object and returns it to the caller. When the queue is empty, query the database for records inserted after the last query, then updates its last query time variable.

When a thread has finished processing a record, it calls the supervisor's CreateRecord method. The supervisor can then insert it into the DB immediately or, put it in a list for batch inserts for better performance. The supervisor will also place the record in its unprocessed record queue. The worker thread now calls GetUnproccessedRecord record and the cycle repeats.

Unburden your database, let your application code do the heavy lifting and synchronization; your application was made by you, so it knows best how to manage record synchronization.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.