Course contentsShow
← System Design
Lesson 1015 of 1,91926. Idempotency and RetriesFree lesson

Conditional Writes for Idempotency

Use database features like INSERT IF NOT EXISTS or compare-and-swap operations to ensure idempotent data modifications.

Conditional Writes for Idempotency

You'll learn how to use database-level conditional operations to guarantee idempotent writes, even when requests arrive multiple times.

The Core Idea

Conditional writes are database features that enforce "only write if a specific condition is true." They're a powerful tool for idempotency because the database itself prevents duplicate modifications, rather than relying purely on application logic.

Think of it like a vending machine that only dispenses a snack if your money hasn't already been processed. The machine checks its internal ledger: "Did transaction #12345 already complete? If yes, refuse. If no, proceed and mark it complete." This all happens atomically—no race conditions.

Common Patterns

INSERT IF NOT EXISTS: Prevents duplicate record creation. The database only inserts if a record with the given idempotency key doesn't exist yet. Perfect for preventing double-charges or duplicate order submissions.

Compare-and-Swap (CAS): Updates a record only if its current value matches what you expect. For example: "Update account balance to 100onlyifit′scurrently100 only if it's currently 80." If another request changed it first, your update fails, signaling you need to retry or abort.

Versioning: Each record has a version number. Updates only succeed if you provide the correct current version: "Update order status to 'shipped' WHERE version = 5, then increment version to 6."

Why It Works

These operations are atomic at the database level. No matter how many duplicate requests arrive simultaneously, only one will succeed. The others receive a clear signal (constraint violation, zero rows affected) that the operation already completed.

Key Takeaway: Conditional writes leverage database atomicity to enforce idempotency mechanically, eliminating race conditions and reducing application-layer complexity for duplicate request handling.