Wrapping Database Transactions for Safe Retries

Problem Statement & Prerequisites

This runbook answers a specific engineering question: how do you structure a database transaction so that automatic client retries, proxy retries, and gateway-level replays never produce duplicate writes, double-charges, or corrupted state machines?

Part of: Transaction Scoping & Atomic Operations

Before working through this guide you should understand:

• Transaction scoping and isolation levels — specifically why the idempotency key check and the business mutation must share a single BEGIN/COMMIT boundary.
• Idempotency key storage and TTL management — the schema design and expiry policies that this runbook builds on.
• Redis cache-based deduplication — the fast-path layer that sits in front of the database writes described here.

The core problem is a race condition: without explicit serialisation, two concurrent retries can both pass an idempotency check and both commit duplicate mutations. The solution is to move the check and the mutation inside the same atomic unit, with a lock that prevents concurrent access to the same key.

The diagram below shows the transaction boundary that eliminates the race window.

---

Step-by-Step Implementation

Step 1 — Create the idempotency schema

Create a partitioned table that supports rapid lookups, payload-hash validation, and TTL-based cleanup without full-table scans.

CREATE TABLE idempotency_keys (
    id                   BIGINT GENERATED ALWAYS AS IDENTITY,
    key_hash             VARCHAR(64)  NOT NULL,
    request_payload_hash VARCHAR(64)  NOT NULL,
    status               VARCHAR(20)  NOT NULL
                             CHECK (status IN ('PENDING', 'COMPLETED', 'FAILED')),
    response_payload     JSONB,
    retry_count          INT          DEFAULT 0,
    created_at           TIMESTAMPTZ  DEFAULT NOW(),
    expires_at           TIMESTAMPTZ  NOT NULL,
    UNIQUE (key_hash),
    PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (created_at);

-- Composite index for TTL cleanup and status routing
CREATE INDEX idx_idempotency_status_expires
    ON idempotency_keys (status, expires_at);

request_payload_hash is the guard against key reuse with different parameters — a common source of silent data corruption in multi-tenant APIs. Set expires_at to NOW() + INTERVAL '48 HOURS' for payment gateway workflows whose SLA retry window is 24–72 hours, and NOW() + INTERVAL '1 HOUR' for short-lived API operations.

Step 2 — Wrap the upsert and mutation in a single transaction (Go + pgx)

PostgreSQL advisory locks serialise concurrent retries on the same key without table-level contention. Use SERIALIZABLE isolation for financial ledger writes; READ COMMITTED with SELECT FOR UPDATE is sufficient for lower-risk mutations.

func ProcessIdempotent(ctx context.Context, pool *pgxpool.Pool, key, payloadHash string, ttl time.Duration) (*Response, error) {
    tx, err := pool.BeginTx(ctx, pgx.TxOptions{IsoLevel: pgx.Serializable})
    if err != nil {
        return nil, fmt.Errorf("begin tx: %w", err)
    }
    defer tx.Rollback(ctx)

    // Acquire advisory lock scoped to this transaction; key is hashed to int64
    var locked bool
    if err = tx.QueryRow(ctx,
        "SELECT pg_try_advisory_xact_lock(hashtext($1))", key).Scan(&locked); err != nil || !locked {
        return nil, fmt.Errorf("lock unavailable for key %s", key)
    }

    expiresAt := time.Now().Add(ttl)
    var (
        id              int64
        status          string
        responsePayload []byte
    )
    err = tx.QueryRow(ctx, `
        INSERT INTO idempotency_keys (key_hash, request_payload_hash, status, expires_at)
        VALUES ($1, $2, 'PENDING', $3)
        ON CONFLICT (key_hash) DO UPDATE
            SET retry_count = idempotency_keys.retry_count + 1
            WHERE idempotency_keys.expires_at > NOW()
        RETURNING id, status, response_payload`,
        key, payloadHash, expiresAt,
    ).Scan(&id, &status, &responsePayload)
    if err != nil {
        return nil, fmt.Errorf("upsert idempotency key: %w", err)
    }

    // Duplicate: return the stored response without re-executing
    if status == "COMPLETED" {
        _ = tx.Rollback(ctx)
        return unmarshalResponse(responsePayload)
    }

    // Execute business logic inside the same transaction
    result, err := executeMutation(ctx, tx, id)
    if err != nil {
        return nil, err // tx.Rollback runs via defer
    }

    // Mark complete and persist response for future retries
    encoded, _ := json.Marshal(result)
    if _, err = tx.Exec(ctx,
        "UPDATE idempotency_keys SET status='COMPLETED', response_payload=$1 WHERE id=$2",
        encoded, id); err != nil {
        return nil, fmt.Errorf("mark complete: %w", err)
    }

    return result, tx.Commit(ctx)
}

Each step is independently verifiable: check locked, then status, then the COMPLETED update — every branch is explicit and testable in isolation.

Step 3 — Node.js implementation (pg + ioredis fast path)

Use a Redis SET NX fast path to avoid a database round-trip on hot duplicate traffic, with a mandatory database fallback for cache misses.

const DEDUP_TTL_SEC = 172800; // 48 hours

async function processIdempotent(pgClient, redis, key, payloadHash, mutationFn) {
  const cacheKey = `idem:${key}`;

  // Fast path: try Redis NX to detect an in-flight or completed request
  const redisResult = await redis.set(cacheKey, payloadHash, 'NX', 'EX', DEDUP_TTL_SEC);

  if (redisResult === null) {
    // Cache hit — check if COMPLETED in DB before short-circuiting
    const { rows } = await pgClient.query(
      `SELECT status, response_payload FROM idempotency_keys
       WHERE key_hash = $1 AND expires_at > NOW()`, [key]
    );
    if (rows[0]?.status === 'COMPLETED') {
      return JSON.parse(rows[0].response_payload);
    }
    // PENDING or not found — fall through to DB upsert path
  }

  // Database path: full transactional upsert
  await pgClient.query('BEGIN');
  try {
    const { rows } = await pgClient.query(`
      INSERT INTO idempotency_keys (key_hash, request_payload_hash, status, expires_at)
      VALUES ($1, $2, 'PENDING', NOW() + INTERVAL '48 hours')
      ON CONFLICT (key_hash) DO UPDATE
        SET retry_count = idempotency_keys.retry_count + 1
        WHERE idempotency_keys.expires_at > NOW()
      RETURNING id, status, response_payload`,
      [key, payloadHash]
    );

    if (rows[0].status === 'COMPLETED') {
      await pgClient.query('ROLLBACK');
      return JSON.parse(rows[0].response_payload);
    }

    const result = await mutationFn(pgClient, rows[0].id);
    await pgClient.query(
      `UPDATE idempotency_keys SET status='COMPLETED', response_payload=$1 WHERE id=$2`,
      [JSON.stringify(result), rows[0].id]
    );
    await pgClient.query('COMMIT');
    return result;
  } catch (err) {
    await pgClient.query('ROLLBACK');
    throw err;
  }
}

Step 4 — Outbox pattern for exactly-once event publishing (SQL)

When a mutation must also emit a downstream event (order created, payment captured), include the outbox row in the same transaction. The event relay reads the outbox table; duplicate retries never re-insert the idempotency key or the event.

BEGIN;

INSERT INTO orders (id, amount, status)
VALUES ($1, $2, 'PROCESSING');

INSERT INTO idempotency_keys (key_hash, request_payload_hash, status, expires_at)
VALUES ($3, $4, 'COMPLETED', NOW() + INTERVAL '48 hours')
ON CONFLICT (key_hash) DO NOTHING
RETURNING id;

INSERT INTO outbox (aggregate_id, event_type, payload, created_at)
SELECT $1, 'ORDER_CREATED', $5, NOW()
WHERE NOT EXISTS (
    SELECT 1 FROM outbox WHERE aggregate_id = $1 AND event_type = 'ORDER_CREATED'
);

COMMIT;

The WHERE NOT EXISTS guard makes the outbox insert idempotent independently of the idempotency key — important when the outbox relay replays events during a restart.

Step 5 — Python implementation (psycopg3 + asyncio)

import asyncio, json, hashlib
from contextlib import asynccontextmanager
import psycopg

async def process_idempotent(conn: psycopg.AsyncConnection, key: str,
                              payload_hash: str, mutation_fn) -> dict:
    key_hash = hashlib.sha256(key.encode()).hexdigest()

    async with await conn.transaction():
        # Advisory lock prevents concurrent retries executing the mutation
        locked = await conn.execute(
            "SELECT pg_try_advisory_xact_lock(hashtext(%s))", [key_hash]
        )
        row = (await locked.fetchone())[0]
        if not row:
            raise RuntimeError(f"Could not acquire lock for key {key_hash}")

        cur = await conn.execute("""
            INSERT INTO idempotency_keys
                (key_hash, request_payload_hash, status, expires_at)
            VALUES (%s, %s, 'PENDING', NOW() + INTERVAL '48 hours')
            ON CONFLICT (key_hash) DO UPDATE
                SET retry_count = idempotency_keys.retry_count + 1
                WHERE idempotency_keys.expires_at > NOW()
            RETURNING id, status, response_payload
        """, [key_hash, payload_hash])

        record = await cur.fetchone()
        if record["status"] == "COMPLETED":
            return json.loads(record["response_payload"])

        result = await mutation_fn(conn, record["id"])

        await conn.execute(
            "UPDATE idempotency_keys SET status='COMPLETED', response_payload=%s WHERE id=%s",
            [json.dumps(result), record["id"]]
        )
        return result

---

Verification & Testing

Confirm the implementation handles duplicate requests correctly before deploying.

1. Simulate a duplicate request in psql:

-- First call: inserts PENDING, executes mutation, sets COMPLETED
CALL process_order('idem-key-abc123', '{"amount":5000}');

-- Second call with same key: ON CONFLICT path should return response_payload
-- without executing the mutation again. Verify retry_count increments.
SELECT key_hash, status, retry_count, response_payload
FROM idempotency_keys
WHERE key_hash = encode(digest('idem-key-abc123', 'sha256'), 'hex');
-- Expected: status = COMPLETED, retry_count = 1

2. Verify Redis fast-path behaviour:

# Check that the key exists after the first call
redis-cli GET "idem:idem-key-abc123"
# Expected: the payload hash string

# Verify TTL is set correctly (should be ~172800 seconds)
redis-cli TTL "idem:idem-key-abc123"

3. Test concurrent retries with pgbench:

pgbench -c 10 -j 4 -t 50 -f idempotency_concurrent_test.sql mydb
# idempotency_concurrent_test.sql sends 10 concurrent requests with the same key.
# Verify: orders table has exactly 1 row for the test order ID after the run.

4. Verify the outbox has exactly one event:

SELECT COUNT(*) FROM outbox
WHERE aggregate_id = '<test-order-id>' AND event_type = 'ORDER_CREATED';
-- Expected: 1

---

Failure Scenarios & Debugging

---

SRE / Observability Checklist

Instrument these specific signals at both the application layer and the database layer. Reference the retry logic and backoff fundamentals page for the metric naming conventions used across this site.

1. idempotency.retry_rate — percentage of requests where retry_count > 0. Alert on > 15 % sustained over 5 minutes; indicates upstream retry misconfiguration or downstream instability.
2. idempotency.deduplication_decision — trace span attribute with values cache_hit, db_hit, conflict_retry, new_execution. Sudden db_hit dominance after a Redis incident reveals cache eviction driving load to the database.
3. idempotency.transaction_duration_p99 — alert on > 500 ms for idempotent write paths; indicates lock contention or index degradation on idempotency_keys.
4. db.deadlock_count — set alert threshold at > 0 sustained for 60 seconds. A single deadlock spike is acceptable; sustained deadlocks indicate missing jitter in retry logic or an advisory-lock gap.
5. idempotency.cache_miss_rate — Redis fallback frequency. Correlate with db.connection_pool_wait_p99; a rising cache miss rate that precedes a DB load spike confirms the Redis-to-DB fallback is working but under-provisioned.
6. idempotency.key_payload_hash_mismatch_count — non-zero values indicate client-side key reuse bugs; page on-call immediately if this appears in production financial flows.

Propagate X-Idempotency-Key as a trace header and inject it into all downstream spans as idempotency_key. Annotate spans with retry_attempt_count, lock_wait_time_ms, and deduplication_decision to enable precise filtering in Jaeger or Datadog when isolating retry-induced latency.

---

Related

• Transaction Scoping & Atomic Operations — parent page covering isolation levels, BEGIN/COMMIT boundaries, and two-phase commit strategies.
• Idempotency Key Storage & TTL Management — schema design, TTL policies, and storage cost management for the idempotency_keys table this runbook builds on.
• Using Redis SET NX for Distributed Request Deduplication — the fast-path layer described in Step 3, with Lua atomic check-and-set patterns.
• Retry Logic & Backoff Fundamentals — the full-jitter and exponential backoff formulas referenced in the failure scenarios table.