Test stored-response replay with an idempotency key in SQLite
Test saved-response replay, changed-parameter refusal and rollback in SQLite. A separate inline two-connection test checks the locking idea, not a payment provider.
A client sends a payment request. The server charges the card. The response is lost on the way back and the client times out. A sensible client retries. Without protection, the second request charges the customer again.
Idempotency is the property that makes the retry safe: repeating the same request has the same effect as sending it once. Stripe's API reference describes its approach. A client generates a unique key, which the server uses to recognize later retries of the same request. Stripe suggests V4 UUIDs or another random string with enough entropy, allows up to 255 characters, and advises against using sensitive data such as email addresses as keys. For API v1, it saves the status code and body of the first request made with a key after execution begins, and returns that saved response on retries, including 500 errors. It compares incoming parameters with the original and returns an error if they differ. It does not save a result when validation fails or when the request conflicts with one still running, so those can be retried. It also notes that keys can be removed automatically after at least 24 hours.
This guide builds a tiny version of that idea in SQLite and tests it. The article and fixture were prepared with AI assistance and reviewed on 18 November 2026. The fixture is not Stripe's implementation and does not call any payment provider.
One transaction holds the key, the effect and the saved response
The fixture has a charges table and an idem table keyed by the idempotency key. The function begins an immediate transaction, looks up the key, and then does one of three things.
def charge(db, key, params, fail_after_effect=False):
h = fingerprint(params)
db.execute('BEGIN IMMEDIATE')
try:
row = db.execute('SELECT req_hash,response FROM idem WHERE key=?',
(key,)).fetchone()
if row:
if row[0] != h:
raise KeyReuse('key reused with different parameters')
db.execute('COMMIT')
return json.loads(row[1]), True
cur = db.execute('INSERT INTO charges(amount_minor) VALUES (?)',
(params['amount_minor'],))
if fail_after_effect:
raise RuntimeError('synthetic failure before commit')
resp = {'charge_id': cur.lastrowid, 'amount_minor': params['amount_minor']}
db.execute('INSERT INTO idem VALUES (?,?,?)', (key, h, json.dumps(resp)))
db.execute('COMMIT')
return resp, False
except BaseException:
db.execute('ROLLBACK')
raise
If the key is unknown, it records the charge and the saved response in the same transaction. If the key is known with the same parameters, it returns the saved response and does nothing else. If the key is known with different parameters, it refuses. The parameter fingerprint hashes the JSON with sorted keys, so the same fields in a different order count as the same request.
What the run checks
- Replay after commit. The first call creates one charge. A second call with the same key, and the parameters in reverse order, returns the identical response flagged as a replay. The table still has one charge.
- Key reuse. The same key with a different amount raises an error and changes nothing.
- Failure before commit. A call that inserts the charge and then fails rolls back. No key row is stored for that attempt, and the charge count is unchanged. A retry with the same key then runs once and creates a new charge, so the table holds two.
- Different key. A new key is a new operation and creates a third charge.
- A race. Two connections on a file database start at the same moment with one key. Each takes the write lock before checking. The result is one
executedand onereplay, and a single charge row.
All of it passes on Python 3.10.12 with SQLite 3.37.2. I also ran the whole script 20 times and it passed each time. Those runs show this fixture is stable on one machine, not that the design is safe under every load.
Honest limits
The race in step 5 uses the same logic written inline in the worker, not a call to charge, so it demonstrates the locking idea and not that exact function under contention. The fixture covers one database. It does not cover a charge made through an external provider, where the provider must receive the key too. A local key table cannot undo an email, a webhook or a call to another service, so those need their own idempotent handling.
It also skips key expiry. Stripe documents removal after at least 24 hours for API v1, and a pruned key means a repeated request runs as new. Choose a retention period longer than your longest realistic retry window, and decide what a very late retry should do.
Finally, the fixture does not model a request still in progress. Stripe's note that such conflicts are not saved and can be retried is a design choice. Another service might make the second caller wait or return a conflict.
Pair it with a bounded retry
An idempotency key can prevent duplicate effects within the system that enforces it. This fixture only demonstrates that for local database rows. A key does not decide how many retries to make or when to stop. Use it with a retry limit, a time budget and jittered delays, as the earlier retry-budget guide on this site tests.
Sources
- Stripe API reference, Idempotent requests: https://docs.stripe.com/api/idempotent_requests