API Idempotency and Database Transactions

API idempotency prevents retries from producing multiple business effects, but the idempotency record and the business mutation must be coordinated. A database transaction can make those writes atomic when they share the same transactional resource.

An idempotent API gives repeated requests with the same idempotency key the same logical effect as processing the request once. A common implementation stores a key, request metadata, processing state, and often the resulting response. The key should normally be scoped to the operation or endpoint so unrelated requests do not collide.

The difficult case is coordinating the idempotency record with the actual business change. If the application commits the business mutation but fails before recording completion, a retry may repeat the mutation. If it records completion first and then the business transaction rolls back, a retry may incorrectly return success even though the operation never happened.

When both changes use the same database, a single transaction can atomically create or update the idempotency record and apply the business mutation. A unique constraint on the idempotency key provides concurrency protection, while the transaction determines whether the record and mutation become visible together.

For longer-running work or external side effects, one database transaction is not enough. Sending an email, charging a card, or publishing to an external system cannot generally be rolled back by the database. Patterns such as an outbox, durable job processing, provider-side idempotency, or explicit state machines are used to bridge those boundaries.

At senior level, also consider concurrent retries. Two requests carrying the same key can arrive simultaneously, so the design must define who owns processing and what the other request does: wait, return an existing completed result, or receive a conflict/in-progress response. Isolation, locking, unique constraints, transaction boundaries, and retry behavior all matter.

Key points

• Put the idempotency record and related database mutation in one transaction when they share the same database.
• Use a unique constraint to prevent concurrent requests from creating duplicate idempotency records.
• Do not mark an operation successfully completed before the business mutation is durably committed.
• Database transactions cannot atomically roll back external side effects.
• Define explicit behavior for concurrent retries and requests that encounter an in-progress operation.

Interview questions

Why should the idempotency record and business mutation usually be in the same database transaction?

Because otherwise a failure between two commits can leave the system inconsistent. If the business mutation commits first, a crash can cause a retry to perform it again because no completed idempotency record exists. If the idempotency record commits first, a later failure can make the API claim success for work that was rolled back. A single transaction makes the relevant database state change together.

How would you prevent two simultaneous requests with the same idempotency key from both executing the business operation?

I would enforce uniqueness on the idempotency key in the database and make the insertion part of the transaction. One request successfully claims the key, while the other detects the conflict and follows the defined retry policy. Depending on the API contract, the second request can wait for completion, return an in-progress response, or return the already stored result. The exact behavior should be explicit rather than relying on timing.

What happens if your transaction updates the database successfully but sending an event to Kafka fails?

The database transaction cannot atomically roll back the external message broker operation. A common solution is the transactional outbox: write the business mutation and an event record in the same database transaction, then publish the outbox entry asynchronously. The publisher retries until the event is delivered, while consumers should also be designed for duplicate delivery. This separates database atomicity from message delivery reliability.

Should an idempotency record store the complete response or only a status?

It depends on the API contract and the cost of reconstructing the result. Storing the response can make retries straightforward because the server can return the original result without repeating business processing. A status-only record can work when the result is cheap and deterministic to reconstruct, but it requires careful handling of resource state and concurrent retries. The stored data should also be bound to the original request semantics so a key cannot accidentally be reused for a different operation.

Why isn't an idempotency key by itself enough to make an API operation safe?

A key is only an identifier unless the server uses it to enforce a specific processing rule. The implementation must atomically record or claim the key and associate it with the operation's outcome. It also needs protection against races between simultaneous requests. Finally, external side effects may require their own idempotency mechanism because the database cannot roll them back.