In this lesson, you will learn how to design transaction logic that ensures repeated operations result in the same state, preventing data corruption during network failures.
The Problem of the "Ghost" Transaction
In a distributed environment, you often face the "at-least-once" delivery guarantee. If your application sends a payment request to the database but the network drops before receiving an acknowledgment, you don't know if the transaction succeeded or failed. If you blindly retry, you risk double-charging the customer or incrementing balances twice. Idempotent transaction design solves this by ensuring that the result of an operation remains the same regardless of how many times it is performed.
The Idempotency Key Pattern
The most robust way to achieve this is by using an idempotency_key—a unique identifier generated by the client for every distinct request. Instead of blindly executing an INSERT or UPDATE, you create a state tracking table in your database.
Consider this logic:
-- 1. Start a transaction
BEGIN;
-- 2. Check if the transaction key has already been processed
INSERT INTO processed_transactions (request_id, status)
VALUES ('unique-req-123', 'COMPLETED')
ON CONFLICT (request_id) DO NOTHING;
-- 3. If the row existed, the previous statement returns 0 rows affected.
-- If it didn't exist, proceed with the business logic.
UPDATE account_balances
SET balance = balance - 100
WHERE account_id = 'user-1'
AND (SELECT count(*) FROM processed_transactions WHERE request_id = 'unique-req-123') = 1;
COMMIT;
Why This Works
By wrapping the state check and the business logic in the same transaction, you ensure atomicity. Even if the network fails during the first attempt, the database acts as the "source of truth." When the retry arrives, the ON CONFLICT clause prevents a duplicate entry in the tracking table, and the logic prevents the balance update from firing a second time.
This design pattern decouples the reliability of your network from the integrity of your data. By requiring a unique key for every logical transaction, your application becomes resilient to the inevitable flakiness of distributed connections.
Key Takeaway: Always pair your business logic with a unique idempotency key and a persistent status check to ensure that retrying a request has no side effects beyond the intended outcome.