Transaction Scoping & Atomic Operations

Part of: Backend Implementation & Storage Patterns

Distributed systems collapse the clean guarantee that a single BEGIN … COMMIT block can protect an entire business operation. Network calls, message broker publishes, and external API invocations all escape the transaction boundary, meaning partial failures produce intermediate states that no rollback can fully undo. This page explains how to define the narrowest safe transaction scope for each mutation, how to validate idempotency inside that scope, and how to wire the resulting guarantee together with safe retry semantics so that duplicate requests never commit twice.

---

Guarantee Model

Transaction scoping for idempotency targets exactly-once side effects under at-least-once delivery. It does not assert network-level exactly-once (that is impossible across an untrusted transport); instead it asserts that for any given idempotency key, the state machine transitions from PENDING to COMPLETED (or FAILED) exactly once, regardless of how many times the containing request arrives.

The guarantee degrades in predictable ways:

---

Core Algorithm: Transaction State Machine

The fundamental protocol for an atomic idempotent write is a five-step state machine. The diagram below shows the full lifecycle from request arrival through durable commit.

Step-by-step protocol:

1. Pre-flight read — query the idempotency store (SELECT status FROM idempotency_keys WHERE key = $1 FOR UPDATE SKIP LOCKED). If COMPLETED, return the cached response without touching business state.
2. Open transaction — BEGIN a database transaction at the chosen isolation level.
3. Insert PENDING sentinel — INSERT INTO idempotency_keys (key, status, created_at) VALUES ($1, 'PENDING', NOW()) ON CONFLICT (key) DO NOTHING. A zero-row result means a concurrent writer already claimed the key; return a 409 Conflict or queue with backoff.
4. Execute business logic — perform the actual mutations (ledger entries, inventory decrements, outbound event publishes) within the still-open transaction.
5. Commit and mark COMPLETED — update the idempotency record (status = 'COMPLETED', response_body = $2) in the same COMMIT. On any exception, roll back and mark FAILED.

---

Implementation Variants

Variant 1: PostgreSQL — Single-Transaction Atomic Upsert

The strongest durability model. Idempotency validation and business logic commit atomically. This is viable when the entire operation touches only local database state (no external API calls mid-transaction).

BEGIN;

-- Step 1: claim the key atomically
INSERT INTO idempotency_keys (key, status, created_at, expires_at)
VALUES ($1, 'PENDING', NOW(), NOW() + INTERVAL '24 hours')
ON CONFLICT (key) DO NOTHING;

-- Step 2: read back; if status is already COMPLETED, abort and return cached
SELECT status, response_body
FROM idempotency_keys
WHERE key = $1
FOR UPDATE;

-- Step 3: (application checks the row above; if COMPLETED, ROLLBACK and return cached)

-- Step 4: run business mutation
UPDATE accounts
SET balance = balance - $amount
WHERE account_id = $account_id AND balance >= $amount;

-- Step 5: seal the idempotency record
UPDATE idempotency_keys
SET status = 'COMPLETED',
    response_body = $response_json
WHERE key = $1;

COMMIT;

import psycopg2
import json

def process_payment(conn, idempotency_key: str, account_id: str, amount: int) -> dict:
    with conn.cursor() as cur:
        cur.execute("""
            INSERT INTO idempotency_keys (key, status, created_at, expires_at)
            VALUES (%s, 'PENDING', NOW(), NOW() + INTERVAL '24 hours')
            ON CONFLICT (key) DO NOTHING
        """, (idempotency_key,))

        cur.execute("""
            SELECT status, response_body
            FROM idempotency_keys
            WHERE key = %s
            FOR UPDATE
        """, (idempotency_key,))
        row = cur.fetchone()

        if row and row[0] == 'COMPLETED':
            conn.rollback()
            return json.loads(row[1])

        cur.execute("""
            UPDATE accounts
            SET balance = balance - %s
            WHERE account_id = %s AND balance >= %s
        """, (amount, account_id, amount))

        if cur.rowcount == 0:
            conn.rollback()
            raise ValueError("Insufficient funds or account not found")

        response = {"status": "success", "account_id": account_id, "debited": amount}
        cur.execute("""
            UPDATE idempotency_keys
            SET status = 'COMPLETED', response_body = %s
            WHERE key = %s
        """, (json.dumps(response), idempotency_key))

        conn.commit()
        return response

Variant 2: Redis Fast-Path + PostgreSQL Durable Commit

For high-throughput APIs where the single-transaction approach adds unacceptable latency, a Redis fast-path intercepts duplicates before they reach the database. As covered in Redis & cache-based deduplication, the SET key value NX EX ttl command provides atomic check-and-set semantics.

import redis
import json

