I need a simple table that acts as a Queue. My MySQL server restriction is I can't use InnoDB tables, only MyISAM.
Clients/workers will work at the same time and they will need to receive differents jobs each time.
My idea is to do the following (pseudo-code):
$job <- SELECT * FROM queue ORDER BY last_pop ASC LIMIT 1;
UPDATE queue SET last_pop WHERE id = $job->id
return $job
I had tried table lock and "GET_LOCK" but nothing happends, workers sometimes receives same jobs.
You need to turn your ordering around so there is no timing window.
Consumer POP (each consumer has a unique $consumer_id)
Update queue
set last_pop = '$consumer_id'
where last_pop is null
order by id limit 1;
$job =
Select * from queue
where last_pop = '$consumer_id'
order by id desc
limit 1;
Supplier PUSH
insert into queue
(id, last_pop, ...)
values
(NULL, NULL, ...);
The queue is ordered in time by the id column and assigned upon POP by to the consumer_id.
last_pop cannot be NULL after UPDATE1.UPDATE and before the SELECT will result in an orphaned item. START TRANSACTION and COMMIT are necessary to avoid that risk.
Just for information, there's another option that is using Gearman instead of a table to make the queue: Rasmus Lerdorf wrote a very nice article about it.
Oleg,
The solution is correct. $consumer_id must be a unique identifier for the processor. If you had a couple cron jobs on one machine, for example, you could use their pid as the consumer ID .
The UPDATE is atomic, so it marks exactly one row in the queue as being consumed by your ID.
For some applications I also have a status field for finished, so that if last_pop's consumer_id is set, but the finished flag is not set and the job is older than X, it can be marked to be restarted.
