What's the best way of implementing a messaging queue table in mysql

Question

It's probably the tenth time I'm implementing something like this, and I've never been 100% happy about solutions I came up with.

The reason using mysql table instead of a "proper" messaging system is attractive is primarily because most application already use some relational database for other stuff (which tends to be mysql for most of the stuff I've been doing), while very few applications use a messaging system. Also - relational databases have very strong ACID properties, while messaging systems often don't.

The first idea is to use:

 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)  ); 

And then enqueue looks like this:

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

And dequeue looks like this:

 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 

Table and enqueue look nice, but dequeue kinda bothers me. How likely is it to rollback? Or to get blocked? What keys I should use to make it O(1)-ish?

Or is there any better solution that what I'm doing?

Answer 1

Your dequeue could be more concise. Rather than relying on the transaction rollback, you could do it in one atomic statement without an explicit transaction:

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

Then you can pull jobs with (brackets [] mean optional, depending on your particulars):

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