def process_with_cache_layer(r: redis.Redis, conn, idempotency_key: str, payload: dict) -> dict:
    cache_key = f"idem:{idempotency_key}"

    # Fast-path: check Redis first (NX = only set if not exists)
    acquired = r.set(cache_key, "PROCESSING", nx=True, ex=300)

    if not acquired:
        cached = r.get(cache_key)
        if cached and cached != b"PROCESSING":
            return json.loads(cached)
        # Still processing — caller should retry with backoff
        raise RetryableConflictError("Request in progress, retry with backoff")

    try:
        result = run_db_transaction(conn, idempotency_key, payload)
        # Seal cache with full response; 86400 s = 24-hour dedup window
        r.set(cache_key, json.dumps(result), ex=86400)
        return result
    except Exception:
        r.delete(cache_key)
        raise

Variant 3: Two-Phase Commit Across Services

When a single business operation spans two independent data stores (e.g., a payment ledger and an inventory system), neither single-transaction nor cache-layer approaches can span both. Two-phase commit (2PC) coordinates atomic commits, but network partitions during the PREPARE phase leave participants in an uncertain state — the notorious in-doubt transaction problem.

// Coordinator pseudocode — Go
func twoPhaseCommit(ctx context.Context, ledger, inventory TxParticipant, op Operation) error {
    txID := newTxID()

    // Phase 1: PREPARE
    if err := ledger.Prepare(ctx, txID, op.LedgerMutation); err != nil {
        _ = ledger.Abort(ctx, txID)
        return fmt.Errorf("ledger prepare: %w", err)
    }
    if err := inventory.Prepare(ctx, txID, op.InventoryMutation); err != nil {
        _ = ledger.Abort(ctx, txID)
        _ = inventory.Abort(ctx, txID)
        return fmt.Errorf("inventory prepare: %w", err)
    }

    // Phase 2: COMMIT — if either commit fails, a reconciliation daemon
    // must detect the in-doubt record and replay or roll back.
    if err := ledger.Commit(ctx, txID); err != nil {
        return fmt.Errorf("ledger commit: %w", err)
    }
    if err := inventory.Commit(ctx, txID); err != nil {
        // inventory is in-doubt; the reconciliation daemon will resolve it
        return fmt.Errorf("inventory commit (in-doubt): %w", err)
    }
    return nil
}

Production guidance: 2PC is rarely the right answer for synchronous request paths. Prefer database unique constraints and upserts with an outbox for cross-service durability, reserving 2PC for cases where the strict atomicity requirement genuinely cannot be relaxed.

Variant Comparison

---

Edge Cases & Failure Scenarios

---

Operational Concerns

Isolation Level Selection

Choosing the wrong isolation level is one of the most common sources of phantom writes in production. The rules of thumb:

• READ COMMITTED — suitable when idempotency validation uses SELECT … FOR UPDATE to serialize the critical path. Concurrent writers outside the lock window may read intermediate states, but the idempotency record itself is protected.
• REPEATABLE READ — eliminates non-repeatable reads; adds ~10–20 % overhead on write-heavy tables due to conflict detection. Use when derived aggregates (balance totals, inventory counts) must be consistent within a single transaction.
• SERIALIZABLE — full serialization guarantees but produces serialization failures under contention. Budget for a retry loop with exponential backoff with jitter capped at 3 attempts before escalating to 503.

TTL Management for Idempotency Records

Set TTLs based on the maximum plausible retry horizon for each operation class:

• Payment mutations: 72 hours (covers weekend settlement delays)
• API idempotency keys for user-facing writes: 24 hours
• Background job deduplication: 4 hours (aligns with typical job scheduler windows)

Enforce expiry via a scheduled DELETE FROM idempotency_keys WHERE expires_at < NOW() AND status IN ('COMPLETED', 'FAILED') job running every 15 minutes. Move records to a cold audit table before deletion rather than hard-deleting them. See idempotency key storage and TTL management for the full archival strategy.

Index Strategy

The idempotency_keys table requires two indexes in production:

-- Primary lookup: fast O(log n) point read on key arrival
CREATE UNIQUE INDEX idx_idem_key ON idempotency_keys (key);

-- TTL cleanup: avoid full-table scans on the expiry job
CREATE INDEX idx_idem_expires ON idempotency_keys (expires_at)
  WHERE status IN ('COMPLETED', 'FAILED');

For tables exceeding 50 million rows, consider hash partitioning on key to distribute write load across shards and reduce autovacuum pressure on any single partition.

SRE Alert Thresholds

---

Related

• Wrapping Database Transactions for Safe Retries — runbook for implementing the retry wrapper around this page’s protocol in Python, Go, and Node.js
• Database Unique Constraints & Upserts — sibling approach using ON CONFLICT as the sole atomic guard, without an explicit idempotency table
• Redis & Cache-Based Deduplication — fast-path deduplication layer that sits in front of the database transaction described here
• Idempotency Key Storage & TTL Management — archival strategy, partition schema, and cold-storage migration for the idempotency records this page writes
• Retry Logic & Backoff Fundamentals — the backoff and jitter strategies that drive safe re-entry into the transaction state machine above
• Backend Implementation & Storage Patterns — parent section covering the full storage-layer toolkit for idempotent systems