Consistency: transactions, idempotency, outbox and sagas

01ACID and isolation levels

A database transaction groups changes so they succeed or fail together. ACID describes the promise: Atomicity (all or nothing), Consistency (constraints hold), Isolation (concurrent transactions do not see each other's half-done work) and Durability (committed data survives a crash). Isolation comes in levels. Read committed, the PostgreSQL default, only shows committed data but lets a value change between two reads in the same transaction. Repeatable read gives a stable snapshot, which is the MySQL InnoDB default. Serializable makes concurrent transactions behave as if they ran one after another, at the cost of retries when conflicts occur. Classic anomalies are lost updates (two users decrement stock at once) and write skew. Prevent them with row locks (SELECT ... FOR UPDATE), atomic updates (SET stock = stock - 1 WHERE stock > 0), unique constraints or serializable isolation with retry.

02Why distributed transactions are hard

When an order touches your database, a payment provider and a message queue, no single transaction covers all three. Two-phase commit (2PC) tries: a coordinator asks every participant to prepare, then to commit. It works in some closed systems, but it blocks when the coordinator fails, holds locks across network round-trips, and is unsupported by most queues and every third-party HTTP API. The fundamental problem is the dual write: you save an order, then publish 'order created'. If the process crashes between the two, either the event is lost or, if you publish first, an event exists for an order that was never saved. Retrying adds another problem: a timeout does not tell you whether the other side did the work. You cannot remove these failures, so you design for them.

• A timeout means 'unknown', not 'failed'.
• Every cross-boundary step must be retryable without side effects.
• Keep a reconciliation path for states you cannot determine automatically.

03Idempotency keys

An operation is idempotent when doing it twice has the same effect as doing it once. GET, PUT and DELETE are idempotent by definition; POST, which creates things or charges cards, is not. The fix is an idempotency key: the client generates a unique ID (a UUID) per logical operation and sends it, typically in an Idempotency-Key header. The server stores the key with the result in the same transaction as the work, protected by a unique constraint scoped to the tenant or user. A retry with the same key returns the stored result instead of repeating the action; a concurrent duplicate hits the unique constraint and waits or fails cleanly. Keys should expire after a sensible window, and a key reused with a different request body should be rejected. Apply the same idea to consumers: record processed message IDs.

1CREATE TABLE idempotency_keys (
2  tenant_id     uuid        NOT NULL,
3  key           text        NOT NULL,
4  request_hash  text        NOT NULL,
5  response      jsonb,
6  created_at    timestamptz NOT NULL DEFAULT now(),
7  PRIMARY KEY (tenant_id, key)
8);
9 
10-- Inside the same transaction as the business write:
11INSERT INTO idempotency_keys (tenant_id, key, request_hash)
12VALUES ($1, $2, $3)
13ON CONFLICT (tenant_id, key) DO NOTHING
14RETURNING key;
15-- No row returned? It is a retry: load and return the stored response.

04The transactional outbox

The outbox pattern solves the dual write. Instead of publishing to the broker directly, you insert the event into an outbox table in the same database transaction as the business change. Either both are committed or neither is. A separate relay process then reads unpublished rows, publishes them to the queue or broker, and marks them as sent. If the relay crashes after publishing but before marking, the event is published again, so delivery is at-least-once and consumers must be idempotent. Use FOR UPDATE SKIP LOCKED so several relay workers can share the work without collisions, and clean up old published rows. An alternative relay is change data capture (for example, Debezium reading the database log), which avoids polling. The inverse pattern, the inbox, stores incoming message IDs to deduplicate on the consumer side.

1BEGIN;
2UPDATE orders SET status = 'paid' WHERE id = $1;
3INSERT INTO outbox (aggregate_id, type, payload)
4VALUES ($1, 'order.paid', $2);
5COMMIT;
6 
7-- Relay worker, in a loop:
8SELECT id, type, payload
9FROM outbox
10WHERE published_at IS NULL
11ORDER BY created_at
12LIMIT 100
13FOR UPDATE SKIP LOCKED;
14-- publish each row, then:
15UPDATE outbox SET published_at = now() WHERE id = ANY($1);

05Sagas: orchestration vs choreography

A saga is a long business transaction broken into local steps, each with a compensating action that semantically undoes it. Placing an order might be: reserve stock, charge the card, create the shipment. If charging fails, the saga releases the stock. Compensation is not rollback: a sent email cannot be unsent, so you send a correction; a captured payment is refunded, not erased. In choreography, each service listens for events and emits the next one ('StockReserved' triggers payment). It is loosely coupled but hard to follow once there are many steps. In orchestration, one component, an explicit state machine, tells each participant what to do and records progress. It is easier to monitor, test and change, and it is my default for flows with more than two or three steps. Either way, persist saga state, make every step idempotent and expose stuck sagas to operators.

06Eventual consistency, exactly-once myths and CAP

Eventual consistency means that, if no new changes arrive, all copies of the data will converge, but for a short time they may disagree. In practice this is fine for loyalty points appearing a second later; it is not fine for showing a balance the user can overspend. Design the user experience for it: show 'processing' states, read your own writes from the primary, and reconcile regularly against the source of truth, such as a payment provider's report. 'Exactly-once delivery' over a network is a myth; what you can build is exactly-once effect, from at-least-once delivery plus idempotent processing. CAP, in plain words: when the network splits your system in two, each side must either refuse some requests (choose consistency) or keep answering with possibly stale data (choose availability). Without a partition, the real trade-off is latency versus consistency.

07MVCC, serializable isolation and retry loops

PostgreSQL and MySQL InnoDB use multi-version concurrency control: writers create new row versions and readers see a snapshot, so reads do not block writes. That is why anomalies appear: two transactions can each read a snapshot, decide, and write without seeing each other. PostgreSQL's serializable level uses Serializable Snapshot Isolation, which tracks read-write dependencies and aborts one transaction with SQLSTATE 40001 (serialization_failure) when a dangerous pattern forms. It gives the strongest guarantee without locking every row, but only if the application retries the whole transaction on 40001, ideally with a small backoff and a retry cap. Keep serializable transactions short, avoid external calls inside them, and use it for invariants that span rows, such as 'at least one doctor on call', where a simple row lock is not enough.

1async function withSerializableRetry<T>(fn: (tx: Tx) => Promise<T>, attempts = 5): Promise<T> {
2  for (let i = 1; ; i++) {
3    try {
4      return await db.transaction(fn, { isolationLevel: "serializable" });
5    } catch (err: any) {
6      const retryable = err?.code === "40001" || err?.code === "40P01"; // serialization / deadlock
7      if (!retryable || i >= attempts) throw err;
8      await new Promise((r) => setTimeout(r, Math.random() * 50 * i));
9    }
10  }
11}

08Idempotency in depth: in-flight requests and fingerprints

A naive idempotency table has a gap: the first request is still running when the retry arrives. Store a state with the key: in_progress, then completed with the response, or failed with a retryable flag. A retry that finds in_progress gets 409 Conflict (or waits briefly), never a second execution. Fingerprint the request (method, path, a canonical hash of the body) and reject reuse of a key with a different fingerprint. Decide what to store: final success and deterministic validation errors are replayed; transient errors such as a downstream timeout are not cached, so the client can retry. Scope keys by account to avoid collisions, expire them after the client's retry window, and when the operation calls a third party, pass a derived idempotency key downstream, so the whole chain is safe end to end.

• States: in_progress, completed (with response), failed.
• Same key + different body = 422, not a replay.
• Forward a derived key to payment providers and other APIs.

09Outbox at scale: ordering, CDC and replication slots

A polling relay with SKIP LOCKED is simple but can reorder events, because several workers publish in parallel. If consumers need order per entity, partition the work by aggregate ID or use a single relay per partition, and include an aggregate version in each event so consumers can discard stale ones. Change data capture tools such as Debezium read the write-ahead log through a logical replication slot instead of polling, giving low latency and commit order. The operational trap: a replication slot keeps WAL on the primary until the consumer confirms it. If the CDC connector stops, WAL accumulates and can fill the disk. Monitor slot lag, set max_slot_wal_keep_size (PostgreSQL 13+) as a safety cap, and drop abandoned slots. Prune the outbox table regularly, ideally by partition, so it stays small and fast.

1-- How much WAL each replication slot is holding back
2SELECT slot_name, active,
3       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained
4FROM pg_replication_slots;
5 
6-- postgresql.conf: never let a stuck slot fill the disk
7-- max_slot_wal_keep_size = '20GB'

10Saga design: pivot steps and countermeasures for isolation

Sagas give up isolation: other transactions can see intermediate states, like reserved stock for an order that will be cancelled. Order steps deliberately. Compensatable steps come first (reserve stock, place a hold), then the pivot step, the point of no return such as capturing payment, then retriable steps that must eventually succeed (create shipment, send receipt) and are retried rather than compensated. To limit anomalies, use countermeasures. A semantic lock marks records as pending so other flows treat them carefully. Commutative updates (add and subtract balances) make order irrelevant. Rereading a value before an update detects that it changed. Version fields reject stale writes. Persist saga state with timeouts per step, so a saga waiting forever on a lost reply becomes a visible, actionable item rather than a silent leak of reserved stock.