Add a lease-expiry field to a SQLite job record

By Batu · English technical notes

Also published in our Blogger archive.

Requeue only after the lease clock expires

The UPDATE requires claimed state and lease_until less than synthetic now, so the row at 100 returns to queued at 101. rowcount and the SELECT confirm one conditional recovery. The strict comparison makes an equal value still valid in this policy.

All workers must use one clock unit and comparison rule. Expiry only permits another attempt; it cannot prove the old worker stopped, so overlapping effects need idempotency or stronger ownership.

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 PRIMARY KEY, state TEXT, lease_until INTEGER)")
    connection.execute("INSERT INTO job VALUES (1, 'claimed', 100)")
    changed = connection.execute("UPDATE job SET state = 'queued' WHERE state = 'claimed' AND lease_until < ?", (101,)).rowcount
    assert changed == 1
    assert connection.execute("SELECT state FROM job").fetchone()[0] == "queued"
    print("expired-lease=requeued")

Expected stdout:

expired-lease=requeued

Sources

- SQLite CREATE TABLE

- SQLite UPDATE

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