PHP + MySQL Queue

Question

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.

Answer 1

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.

Answer 2

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.