0

I want to apply a parallel and concurrent process for each raw of a table, and each process must take a unique raw.
I have a mysql table with a default Null field value named trigger. At the begining of each parallel process, the process selects a Null raw with:

SELECT table_id from table where trigger is Null limit 1

and then immediatly, in a second request, the process do an update :

UPDATE table set trigger = 1 where table_id = [#previous_select_table_id]

With that process , normally, each raw will be different for each process. The issue I have, is sometimes, during the time between the two requests (select and update), two or more processes can select the same empty raw because they don't have the time to update the raw and set value. Do you have any ideas how to do a select and an update in the same query or other ideas, to be sure a raw can't be selected during the same time ? I use python. Thanks a lot !

Patrice G
  • 89
  • 5
  • I answered nearly the same question earlier today: https://stackoverflow.com/q/62745778/20860 – Bill Karwin Jul 06 '20 at 04:56
  • I saw your similar question. The Danblack's answer is very nice, because it's always working; we don't have to double check if the process is unic. I didn't know start transasction, it's excatly what I was looking for, to merge at the same times several requests. I launched this method, and absolutly no doubles ! Thank you Danblack ! – Patrice G Jul 06 '20 at 08:43

1 Answers1

2

The first problem is these are two independent transactions. So the first is to make them into one.

START TRANSACTION
SELECT table_id from table where trigger is Null limit 1 FOR UPDATE
[do stuff]
UPDATE table set trigger = 1 where table_id = [#previous_select_table_id]
COMMIT

The second aspect is this form of select of unprocessed data and remove it from consideration is a queue. Database implementing queues isn't a natural thing for them. Use a message queue like RabbitMQ or otherwise. Even if all you put on the query is a table_id.

Also be careful of using reserved words like trigger for table columns.

danblack
  • 12,130
  • 2
  • 22
  • 41