Ignoring a locked row in a MySQL query

Question

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

Each thread must select 100 rows, perform some tasks on each row (not related to the database), then they must remove 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 the select statement in MySQL?

Answer 1

I usually create a process_id column that is NULL by default, and then each thread uses 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; 

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

Hope this helps.

Answer 2

Despite the fact that this is not the best solution, since I do not know how to ignore locked rows, I choose random and try to get a lock.

 START TRANSACTION; SET @v1 =(SELECT myId FROM tests.table WHERE status is NULL LIMIT 1); SELECT * FROM tests.table WHERE myId=@v1 FOR UPDATE; #<- lock 

Setting a small timeout for a transaction, if this row is locked, the transaction is aborted, and I will try another one. If I get a lock, I will handle it. If (failure) this line was blocked, it is processed and the lock is released before my timeout, then I select the line that has already been "processed"! However, I check the field that my processes have set (for example, status): if another transaction of the process is completed OK, this field tells me that the work has already been completed, and I do not process this line again.

Every other possible solution without transactions (for example, setting a different field if the line does not have a status, etc.) can easily provide race conditions and missed processes (for example, one thread dies suddenly, the selected data is still marked, while the transaction expires, see comment here

Hope this helps