Transaction Design: ACID, Saga and the Outbox Pattern

About this article

This article is the sixth deep dive in the “Software Architecture” category of the Architecture Crash Course for the Generative-AI Era series, covering transaction design.

The mechanism that guarantees “all-succeed-or-all-undo” — the design core that prevents money-disappearing or money-appearing accidents like bank transfers. The article covers ACID properties, isolation levels, distributed transactions, eventual consistency, Saga / Outbox patterns, the CAP theorem, and idempotency, with axes for sorting consistency level per business need.

What is a transaction in the first place

A transaction is, in a nutshell, “a mechanism that makes a series of operations either all succeed or all roll back.”

Imagine an ATM bank transfer. When A sends $100 to B, “deduct from A’s account” and “add to B’s account” must succeed as a pair. If only one succeeds, $100 either vanishes or appears out of thin air. The mechanism guaranteeing “all succeed or all roll back” is a transaction.

Why transaction design matters

What happens if transaction design is left vague? Money-disappearing or money-appearing accidents happen for real. Some data — bank-account balances — needs strict consistency; some — SNS “like counts” — can drift slightly without harm. Applying maximum consistency to everything makes the system too heavy; sorting precision per business need is the design core.

Transaction design must be considered in tandem with overall structure (especially microservices). If 1 DB suffices, ACID alone is enough — whether you can tilt toward a configuration that avoids distributed transactions directly affects operational cost.

What keeps “money from disappearing”

Transaction design decides “which data needs what granularity of consistency” in the system. Some data — bank-account balances — needs strict consistency; some — SNS “like counts” — can drift slightly without harm. Applying maximum consistency to everything makes the system too heavy; sorting precision per business need is the design core.

It must be considered together with overall structure (especially microservices). If 1 DB is enough, ACID alone suffices; whether you can lean on a configuration that avoids distributed transactions ties directly to operational cost.

Consider it together with overall structure. If 1 DB is enough, ACID alone is enough.

ACID properties

The classic transaction guarantee RDBMSes have offered for decades is ACID — four properties whose initials guarantee data integrity strongly.

ACID matters because applications’ biggest losses come from “money / inventory / order data inconsistency.” Once data drifts, root-cause investigation, correction, and customer-facing costs balloon, shaking trust in the entire business.

ACID established with RDB development from the 1970s, designed assuming “strong integrity guaranteed within one DB.” It worked perfectly when servers and DBs were one machine each. In the cloud-era distributed environment, maintaining ACID across multiple DBs and services requires locking all nodes; one network outage stops everything, making it operationally impractical. That’s why eventual consistency and Saga (covered later) emerged.

Within a single DB, ACID is solidly sufficient — the default for finance, accounting, inventory, and other strong-consistency-required businesses.

Isolation levels

In ACID, the realistic trade-off with performance is “I” (Isolation). The level you pick determines how strictly other transactions are kept apart. Stricter raises consistency but lowers concurrent performance.

PostgreSQL defaults to READ COMMITTED, MySQL (InnoDB) to REPEATABLE READ. Higher isolation costs performance, so strengthen only the parts that need it.

The difficulty of distributed transactions

In microservices and multi-DB configurations, transactions across multiple DBs sometimes become necessary. Traditionally 2PC (Two-Phase Commit — ask all DBs “prepared?” then commit together) solved this, but it has fatal flaws in the cloud era.

2PC forces a two-phase exchange of “all nodes prepared -> all nodes commit”, so any one node going down blocks everything. In cloud environments where network failures are routine, running 2PC in production frequently stops the whole service — “effectively non-functional” in practice.

The cloud-native principle is to avoid 2PC. Use eventual consistency instead.

Eventual Consistency

Eventual consistency allows a relaxed guarantee: “not consistent immediately, but consistent eventually.” Writes succeed instantly; propagation to other replicas and services happens asynchronously. So users may temporarily see stale data on screen.

Bank accounts can’t drift even momentarily, but “like counts,” “follower counts,” “review counts” on e-commerce sites work fine even if 1 second stale. Treating “data where slight drift is fine” as eventually consistent secures the system’s overall availability and scalability.

Examples: Amazon inventory counts, Twitter follower counts, Instagram likes, YouTube view counts.

Per data type, judge from business requirements whether strong consistency is required or eventual is sufficient.

Saga pattern

