The research question
When a process stops halfway through, what do we actually know? Automation becomes difficult at the boundary between an intention and a confirmed result. A timeout might mean an operation never happened—or that it happened and its acknowledgement was lost.
This lab models two situations. One task fails before doing any work and can be retried. Another has an uncertain outcome and is routed to review instead of being blindly repeated. We use a local SQLite database to keep state across worker passes. No email, external API, or order is sent.
Time: approximately 35 minutes. You need: standard Python with sqlite3, normally included in CPython. The script creates its database in a temporary directory and removes it afterward, so each run starts clean.
Step 1 — Choose states that mean something
Use pending for work waiting to start, running for claimed work, done only after a local receipt is recorded, and review when the outcome cannot be established safely. Keep an attempt count. Give each task a stable key so a second worker pass refers to the same task instead of inventing a new one.
This example has one worker and a database transaction around each state update. It is not a distributed queue implementation. The goal is to understand state and evidence before introducing parallel workers.
Step 2 — Run two worker passes
Save the following script as lab_06.py.
import sqlite3
import tempfile
from pathlib import Path
with tempfile.TemporaryDirectory() as directory:
connection = sqlite3.connect(Path(directory) / "lab.sqlite3")
connection.executescript("""
CREATE TABLE jobs (
id TEXT PRIMARY KEY,
status TEXT NOT NULL,
attempts INTEGER NOT NULL DEFAULT 0
);
CREATE TABLE receipts (job_id TEXT PRIMARY KEY, result TEXT NOT NULL);
""")
with connection:
connection.executemany(
"INSERT INTO jobs(id, status) VALUES (?, 'pending')",
[("retryable",), ("uncertain",)],
)
def worker_pass():
for job_id, attempts in connection.execute(
"SELECT id, attempts FROM jobs WHERE status='pending' ORDER BY id"
).fetchall():
attempt = attempts + 1
with connection:
connection.execute(
"UPDATE jobs SET status='running', attempts=? WHERE id=?",
(attempt, job_id),
)
if job_id == "retryable" and attempt == 1:
# Simulated failure BEFORE any result was created.
with connection:
connection.execute(
"UPDATE jobs SET status='pending' WHERE id=?", (job_id,)
)
print(f"{job_id}: retry_scheduled attempt={attempt}")
elif job_id == "uncertain":
# We cannot establish whether an external effect happened.
with connection:
connection.execute(
"UPDATE jobs SET status='review' WHERE id=?", (job_id,)
)
print(f"{job_id}: review_required attempt={attempt}")
else:
with connection:
connection.execute(
"INSERT INTO receipts VALUES (?, ?)", (job_id, "paper_result")
)
connection.execute(
"UPDATE jobs SET status='done' WHERE id=?", (job_id,)
)
print(f"{job_id}: done attempt={attempt}")
worker_pass()
worker_pass()
worker_pass() # A repeated pass must not duplicate the completed result.
count = connection.execute("SELECT COUNT(*) FROM receipts").fetchone()[0]
assert count == 1
assert connection.execute(
"SELECT status FROM jobs WHERE id='uncertain'"
).fetchone()[0] == "review"
print(f"receipts={count} duplicate_receipts=0 review_jobs=1")
connection.close()
Expected output:
retryable: retry_scheduled attempt=1
uncertain: review_required attempt=1
retryable: done attempt=2
receipts=1 duplicate_receipts=0 review_jobs=1
Step 3 — Explain the receipt boundary
The receipt and the local done update are in the same transaction. They either commit together or do not. This is a property of the local database operation in our example. It does not make an external API call exactly-once.
If a real provider accepts a request and the process crashes before recording the response, a local unique key cannot undo the provider's action. You need the provider's supported idempotency mechanism, a way to reconcile the result, or explicit review. The right recovery action depends on what is known, not just how many seconds have elapsed.
Step 4 — Design a useful status report
Write down the fields you would want during an incident: task ID, source version, state, attempt count, last change time, safe error category, and receipt identifier if one exists. Avoid putting access tokens, private source documents, or complete customer payloads into an error log.
For a real background service, also define maximum attempts, backoff, a heartbeat, and an alert destination. A process that is running but has made no progress may still be unhealthy. An automatic retry loop without a limit is not a recovery policy.
Troubleshooting
If SQLite is unavailable, check that your Python distribution includes the standard sqlite3 module. A locked-database error in a modified experiment may mean you added a second connection or left a transaction open. Do not remove the receipt's primary key to make duplicate inserts succeed; that constraint is part of the experiment.
Evidence and limits
We proved that this single-process, local example skips completed work on later passes and distinguishes uncertainty from a known pre-action failure. We did not test worker crashes, parallel claims, provider reconciliation, long-lived storage, or production queue guarantees. These are specific next experiments, not assumptions to hide inside the word “reliable.”
Completion check: Explain why uncertain has one attempt and remains in review while retryable has two attempts and one receipt.
Next edition: Publish a research report someone can reproduce.
Primary reference
- Python SQLite documentation covers transactions, parameter binding, connection context managers, and explicit connection closure. The job-state policy above is an original teaching example.