Make retried database mutations atomic with their idempotency records

TL;DR

A conflicting request may wait for the first transaction to resolve or receive a temporary in-progress response. A caller can retry a mutation after a timeout and cannot tell whether the server committed. It must not perform the mutation while the claim is unresolved. All attempts must use the authoritative unique constraint, not an in-memory lock or a possibly stale read as the only guard.

When to use

You are seeing this: A caller can retry a mutation after a timeout and cannot tell whether the server committed. Use this skill when you run into "Make retried database mutations atomic with their idempotency records".

When not to use

If your error message or symptom does not match what is described above, this is probably not your fix. Search for your exact error text instead of forcing this one to fit.

Versions

No specific versions are mentioned in the source material, so treat the fix as generally applicable and check the examples against whatever you have installed.

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.

The guarantee follows from a unique key claim and atomic commit of the claim, business mutation, and replayable outcome. The timeout, rollback, and concurrent conflict branches were analyzed logically; no implementation was run or tested.

Rationale: A narrower existing guide addresses request canonicalization across schema rollouts. This guidance covers the foundational transaction and retry protocol that applies before version migration becomes relevant.