Skip to content
AITroveRead. Build. Understand.
Reading list
Make this comfortable

Python SQLite outbox leases: stale workers must not acknowledge a newer claim

Last updated: 30 Sept 20264 min read
tutorial
IntermediateBy AITrove Editorial

An outbox lease temporarily assigns a pending event to one worker and uses a claim token to distinguish successive owners.

Download Python source kit

Operation contract

The claim operation begins an immediate transaction, selects an unacknowledged expired or unclaimed row and updates it with a new token and deadline before committing. A second connection sees no eligible row while that lease is active. At the deadline a different worker can claim it with a different token. Acknowledgement matches both event ID and current token, so the first worker cannot complete a newer worker’s claim. Claim acquisition is serialized by this SQLite transaction; the external side effect is not inside it.

Failure and ownership boundary

The fixture uses an owned temporary database, two real connections and a caller-supplied nondecreasing test clock. A production deployment needs an agreed time source, lock retry/deadline handling, lease renewal and recipient deduplication. The Python SQLite outbox project: commit a receipt and its pending event together creates intent; a lease governs workers; Python SQLite job project: reject conflicting replays by request identity governs repeated external delivery. None alone gives exactly-once effects.

Working program

python
import sqlite3
import tempfile
from pathlib import Path

def claim(connection, now, token):
    if type(now) is not int or not 0 <= now <= 1000000 or type(token) is not str or not token.isascii() or not token.isalnum() or not 1 <= len(token) <= 16:
        raise ValueError("bounded clock and fresh token required")
    connection.execute("BEGIN IMMEDIATE")
    try:
        row = connection.execute("SELECT event_id FROM outbox WHERE done=0 AND lease_until<=? ORDER BY event_id LIMIT 1", (now,)).fetchone()
        if row:
            connection.execute("UPDATE outbox SET token=?,lease_until=? WHERE event_id=?", (token, now + 10, row[0]))
        connection.commit()
        return row[0] if row else None
    except BaseException:
        connection.rollback()
        raise

def acknowledge(connection, event_id, token):
    cursor = connection.execute("UPDATE outbox SET done=1 WHERE event_id=? AND token=? AND done=0", (event_id, token))
    return cursor.rowcount == 1

with tempfile.TemporaryDirectory() as directory:
    path = Path(directory) / "owned.sqlite3"
    first = sqlite3.connect(path, isolation_level=None, timeout=0)
    second = sqlite3.connect(path, isolation_level=None, timeout=0)
    try:
        first.execute("CREATE TABLE outbox(event_id INTEGER PRIMARY KEY,token TEXT,lease_until INTEGER NOT NULL DEFAULT 0,done INTEGER NOT NULL DEFAULT 0)")
        first.execute("INSERT INTO outbox(event_id) VALUES(41)")
        print("first claim:", claim(first, 100, "claimA"))
        print("active lease:", claim(second, 109, "claimB"))
        print("reclaimed:", claim(second, 110, "claimB"))
        print("stale ack:", acknowledge(first, 41, "claimA"))
        print("current ack:", acknowledge(second, 41, "claimB"))
        print("after ack:", claim(first, 120, "claimC"))
    finally:
        second.close()
        first.close()

Output

Output
first claim: 41
active lease: None
reclaimed: 41
stale ack: False
current ack: True
after ack: None

Costs and limits

The teaching table contains one row. A large outbox needs measured query plans, indexes, retention and contention behavior. Tokens must be unique per claim and never reused for the same event: the parameter validator checks spelling, not global uniqueness. A late worker may still deliver after expiry, so recipients need their own event identity policy.

Common Mistakes

  • Reuse of a claim token defeats stale-owner rejection.
  • Lease expiry does not stop the old worker from producing an external effect.

Connected lessons

Python SQLite outbox project: commit a receipt and its pending event together, Python SQLite writer contention: reject a busy transaction before changing state, Python SQLite job project: reject conflicting replays by request identity, Spring transaction events: run a listener after commit without claiming durability.

Follow the ownership and update boundary

Python SQLite lease renewal: reject stale tokens and exact-expiry ownership.

python
leased-outbox-project
Storage details