PHP + MySQL Queue

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 开发者_StackOverflow中文版* 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.

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.