EXPLAIN EXTENDED

Imagine that we are developing an email server that keeps the outgoing messages in a database.

The server is always busy sending the messages if the outgoing queue is not empty. It may have up to 10 connections at once.

If the message is failed to be sent, it’s reinserted to the end of the queue until the retry count is exceeded.

As soon as all messages from a batch succeed or fail to be sent, next 10 messages from the queue are fetched.

How do we write a query to fetch a batch of messages from the queue?

This all can be done in a single query run in a loop. No range checking, no reinserting.

Let’s create the sample table:

Table creation details

CREATE TABLE t_queue (
        id INT NOT NULL PRIMARY KEY,
        header VARCHAR(50) NOT NULL,
        attempt INT NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE filler (
        id INT NOT NULL PRIMARY KEY AUTO_INCREMENT
) ENGINE=Memory;

DELIMITER $$

CREATE PROCEDURE prc_filler(cnt INT)
BEGIN
        DECLARE _cnt INT;
        SET _cnt = 1;
        WHILE _cnt <= cnt DO
                INSERT
                INTO    filler
                SELECT  _cnt;
                SET _cnt = _cnt + 1;
        END WHILE;
END
$$

DELIMITER ;

START TRANSACTION;
CALL prc_filler(20);
COMMIT;

INSERT
INTO    t_queue (id, header, attempt)
SELECT  id, CONCAT('Message ', id), 0
FROM    filler;

SELECT  *
FROM    t_queue

We have a queue of 20 fresh messages, none of them having been attempted to send.

Let’s select a batch of the messages to be sent. We need the messages for which the retry counter had not been exceeded, i. e. those having attempt < 3.

We initialize a session variable:

SET @r := -1;

and issue the following query:

SELECT  q.*
FROM    (
        SELECT  @r AS _current,
                @r := COALESCE(
                (
                SELECT  id
                FROM    t_queue
                WHERE   id > _current
                        AND attempt < 3
                ORDER BY
                        id
                LIMIT 1
                ),
                (
                SELECT  MIN(id)
                FROM    t_queue
                WHERE   attempt < 3
                )
                ) AS nid
        FROM    t_queue
        LIMIT 10
        ) ids, t_queue q
WHERE   id = nid
FOR UPDATE

This selects a list of first 10 messages.

Now, if we just reissue the exactly same query, we get:

Exactly same query, but the results are different.

Now let's assume that messages 5, 10 and 15 failed to be sent and update their attempts to 3's:

UPDATE t_queue
SET    attempt = 3
WHERE  id IN (5, 10, 15)

, and rerun the batch query. We get the following result:

The query has wrapped around automatically.

If we repeat the query again, we will get:

The query makes a rollover right inside the resultset: it selects 1 after 20.

So we can basically just run the query inside an endless loop without having to bother about rewinding, rollovers etc. The query will just select 10 next messages again and again.

So how does it work?

The principle is simple:

1. We use t_queue in the inner subquery as a dummy rowsource, limiting rows as necessary
2. We keep current id in the session variable @r
3. For each row in the dummy rowset, we get the id next to @r that satisfies the conditions (attempt < 3)
4. If there is no next id for a given @r, we select the minimal id satisfying the conditions, thus making a rollover
5. Finally, we join t_queue on this next id and mark it FOR UPDATE

As the intermediate result is stored in @r between the queries, we assign @r only once (right after connecting), and just run the batch query over and over again.

If this article was useful to you, please put some effort into sharing it with the others.
I would really appreciate it.