Queueing with SKIP LOCKED

Posted on By Gougousis Alexandros

In 2018, at last, MySQL (version 8) decided to add support to the famous “SKIP LOCKED” feature. Other RDBMS like Oracle and SQL Server support this feature for more than 15 years and PostgreSQL added support in 2016. If this is the first time you hear about this, it is a flag that tells the engine to ignore (not to wait for and not include in the results) rows that are found locked by another transaction. Among other things that you can do with this feature, is to implement an RDBMS queue. Imagine you have a table like this:

CREATE TABLE queue (
    id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    message VARCHAR(255) NOT NULL COLLATE 'utf8mb4_general_ci',
    PRIMARY KEY (id) USING BTREE
)
COLLATE='utf8mb4_general_ci'
ENGINE=InnoDB;

You push new messages into the queue with statements like that:

INSERT INTO queue (message) VALUES ("…your message payload…");

And you pick up messages, in a FIFO fashion, from the queue like that:

SELECT * FROM queue ORDER BY id LIMIT 1 FOR UPDATE SKIP LOCKED;

Of course, the picking up will probably happen as part of a transaction that process (consumes) the message. So, before the committing of the transaction, a delete statement will also be there to remove the processed message (or a rollback will happen that will release the message lock).

Unfortunately we cannot do both in one statement like Oracle can:
DELETE FROM queue WHERE id = (
SELECT id FROM queue ORDER BY id LIMIT 1
FOR UPDATE SKIP LOCKED
)
RETURNING message;

If the oldest message is already locked by another consumer, it will be skipped and it will move forward to the next available message without wasting any time waiting. Even of the consumer that had locked the first message fails to process it and the message gets released again, it will be picked up by the next consumer since it has the lowest id.

I know that many of you would look down on this feature because it is not shiny and glamorous but it comes in handy as a quick solution when you are building some prototype or as a means of communication between modules of your monolithic application.

If you are looking for more interesting ways to take advantage of the SKIP LOCKED feature, you can look here: https://mysqlserverteam.com/mysql-8-0-1-using-skip-locked-and-nowait-to-handle-hot-rows/