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

The table 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:

SELECT @last_queue_id

Once you’re done with the item, you delete it from the queue:

DELETE FROM queue WHERE id = @last_queue_id AND available = FALSE

The available = FALSE clause isn’t necessary, but I like to keep it just to be extra safe.