βοΈ Python Backend Patterns β π Idempotency Pattern π
Posted on: November 20, 2026
Description:
Make repeated submissions of the same API operation produce one local order, even when the original response is lost.
The problem: a missing response is not a failed operation
An API commits an order, but the connection drops before the client receives its response. Retrying an ordinary create request can create a second order.
Idempotency means repeated execution has the same intended business effect as one execution. For a create endpoint, an idempotency key identifies a logical operation across retries.
Define the contract
- Generate a fresh, unpredictable key for a new operation; retain it for all retries.
- Scope the key to the authenticated customer and endpoint/operation.
- Save the validated request data, response status and response body.
- Same key and same data: replay the saved response.
- Same key and different data: reject it; this demo uses 409 Conflict.
Identical payloads with different keys represent different operations. This is not general payload deduplication. A key is not authentication.
A runnable Python example
The accompanying idempotency_pattern.py uses only the standard library. api_request() models the API boundary and returns (status, body); it does not launch a web server. Pass a customer identity obtained from trusted authentication.
payload = {"product": "book", "quantity": 1}
first = api_request(db, "customer-7", "req-42", payload)
retry = api_request(db, "customer-7", "req-42", payload)
assert first == retry
assert order_count(db) == 1
Both responses are status 201 with order_id: 1. A retry replays that original status; it does not create a new resource. The short keys here are readable demonstration values; real clients should generate collision-resistant keys, such as UUIDs.
Make the check and write atomic
The script begins with BEGIN IMMEDIATE, then checks the scoped key inside that transaction. If no record exists, it inserts the order and its saved response before committing. The record's composite primary key enforces uniqueness for (customer, operation, key).
SQLite allows one writer at a time. Acquiring the write transaction before the lookup prevents two connections from both proceeding after a missing-key check. A second writer waits within the configured timeout or receives a database error; it does not bypass the transaction. This simple choice serializes unrelated writes too, so it is an educational design, not a high-throughput architecture.
con.execute("BEGIN IMMEDIATE")
# Check scoped key; compare data; replay if already present.
# Otherwise insert the order AND the response record.
con.commit()
These comments summarize the full implementation in the script. If either write fails, rollback undoes the local order as well. Errors are not saved as successful idempotent results in this example.
Observed results
| Scenario | Result |
|---|---|
| First call, then retry | Same 201 response; one order |
| Same key, changed quantity | 409; still one order |
| New key, same payload | A second order |
| Eight concurrent calls on separate connections | One order; eight identical replies |
| Injected failure while saving the record | Order rolled back; later retry succeeds |
The script also verifies customer isolation, validation and JSON field-order independence. These are correctness checks, not a throughput benchmark. Canonical JSON is used for this small validated schema; broader APIs must define which request fields affect identity.
Production boundaries
Keep records for a documented retry window. This demo keeps them for the lifetime of its database and does not implement expiry. Once a record is removed, a retry may create another order.
The local transaction covers only writes to this SQLite database. It cannot roll back an external charge, email or remote API call. Use downstream idempotency where available and an appropriately designed outbox/consumer deduplication workflow. Do not interpret this example as a distributed exactly-once guarantee.
Define retryable failures, timeouts, key retention and response replay in your API contract. The script propagates database/server errors to the application's centralised exception handler.
Code Snippet:
from concurrent.futures import ThreadPoolExecutor
from contextlib import closing
from pathlib import Path
from tempfile import TemporaryDirectory
from threading import Barrier
import json
import sqlite3
# 1. Keep the business operation and its idempotency record in the SAME database.
def initialize(db):
with closing(sqlite3.connect(db)) as con, con:
con.executescript("""
CREATE TABLE IF NOT EXISTS orders (
id INTEGER PRIMARY KEY, customer TEXT NOT NULL,
product TEXT NOT NULL, quantity INTEGER NOT NULL
);
CREATE TABLE IF NOT EXISTS requests (
customer TEXT NOT NULL, operation TEXT NOT NULL, key TEXT NOT NULL,
payload TEXT NOT NULL, status INTEGER NOT NULL, body TEXT NOT NULL,
PRIMARY KEY (customer, operation, key)
);
""")
def validate(key, payload):
if not isinstance(key, str) or not key.strip() or len(key) > 128:
raise ValueError('Supply a nonempty idempotency key of at most 128 characters.')
if not isinstance(payload, dict) or set(payload) != {'product', 'quantity'}:
raise ValueError('Expected product and quantity only.')
if not isinstance(payload['product'], str) or not payload['product'].strip():
raise ValueError('product must be a nonempty string.')
if type(payload['quantity']) is not int or not 1 <= payload['quantity'] <= 100:
raise ValueError('quantity must be an integer from 1 to 100.')
# Canonical JSON is sufficient for this small, validated schema.
return json.dumps(payload, sort_keys=True, separators=(',', ':'))
class KeyConflict(Exception):
"""One operation key cannot represent two different payloads."""
# 2. The customer comes from trusted authentication, never an unchecked header.
def create_order(db, customer, key, payload):
canonical = validate(key, payload)
operation = 'POST /orders'
with closing(sqlite3.connect(db, timeout=5, isolation_level=None)) as con:
try:
# Acquire SQLite's write transaction BEFORE checking the key.
# Other writers wait (up to timeout) rather than both seeing a miss.
con.execute('BEGIN IMMEDIATE')
saved = con.execute(
'SELECT payload, status, body FROM requests '
'WHERE customer=? AND operation=? AND key=?',
(customer, operation, key),
).fetchone()
if saved:
if saved[0] != canonical:
raise KeyConflict('Key already used with different request data.')
con.commit()
return saved[1], json.loads(saved[2])
order_id = con.execute(
'INSERT INTO orders(customer, product, quantity) VALUES (?, ?, ?)',
(customer, payload['product'], payload['quantity']),
).lastrowid
status = 201
body = {'order_id': order_id, 'product': payload['product'],
'quantity': payload['quantity']}
con.execute(
'INSERT INTO requests VALUES (?, ?, ?, ?, ?, ?)',
(customer, operation, key, canonical, status, json.dumps(body)),
)
con.commit() # Order + response become durable together.
return status, body
except Exception:
con.rollback() # Failed work is not cached by this example.
raise
# 3. Framework-independent API boundary: (status code, JSON response body).
def api_request(db, customer, key, payload):
try:
return create_order(db, customer, key, payload)
except ValueError as exc:
return 400, {'error': str(exc)}
except KeyConflict as exc:
return 409, {'error': str(exc)}
# Database/server failures propagate to the application's centralized handler.
def order_count(db):
with closing(sqlite3.connect(db)) as con:
return con.execute('SELECT COUNT(*) FROM orders').fetchone()[0]
# 4. Reproduce a lost response, retry, mismatch and a genuinely NEW operation.
def demo():
payload = {'product': 'book', 'quantity': 1}
with TemporaryDirectory() as tmp:
db = Path(tmp) / 'orders.sqlite'
initialize(db)
first = api_request(db, 'customer-7', 'req-42', payload)
# Imagine the first response is lost AFTER the transaction commits.
retry = api_request(db, 'customer-7', 'req-42', payload)
assert first == retry and order_count(db) == 1
print('First:', first)
print('Retry:', retry)
print('Orders after retry:', order_count(db))
conflict = api_request(db, 'customer-7', 'req-42', {**payload, 'quantity': 2})
assert conflict[0] == 409 and order_count(db) == 1
print('Changed payload:', conflict[0])
fresh = api_request(db, 'customer-7', 'req-43', payload)
assert fresh[1]['order_id'] == 2 and order_count(db) == 2
print('New key:', fresh)
print('Orders after new key:', order_count(db))
# 5. Eight concurrent calls, separate connections, one shared database.
concurrent_db = Path(tmp) / 'concurrent.sqlite'
initialize(concurrent_db)
gate = Barrier(8)
def submit(_):
gate.wait()
return api_request(concurrent_db, 'customer-7', 'same-key', payload)
with ThreadPoolExecutor(max_workers=8) as pool:
replies = list(pool.map(submit, range(8)))
assert all(reply == replies[0] for reply in replies)
assert order_count(concurrent_db) == 1
print('Concurrent calls: 8; orders: 1; identical replies: True')
# Scope the key to the authenticated customer and operation.
assert api_request(db, 'customer-8', 'req-42', payload)[0] == 201
assert order_count(db) == 3
assert api_request(db, 'customer-7', '', payload)[0] == 400
assert api_request(db, 'customer-7', 'bad', {**payload, 'quantity': True})[0] == 400
# JSON field order does not change the request identity.
assert api_request(db, 'customer-7', 'req-42', {'quantity': 1, 'product': 'book'}) == first
# 6. Inject a record-write failure: the order must roll back too.
failed_db = Path(tmp) / 'rollback.sqlite'
initialize(failed_db)
with closing(sqlite3.connect(failed_db)) as con, con:
con.execute("CREATE TRIGGER fail_record BEFORE INSERT ON requests "
"BEGIN SELECT RAISE(ABORT, 'injected failure'); END")
try:
create_order(failed_db, 'customer-7', 'req-42', payload)
except sqlite3.IntegrityError:
pass
else:
raise AssertionError('Expected injected failure')
assert order_count(failed_db) == 0
with closing(sqlite3.connect(failed_db)) as con, con:
con.execute('DROP TRIGGER fail_record')
assert api_request(failed_db, 'customer-7', 'req-42', payload)[0] == 201
assert order_count(failed_db) == 1
print('Scope, validation, canonicalization and rollback checks: passed')
if __name__ == '__main__':
demo()
No comments yet. Be the first to comment!