In this lesson, you will learn how to leverage client-provided idempotency tokens to ensure that duplicate network requests do not result in duplicate state changes within your distributed database.
The Problem: The "At-Least-Once" Delivery Trap
In distributed systems, especially when dealing with sharded architectures or microservices, network timeouts are inevitable. When a client sends a write request and the connection drops before receiving an acknowledgment, the client cannot know if the write succeeded or failed.
If the client retries the operation, a naive system might perform the action twice (e.g., charging a customer’s credit card or deducting inventory twice). To solve this, we shift from "at-least-once" delivery to idempotent operations, where multiple identical requests produce the same effect as a single request.
Implementing Idempotency Tokens
An idempotency token is a unique identifier (typically a UUID) generated by the client and included in the header or body of the request. The database uses this token to track the lifecycle of the operation.
Consider a payments table in a sharded cluster. Instead of simply inserting a row, we use a two-step "check-then-insert" approach or a unique constraint pattern:
-- Step 1: Check if the token exists for this transaction
-- If a row exists with this token, return the stored result
-- without re-processing the business logic.
-- Step 2: Attempt an atomic insert
INSERT INTO processed_requests (request_id, status, response_payload)
VALUES ('uuid-client-12345', 'COMPLETED', '{"tx_id": 98765}');
By placing a UNIQUE constraint on the request_id column, the database effectively acts as a lock. If a retry occurs with the same request_id, the database throws a unique violation error. Your application layer catches this error and returns the original successful response instead of processing the request again.
Why this is critical for Sharding
When using sharded data architectures, you must ensure your request_id contains enough information (or is routed correctly) so that the check occurs within the same shard as the data update. If you use a request_id table, ensure it is either co-located with the transaction data or handled via a distributed transaction/outbox pattern to maintain consistency across the network.
Key Takeaway: Idempotency tokens turn non-deterministic retries into deterministic outcomes by using the database to enforce a "process-once" constraint via unique identifiers.