In this lesson, you will learn how to design database schemas that guarantee the same result regardless of how many times an operation is executed.
Why Idempotency Matters
In distributed applications, network failures are common. If your application sends a request to "subtract $50 from a balance" and the network times out, the application doesn't know if the database processed the change or not. If you simply retry the operation, you risk charging the customer twice. Idempotency ensures that repeating an action has no additional effect beyond the initial successful execution.
The Idempotency Key Pattern
The most robust way to achieve idempotency at the database layer is by using an Idempotency Key. Instead of issuing generic commands like UPDATE balance = balance - 50, you track the intent of the request in your schema.
Consider a processed_transactions table:
CREATE TABLE processed_transactions (
idempotency_key UUID PRIMARY KEY,
amount DECIMAL(10, 2),
account_id INT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
When a request arrives, the application includes a unique idempotency_key (generated by the client). Before modifying any account balances, you attempt to insert this key into the processed_transactions table:
-- This operation is idempotent
INSERT INTO processed_transactions (idempotency_key, amount, account_id)
VALUES ('unique-request-uuid-123', 50.00, 1);
If the insert succeeds, you then proceed to update the account_balance table within the same transactional scope. If the insert fails because the idempotency_key already exists, you know the request is a duplicate. You can then safely ignore the retry or return the result of the previous attempt without double-charging the user.
Combining with Constraints
By using a UNIQUE constraint or a PRIMARY KEY on your idempotency identifier, you leverage the database engine to enforce safety. This shifts the burden of verification away from fragile application logic and anchors it in the relational integrity of your schema. By designing your tables to record intent (the key) alongside the data change, you make your system inherently resilient to network retries.
Key Takeaway: Design your schema to record unique request identifiers, using database-level constraints to prevent duplicate processing when operations are retried.