Saga is a pattern that implements distributed transactions as “a chain of small local transactions + compensation.” In a hotel-booking flow, “reservation creation,” “payment,” “inventory reservation,” “notification” run on different services; failure mid-flow triggers compensation to undo earlier steps.

Unlike 2PC, this doesn’t lock all services; each step completes locally, so it works in cloud environments. But incomplete compensation design risks leaving half-done state.

Pseudocode for orchestration-style Saga: each step completes in its own local TX; on failure, compensation runs in reverse order.

async function bookHotel(input: BookingInput) {
  const completed: Array<() => Promise<void>> = [];
  try {
    const reservation = await reservationSvc.create(input);
    completed.push(() => reservationSvc.cancel(reservation.id));

    const payment = await paymentSvc.charge(input.amount);
    completed.push(() => paymentSvc.refund(payment.id));

    await inventorySvc.reserve(input.roomId);
    completed.push(() => inventorySvc.release(input.roomId));

    await notificationSvc.notify(input.userId);
    return { ok: true, reservationId: reservation.id };
  } catch (err) {
    // Compensate in reverse order (idempotent assumed)
    for (const compensate of completed.reverse()) {
      await compensate().catch(logCompensationFailure);
    }
    throw err;
  }
}

Compensation must be idempotent. If retries running double don’t produce stable results, the saga itself becomes a new source of consistency incidents. In production, combine with the Outbox pattern to reliably synchronize message sending and DB updates — the standard approach.

Two Saga forms

Saga has two implementation styles, differing in where flow control sits.

When business is complex and order matters, Orchestration. When simple and services are independent, Choreography.

Default is starting from Orchestration. Systems where the whole isn’t visible become operational hell.

Outbox pattern

Outbox harmonizes “DB write” and “message-queue send” in a single DB transaction. It prevents the recurring microservices trouble “DB succeeded but Kafka send failed” (Kafka: distributed message-queue infrastructure) — the standard pattern.

1. Record the message-to-send into the outbox table together with business data.
2. A separate process (Relay) reads outbox and sends to the message queue.
3. Update the sent flag.

This prevents the accident of only one of “DB commit” or “send” succeeding. For event-driven microservice architecture, near-mandatory.

Saga + Outbox is the default; near-mandatory for microservices.

CAP theorem

The foundation of distributed-system architecture is the CAP theorem: Consistency, Availability, and Partition tolerance can’t be simultaneously satisfied — that’s the constraint.

Network partitions happen in reality, so “P” is essentially required. Practical choice becomes “CP (consistency-priority)” vs “AP (availability-priority).” Business requirements determine whether “err out but be accurate” or “keep running with stale data.”

Reverse from business requirements. Picking distributed DBs without CAP awareness produces unexpected behavior.

Idempotency

In distributed systems, “the same request arriving multiple times” due to network failures and timeouts is routine. The property guaranteeing the result is the same regardless of repeat execution is idempotency.

Without idempotent “payment APIs,” retries cause double charges. Idempotency is achieved by clients attaching a request ID and servers deduplicating by that ID — the default. By HTTP method, GET / PUT / DELETE are spec-defined idempotent; POST is not, requiring care.

In microservices, event-driven, and retry processing, idempotency is required; bolting it on later is hard, so design from the start.

Decision criteria: choosing consistency level

The core of transaction design is identifying the consistency level needed per business. Demanding strong consistency for all data crashes performance; making everything eventual breaks the business.

The decision axis: “if this data is even briefly inconsistent, will there be financial, legal, or reputational damage?” Money-moving processing, inventory allocation, records subject to taxation or audit can’t tolerate even one cent / one unit drift, so strong consistency is mandatory; compromising here shakes the business itself.

Conversely, “data that works business-wise even with slight drift” like like counts, view counts, recommendations, log aggregations is fine with eventual consistency; applying strong consistency would sacrifice performance and scalability. Asking the business “how would it hurt if this data were a few seconds stale?” and reasoning back is most reliable.

Strong consistency is a high-cost requirement; limit it to where it’s truly needed.

Data-type × consistency-level ladder

Note: industry rates as of April 2026. Periodic refresh required.

“All strong consistency” is excess and “all eventual” is dangerous; sorting consistency level per data type is the operational core. Typical industry split:

The isolation-level numeric gate: PostgreSQL’s default READ COMMITTED as the basis, with “only inventory allocation and financial transactions individually elevated to SERIALIZABLE” as the rule. Applying SERIALIZABLE to all tables drops concurrent performance several-fold; limit it to the necessary parts.

