Overview
Distributed systems retry work. A browser may resend after a timeout, a queue may deliver a message more than once, or a worker may crash after committing a change but before acknowledging the job. Idempotency ensures that repeating the same logical operation does not create duplicate effects.
Start with the workload and the invariant
For an API request, accept an idempotency key scoped to the authenticated client and operation. Store the key with a unique constraint, request fingerprint, processing state, and final response reference. A repeated key with the same payload can return the original result; a repeated key with a different payload should be rejected.
Practical example
CREATE TABLE idempotency_keys (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
client_id BIGINT UNSIGNED NOT NULL,
idempotency_key VARCHAR(100) NOT NULL,
request_hash CHAR(64) NOT NULL,
response_code SMALLINT NULL,
response_body MEDIUMTEXT NULL,
created_at DATETIME NOT NULL,
UNIQUE KEY uq_client_idempotency (client_id, idempotency_key)
);
Claim a job with an atomic state transition or row lock. Define allowed transitions such as pending to processing to completed, and record attempt count and lease expiry. A worker that loses its lease must not overwrite a newer worker's result. Use a version or token to guard completion.
Trade-offs and failure modes
The transactional outbox pattern helps when a database change must trigger a message. Write the business update and an outbox row in one transaction. A separate publisher sends pending events and marks them delivered. Consumers should also be idempotent because message delivery can be repeated.
Verification and rollout
Do not promise exactly-once execution across independent systems without a specific protocol that provides it. In practice, design for at-least-once delivery and deduplicate at the boundary where effects are applied. Keep idempotency records long enough to cover realistic client retries and delayed messages.
Engineering checklist
Test crashes at each boundary: before commit, after commit but before response, after publish but before acknowledgement, and during duplicate delivery. Idempotency is a data-model and workflow property, not merely a request header.
Before shipping, review the database version, storage engine, SQL mode, character set, collation, and deployment process used by the target environment. Features and DDL behavior can differ between MySQL and MariaDB releases. Validate the exact statements on a representative staging database, capture a baseline, and keep a tested recovery path for changes that affect production data.