Durable idempotency for retried database mutations

Use when

A caller can retry a mutation after a timeout and cannot tell whether the server committed. Assume the business effect and retry record can be written in one transactional database.

Minimum record and key

The caller creates one high-entropy idempotency key for one logical operation and reuses it for every retry. A new logical operation gets a new key. The server enforces uniqueness for authenticated caller, operation type, and key. The record stores a fingerprint of effect-defining request fields and an immutable committed outcome, such as response status and body or a stable result identifier. A retry with the same key and a different intent is rejected.

Atomic execution

In one database transaction, claim the unique key, perform the business mutation, store the committed outcome in the idempotency record, and commit. Never commit the effect separately from the record. A conflicting request may wait for the first transaction to resolve or receive a temporary in-progress response. It must not perform the mutation while the claim is unresolved. After the first transaction commits, compare intent and replay its stored outcome. If it rolls back, a waiting retry may claim the key and execute.

Failure reasoning

If the database commits but the response is lost, the retry sees the durable outcome and does not repeat the effect. If the server crashes before commit, the database rolls back both effect and record, so a retry can safely attempt the operation. Concurrent retries are serialized by the unique constraint. This gives at most one committed database effect per retained key, even though execution may be attempted more than once after rollbacks.

Boundaries

Retain the record for at least the full retry window; deletion permits an old retry to act again. All attempts must use the authoritative unique constraint, not an in-memory lock or a possibly stale read as the only guard. A remote call cannot be atomically committed with a local database row. For external effects, commit an outbox intent with the local state and require the receiver to deduplicate a stable operation identifier or provide an equivalent idempotency guarantee. Do not claim exactly-once remote effects without that receiver guarantee.

Evidence level

This is transaction-level reasoning. Validate the chosen database conflict behavior, isolation level, crash recovery, and receiver contract in an implementation.