Record a retry count without turning every error into a retry

By Batu · English technical notes

Also published in our Blogger archive.

Put retry eligibility in the UPDATE predicate

The statement increments retries only for rows that are failed and retryable. rowcount is one, and the final rows show the permanent failure stayed unchanged. Classification therefore controls the database mutation instead of merely a log line.

Repeated execution keeps incrementing the eligible row. Add a maximum attempt condition and next-attempt schedule when retries must stop or be delayed.

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, retries INTEGER, retryable INTEGER)")
    connection.executemany("INSERT INTO job VALUES (?, 'failed', 0, ?)", [(1, 1), (2, 0)])
    changed = connection.execute("UPDATE job SET retries = retries + 1 WHERE state = 'failed' AND retryable = 1").rowcount
    rows = connection.execute("SELECT id, retries FROM job ORDER BY id").fetchall()
    assert changed == 1
    assert rows == [(1, 1), (2, 0)]
    print("retryable=incremented permanent=unchanged")

Expected stdout:

retryable=incremented permanent=unchanged

Sources

- SQLite UPDATE

- sqlite3.Cursor.rowcount

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