Strong consistency is high-cost; limit it to genuinely necessary business.

Distributed-transaction traps

Common ways microservice-crossing / multi-DB-crossing transactions fail. All produce production data inconsistency.

Strictly speaking, the Knight Capital 2012 incident wasn’t a distributed-TX accident, but the scenario of “one machine left with old code + retry runaway” producing 45 minutes / $440M loss / company dissolution shows the horror of neglecting idempotency and consistency design (full details in Appendix: Major Incident Catalog).

“Distributed + retry + no idempotency” is the triple-landmine set for double processing.

AI decision axes — Staying inside one database is the AI’s safe zone

Typical holes in AI-generated transaction code

When you instruct AI to “write order processing,” it writes single-DB ACID transactions accurately. The BEGIN→INSERT→UPDATE→COMMIT basic pattern exists abundantly in training data.

Problems emerge in cross-service scenarios. For example, if you instruct “charge the payment service → decrement inventory service → write to orders table” as 3 steps, AI writes code calling each step sequentially but often omits compensation (refund) for step 1 when step 2 fails.

Specific areas AI tends to miss:

• Refund logic when inventory decrement fails after payment API success
• Atomicity between message-queue sends and DB writes (scenes requiring the Outbox pattern)
• Idempotency-key design to prevent double-processing on retries
• Recovery when server-side processing succeeded after a timeout

These are typical “works on the happy path but breaks on error paths” code, undetectable by unit tests.

Why 1-DB-complete architecture is safest in the AI era

Within the scope where ACID works on a single PostgreSQL instance, AI writes transaction control correctly. INSERT/UPDATE within BEGIN~COMMIT, data integrity via foreign-key constraints, optimistic locking with WHERE version = ? — all are classic patterns where AI generation accuracy is high.

In other words, deciding at the architecture level to “keep things 1-DB-complete as much as possible” maximizes the range where AI can write safely. Distributed transactions are an area where even humans struggle to implement correctly, and maintaining quality when delegating to AI is even harder.

Caveats when delegating idempotency-key design to AI

When you instruct AI to “add idempotency to this API,” it writes an implementation retrieving Idempotency-Key from the request header and storing it in Redis. Due to Stripe API influence, many examples exist in training data, and the basic pattern is accurate.

However, the following design decisions are not made automatically by AI, so they need to be decided in advance:

• Key expiration period (24 hours or 7 days)
• Behavior when the same key arrives with a different request body (return 409 or ignore)
• Key storage destination (Redis / DB / both)
• Key format (fixed UUID v4, or user-ID + operation-type combination)

These depend on business requirements, so the realistic approach is to codify them in design documents or ADRs before delegating implementation to AI.

”The day we treated timeout = failure” (industry case)

A project added 3-retry to the client side as timeout protection for the payment API; the result was duplicate charges to a small number of users. Server-side processing had succeeded; only the response was delayed, and all retries went through.

Few developers haven’t watched a similar failure happen nearby. Retry implementation tends to come in optimistically, and the “probably failed, let’s resend” mindset is the textbook path to production double-charges.

Without an idempotency key, “timeout = failure” is almost never a safe assumption. The “don’t know if it succeeded” state across the network is routine in distributed systems. Design idempotency keys before adding retries — that’s the rule.

Distributed + retry + no idempotency = double-processing landmine. Triple-set is the safety condition.

What you must decide — what’s your project’s answer?

Articulate your project’s answer in 1-2 sentences for each:

• Per-data consistency requirement (strong / eventual)
• Isolation level (the lowest line business tolerates)
• Distributed-transaction handling (Saga / Outbox / avoid)
• Retry policy and idempotency
• Locking strategy (optimistic / pessimistic)
• CAP choice (CP / AP)

Write your answers down as an ADR. A concrete guide to writing them is here.

[DevOps Architecture] Documentationen.senkohome.com/arch-intro-devops-docs/

Related Articles

Summary

This article covered transaction design — ACID, isolation levels, Saga, Outbox, the CAP theorem, idempotency.

Sort consistency level per data, prioritize 1-DB completion, and use the Saga + Outbox + idempotency triple-set when distributed is needed. The 2026 realistic answer including AI era.

The next article is the Software Architecture category’s final installment: authentication and sessions (server session / JWT / OAuth).

Back to series TOC -> ‘Architecture Crash Course for the Generative-AI Era’: How to Read This Book

I hope you’ll read the next article as well.