mysql - Locking rows for select query?

Question

I have a mysql table (table-A) that contains a list of unique product ids. The app which utilizes this table makes use of multiple threads, each of which selects a table row (top most), uses an API to grab the product data and update it to a different table (table-B). Once done, the thread deletes the corresponding product id row within table-A and selects another one to work on (looping until all rows in table-A have been deleted while table-B has been updated).

How do I prevent my app's threads from accidentally working on the same row from table-A? Is there a way to lock a row from being selected?

Example: The application's thread-1 selects row-1 from table-A. It takes about 10 to 15 seconds to grab and update all the related data into table-B from the API. While this is happening, thread-2 will fire off and check table-A to select a row to work on. In this case, I want only row-1 locked so that it does not thread-2 does not see/read it and instead picks row-2.

Answer 1

Native MySQL locking doesn't provide this functionality. You could use a column to perform your "locks".

Assuming each thread had a unique ID, you could create a column named thread_owner, with default 0.

One thread would grab a row like this:

UPDATE mytable
SET thread_owner = :my_threadID
WHERE thread_owner = 0
LIMIT 1

Then select the row like this (it might return none, if there were no rows to be processed):

SELECT *
FROM mytable
WHERE thread_owner = :my_threadID

Then process it, and finally delete it.

This solution would work on both MyISAM and InnoDB.

However, for InnoDB, it might be slow because each UPDATE statement is trying to lock all rows where thread_owner = 0, and unless you're sure you're locking all rows in the same order each time, it could even cause a deadlock. So, you might try explicitly locking the whole table in your UPDATE statement:

LOCK TABLES mytable WRITE;
UPDATE mytable
SET thread_owner = :my_threadID
WHERE thread_owner = 0
LIMIT 1;
UNLOCK TABLES;

That way, both MyISAM and InnoDB will work the same way.