Idempotency in Distributed Writes

In this lesson, you will learn how to design distributed write operations that guarantee the same final system state regardless of how many times a request is processed.

The Challenge of Retries

In distributed systems, especially when dealing with network partitions or asynchronous communication, a "success" response might be lost, or a request might time out before the client knows if it succeeded. If the client retries the operation, you risk duplicate entries, double-billing, or corrupted state. An idempotent operation is one where the side effect of performing it once is identical to the side effect of performing it multiple times.

Achieving Idempotency via Client-Generated Tokens

The most robust way to ensure idempotency is to assign a unique idempotency_key to every write request. This key should be generated by the client and persisted by the database during the transaction.

Consider a distributed banking ledger. If a user transfers money, the server should check for the existence of the request key before executing the SQL UPDATE.

-- Transactional approach to an idempotent write
BEGIN;

-- Check if we have already processed this specific request
IF NOT EXISTS (SELECT 1 FROM processed_requests WHERE request_id = 'uuid-12345') THEN
    
    -- Perform the write operation
    UPDATE accounts SET balance = balance - 100 WHERE user_id = 55;
    
    -- Record the request_id so it cannot be re-run
    INSERT INTO processed_requests (request_id, processed_at) VALUES ('uuid-12345', NOW());

END IF;

COMMIT;

Idempotent SQL Operators

Beyond external tracking, you can design your SQL statements to be naturally idempotent:

1. UPSERT (or INSERT ON CONFLICT): Instead of issuing an INSERT, use ON CONFLICT (id) DO UPDATE. If the record exists, the system updates it to the desired final state rather than creating a duplicate.
2. Deterministic Updates: Prefer absolute values over relative ones when retrying. For example, SET status = 'COMPLETED' is idempotent, whereas SET status_count = status_count + 1 is not, because multiple retries would incorrectly increment the counter.

By ensuring your write logic is anchored to a unique identifier or is inherently declarative, you eliminate the risk of transient network failures causing permanent data anomalies.

Key Takeaway: Design your distributed write operations to be repeatable by using unique request tokens or declarative SQL statements that converge on the same state regardless of execution count.