EXPLAIN EXTENDED

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;
&#91;/sourcecode&#93;
</div>

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
&#91;/sourcecode&#93;

<div class="terminal">
<table class="terminal">
<tr><th>id</th><th>header</th><th>attempt</th></tr>
<tr><td class="integer">1</td><td class="varchar">Message 1</td><td class="integer">0</td></tr>
<tr><td class="integer">2</td><td class="varchar">Message 2</td><td class="integer">0</td></tr>
<tr><td class="integer">3</td><td class="varchar">Message 3</td><td class="integer">0</td></tr>
<tr><td class="integer">4</td><td class="varchar">Message 4</td><td class="integer">0</td></tr>
<tr><td class="integer">5</td><td class="varchar">Message 5</td><td class="integer">0</td></tr>
<tr><td class="integer">6</td><td class="varchar">Message 6</td><td class="integer">0</td></tr>
<tr><td class="integer">7</td><td class="varchar">Message 7</td><td class="integer">0</td></tr>
<tr><td class="integer">8</td><td class="varchar">Message 8</td><td class="integer">0</td></tr>
<tr><td class="integer">9</td><td class="varchar">Message 9</td><td class="integer">0</td></tr>
<tr class="statusbar"><td colspan="100">9 rows fetched in 0.0003s (0.0067s)</td></tr>
</table>
</div>

This selects a list of first <strong>10</strong> messages.

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

<div class="terminal">
<table class="terminal">
<tr><th>id</th><th>header</th><th>attempt</th></tr>
<tr><td class="integer">11</td><td class="varchar">Message 11</td><td class="integer">0</td></tr>
<tr><td class="integer">12</td><td class="varchar">Message 12</td><td class="integer">0</td></tr>
<tr><td class="integer">13</td><td class="varchar">Message 13</td><td class="integer">0</td></tr>
<tr><td class="integer">14</td><td class="varchar">Message 14</td><td class="integer">0</td></tr>
<tr><td class="integer">15</td><td class="varchar">Message 15</td><td class="integer">0</td></tr>
<tr><td class="integer">16</td><td class="varchar">Message 16</td><td class="integer">0</td></tr>
<tr><td class="integer">17</td><td class="varchar">Message 17</td><td class="integer">0</td></tr>
<tr><td class="integer">18</td><td class="varchar">Message 18</td><td class="integer">0</td></tr>
<tr><td class="integer">19</td><td class="varchar">Message 19</td><td class="integer">0</td></tr>
<tr><td class="integer">20</td><td class="varchar">Message 20</td><td class="integer">0</td></tr>
<tr class="statusbar"><td colspan="100">10 rows fetched in 0.0004s (0.0068s)</td></tr>
</table>
</div>

Exactly same query, but the results are different.

Now let's assume that messages <strong>5</strong>, <strong>10</strong> and <strong>15</strong> failed to be sent and update their attempts to <code>3</code>'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.