How to queue a task in MySQL?

Question

MySQL

New tasks appear in the database.
Several workers once a second check for new tasks and start processing.
How to prevent two workers from taking on the same task?
More precisely, which MySQL locks to set when the worker makes a select?
PS I know about the option with RabbitMQ, but I would like to do without it.

Answer 1

1. Set the proccess field of the double type to 0 in the table by default.
2. In the PHP script, set $process_id = microtime(TRUE) + getmypid(); to get a unique process ID for each script call.
3. UPDATE `task` SET `process` = $process_id WHERE `process` = 0 LIMIT 1
4. SELECT * FROM `task` WHERE `process` = $
process_id one data for processing.

Answer 2

If for one task, then

UPDATE `tasks` 
    SET @id := `id`, `state` = 'in_work', 
        `work_start_time` = NOW(), `worker` = :pid
    WHERE `state` = 'wait'
    ORDER BY `create_time`
    LIMIT 1;
SELECT * FROM `tasks` WHERE `id` = @id;

At the end of processing

UPDATE `tasks` SET `state` = 'done' WHERE `id` = @id;

Periodically check for tasks that are taking too long to process, kill the process by pid and reset the `state` of the task.
PS Of course,@id := `id`

Answer 3

LOCK TABLES at the time of selecting and assigning a worker is a completely working option.