MySQL based message queue

Question

This table is used as a message queue with multiple readers and writers:

CREATE TABLE IF NOT EXISTS MyQueue(
id CHAR(36) PRIMARY KEY NOT NULL, 
at DateTime NOT NULL, 
message TEXT NOT NULL, 
INDEX(at ASC));

I try to prevent double reading of the messages by using update locks:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT message FROM MyQueue WHERE at <= @today ORDER BY at ASC LIMIT 1 FOR UPDATE;
UPDATE MyQueue SET at=@nextDay WHERE at <= @today ORDER BY at ASC LIMIT 1;

(Idea is to remove message latter today after successful processing by one of the readers or reprocess tomorrow.)

But it looks like that some double reads are still possible. What to pay attention to?

1) Your DDL does not guarantee that selected record and updated record is the same - at field is not unique. 2) Perform this action as one query, not 2 separate ones. — Akina
– Akina, Commented Mar 25, 2020 at 7:39

Dmitry Nogin · Accepted Answer · 2020-03-25 08:43:42Z

1

It helped to wrap the query into the Tx:

START TRANSACTION;
SELECT id, at, message FROM {Table} WHERE at <= @now ORDER BY at ASC LIMIT 1 FOR UPDATE;
UPDATE {Table} SET at=@timeout WHERE at <= @now ORDER BY at ASC LIMIT 1;
COMMIT;

answered Mar 25, 2020 at 8:43

Dmitry Nogin

3,7601 gold badge22 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MySQL based message queue

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related