Creating a queue in MySQL isn’t a great idea as highlighted in this article: 5
subtle ways you’re using MySQL as a queue, and why it’ll bite you. Yet it’s
possible to create a relatively-efficient queue as long as you avoid
FOR UPDATE. I had to create one for work a little while ago.
Here’s the schema for such queue:
CREATE TABLE queue ( id INTEGER PRIMARY KEY, available BOOLEAN NOT NULL DEFAULT TRUE ... );
queue is only used to lock items, and mark them as them as done. You
can store data in the
queue table, but I’d recommend to store it in a separate
table to keep the
queue table relatively small.
To lock the next item in the queue:
UPDATE queue SET id = @last_queue_id := id, available = FALSE WHERE available = TRUE ORDER BY id LIMIT 1
The key part is
id = @last_queue_id := id: this will mark the next item with
available = FALSE and set the user variable
@last_queue_id to its ID.
You can then get it with:
Once you’re done with the item, you delete it from the queue:
DELETE FROM queue WHERE id = @last_queue_id AND available = FALSE
available = FALSE clause isn’t necessary, but I like to keep it just to be