mysql queries

queue.sql

create table jobs(
  id auto_increment not null primary key,
  message text not null,
  process_id varbinary(255) null default null,
  key jobs_key(process_id) 
);

# queue

insert into jobs(message) values('blah blah');

# dequeue

begin;
select * from jobs where process_id is null order by id asc limit 1;
update jobs set process_id = ? where id = ?; -- whatever i just got
commit;
# return (id, message) to application, cleanup after done

# OR
# queue

UPDATE jobs SET process_id = ? WHERE process_id IS NULL ORDER BY ID ASC LIMIT 1;

# dequeue

SELECT * FROM jobs WHERE process_id = ? [ORDER BY ID LIMIT 1];