A durable idempotency key in one SQLite transaction
Use a SQLite key, payload digest and counter in one transaction to demonstrate retries, conflicts and persistence across reopen.
What the key promises locally
A retry can happen when a caller does not learn whether an earlier request completed. An idempotency key lets the application recognize a repeated operation and return its stored result instead of applying the same local effect again. In this example, the effect is incrementing a SQLite counter; an operations table records the key, a digest of the payload and the result [1].
The demonstration is deliberately scoped to one SQLite database. It illustrates a transaction that records the operation and its counter change together. It does not provide exactly-once delivery across a network, nor does it make unrelated remote effects transactional.
Record identity and reject mismatches
The payload is hashed with SHA-256, and the key is the primary key in the operations table. On the first use of a key, the transaction increments the counter and stores the digest with the resulting counter value. On a retry, the saved digest is compared with the new digest. A match returns the prior result; a mismatch raises ValueError.
Rejecting a changed payload prevents accidental reuse of one key for two different requested operations. The digest is a compact identity check for the bytes supplied by this example. An application must define canonical payload bytes if equivalent requests can be serialized in multiple ways.
"""Python 3.10+ and SQLite 3.24+; run the local transaction tests."""
import hashlib
from pathlib import Path
import sqlite3
import tempfile
import unittest
def connect(path):
db = sqlite3.connect(path)
db.execute("CREATE TABLE IF NOT EXISTS operations (key TEXT PRIMARY KEY, digest TEXT NOT NULL, result INTEGER NOT NULL)")
db.execute("CREATE TABLE IF NOT EXISTS counter (id INTEGER PRIMARY KEY CHECK(id=1), value INTEGER NOT NULL)")
db.execute("INSERT OR IGNORE INTO counter VALUES (1, 0)")
db.commit()
return db
def apply(db, key, payload):
digest = hashlib.sha256(payload).hexdigest()
try:
db.execute("BEGIN IMMEDIATE")
old = db.execute("SELECT digest, result FROM operations WHERE key=?", (key,)).fetchone()
if old:
if old[0] != digest:
raise ValueError("key reused with different payload")
result = old[1]
else:
db.execute("UPDATE counter SET value=value+1 WHERE id=1")
result = db.execute("SELECT value FROM counter WHERE id=1").fetchone()[0]
db.execute("INSERT INTO operations VALUES (?, ?, ?)", (key, digest, result))
db.commit()
return result
except Exception:
db.rollback()
raise
class ReplayTests(unittest.TestCase):
def test_replay_conflict_and_reopen(self):
with tempfile.TemporaryDirectory() as folder:
path = Path(folder) / "operations.sqlite3"
db = connect(path)
self.assertEqual(apply(db, "a", b"first"), 1)
self.assertEqual(apply(db, "a", b"first"), 1)
with self.assertRaises(ValueError):
apply(db, "a", b"changed")
db.close()
db = connect(path)
self.assertEqual(apply(db, "a", b"first"), 1)
self.assertEqual(apply(db, "b", b"second"), 2)
db.close()
if __name__ == "__main__":
unittest.main()
Keep the local effect and record together
BEGIN IMMEDIATE starts a write transaction before the operation checks or changes the records. The counter update and insertion of the key result are committed together. If any exception occurs, the code rolls back and re-raises it. SQLite transaction handling is what makes those database changes atomic within this database [1].
The test confirms that a repeated key and payload returns the original result, that the same key with changed bytes is rejected, and that closing and reopening the database preserves the prior result. It then uses a second key to show the counter advances for a distinct operation. The test uses a temporary database file and checks that the stored result survives reopening.
Operational boundaries
A real service must choose key scope carefully. Keys may need to be scoped by account, endpoint or operation type so one caller cannot collide with another caller’s request. Authorization must be checked independently; possessing a key should not grant access to another user’s stored result. Retention also matters: deleting old operation records can allow a delayed retry to apply the effect again, while retaining them indefinitely increases storage use.
Concurrent writers may contend for SQLite’s write lock. The connection’s timeout and the application’s retry behavior affect how such contention appears to callers. The example does not configure a timeout policy or exercise competing processes. Most importantly, a database transaction cannot include a payment provider, email server or other remote side effect. Those need their own coordination and reconciliation design; this code does not claim exactly-once delivery.
Run the local checks
Download the example as what-does-idempotency-mean-in-a-network-protocol.py and run python3 what-does-idempotency-mean-in-a-network-protocol.py. It uses Python’s standard library, including sqlite3, and requires Python 3.10 or newer plus SQLite 3.24 or newer. A successful run reports Ran 1 test and OK.
The single test method exercises replay, payload conflict and reopen behavior. Its success shows those cases work for the local temporary database under the current run. It does not guarantee availability under lock contention, key-retention safety, or coordination with external systems.