Ignoring locked row in a MySQL query

Question

I have one table that is read at the same time by different threads.

Each thread must select 100 rows, execute some tasks on each row (unrelated to the database) then they must delete the selected row from the table.

rows are selected using this query:

SELECT id FROM table_name FOR UPDATE;

My question is: How can I ignore (or skip) rows that were previously locked using a select statement in MySQL ?

Answer 1

I typically create a process_id column that is default NULL and then have each thread use a unique identifier to do the following:

UPDATE table_name SET process_id = #{process.id} WHERE process_id IS NULL LIMIT 100;

SELECT id FROM table_name WHERE process_id = #{process.id} FOR UPDATE;

That ensures that each thread selects a unique set of rows from the table.

Hope this helps.