Use deterministic ordering when selecting the next queued job

By Batu · English technical notes

Also published in our Blogger archive.

Specify every queue tie-breaker

ORDER BY priority, created, id defines the choice. The rows make id 4 lose priority, id 2 lose creation time, and id 3 lose the final id tie to id 1. LIMIT 1 is therefore not relying on insertion order.

Determinism is not fairness: higher-priority arrivals can starve lower-priority work, and this one-connection example does not model concurrent claims.

Example

import sqlite3
from pathlib import Path
from tempfile import TemporaryDirectory

with TemporaryDirectory() as directory:
    connection = sqlite3.connect(Path(directory) / "queue.db")
    connection.execute("CREATE TABLE job(id INTEGER, state TEXT, priority INTEGER, created INTEGER)")
    connection.executemany("INSERT INTO job VALUES (?, 'queued', ?, ?)", [(4, 6, 1), (2, 5, 20), (3, 5, 10), (1, 5, 10)])
    next_id = connection.execute("SELECT id FROM job WHERE state = 'queued' ORDER BY priority, created, id LIMIT 1").fetchone()[0]
    assert next_id == 1
    print("next=id-1 order=priority,created,id")

Expected stdout:

next=id-1 order=priority,created,id

Sources

- SQLite ORDER BY

Prepared with AI assistance. The example uses synthetic data; its stated limits apply.