Idempotency Key TTL and Atomic Commit Boundaries for Write Retries

Direct Answer

Use TTL = retry_window_max + safety_margin, where the safety margin is roughly 25% of the mean inter-arrival time for retries. For atomicity, avoid two-phase commit between a distributed cache and your database. Instead, store the idempotency key in the same database as the primary mutation, enforce a UNIQUE constraint on the key column, and insert both the key and the data row inside a single transaction. This gives you at-most-once semantics without a coordinator.

Confirmed Facts vs. Likely Explanations

Confirmed: A TTL shorter than the maximum retry arrival window admits duplicates; a TTL longer than necessary increases cache storage linearly. True atomic commit across a cache and a relational database generally requires either a two-phase commit protocol or a single-store design where the idempotency key and data change share one transaction.

Likely: A Repeatable Read or Serializable isolation level is sufficient to prevent a concurrent retry from observing a partially applied mutation. The outbox pattern—writing the idempotency key and data in one DB transaction, then publishing the key to the cache—is the de facto production boundary for most systems because it sidesteps the single-point-of-failure risk of 2PC coordinators.

Steps for This Case

1. Add a UNIQUE constraint to the idempotency key column in the primary data store.
2. Wrap the key insert and the business mutation in one database transaction.
3. Set the cache TTL to retry_window_max + safety_margin based on the observed retry latency distribution.
4. Optionally use the outbox pattern to propagate the key to the cache after commit, rather than writing the cache before the DB commit.

Verification

Measure retried-request cache miss rates across TTL settings. A spike in duplicate records indicates the TTL is below the effective retry window; sustained high cache occupancy suggests the TTL is above the optimal bound. Confirm your write transactions run at Repeatable Read or higher so the idempotency insert and data mutation share the same snapshot.