Distributed Transactions Part 1: The Death of ACID

Mental Model

In a single monolithic application, maintaining data consistency is straightforward. You wrap your database queries inside a standard database transaction, and the engine guarantees ACID (Atomicity, Consistency, Isolation, Durability). However, when we transition to distributed microservices, this local transaction safety vanishes, giving rise to complex data coordination anomalies.

In a distributed system, a single business action (e.g. placing an order) often spans multiple physical databases and message queues. Executing a local transaction on one database does not cover changes made in another service's database. Attempts to enforce local transaction boundaries across remote servers fail because the network is unreliable, nodes crash, and database engines cannot coordinate locks natively without sacrificing availability.

To bridge this consistency gap, we must abandon the expectation of immediate global consistency. Instead, we design our services around Eventual Consistency and utilize patterns that coordinate state changes asynchronously without holding active locks across network boundaries.

System Requirements

To analyze the boundaries of distributed consistency, we define the parameters of a high-load transactional system like checkout or ticket booking:

Functional Requirements

• Consistent Multi-Resource Operations: An ordering workflow must guarantee that an order is created, payment is authorized, and inventory is reserved, or the system reverts back to a clean state.
• Asynchronous Integration: The creation of an order must notify downstream indexing, analytics, and shipment services within 2 seconds.
• Idempotency Guarantee: Retrying the same transaction request must prevent double-billing or duplicate shipping records.
• Reconciliation and Auditing: Provide transaction logs to allow background jobs to audit state transitions and flag any payment or inventory anomalies.

Non-Functional Requirements

• Write Latency SLA: Normal API writes must remain under 30 milliseconds.
• Local Database Isolation: Microservices must maintain isolated data stores without cross-database foreign key constraints or sharing databases.
• Blast Radius Minimization: The failure of auxiliary services (e.g. notification alerts) must not block the core checkout transaction.
• High Availability: Maintain write paths even during partial downstream outages, ensuring clients can queue transactions for later processing.

API Design and Interface Contracts

To solve consistency anomalies without blocking databases, systems utilize the Transactional Outbox pattern. Below is a structured SQL DDL and JSON API contract representing a transactional outbox table schema and event structure:

Outbox Table DDL

CREATE TABLE outbox_events (
    event_id UUID PRIMARY KEY,
    aggregate_type VARCHAR(100) NOT NULL,
    aggregate_id VARCHAR(100) NOT NULL,
    event_type VARCHAR(100) NOT NULL,
    payload JSONB NOT NULL,
    status VARCHAR(50) DEFAULT 'PENDING',
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    processed_at TIMESTAMP WITH TIME ZONE
);

CREATE INDEX idx_outbox_pending ON outbox_events(status, created_at);

Outbox Event JSON Contract

{
  "event_id": "8de54652-76ad-4cac-a50a-da54eb24b45f",
  "aggregate_type": "ORDER",
  "aggregate_id": "order-998811",
  "event_type": "ORDER_CREATED",
  "payload": {
    "user_id": "user-881100",
    "total_amount_cents": 12500,
    "items": [
      { "sku": "SKU-998", "quantity": 2 }
    ]
  },
  "created_at": "2026-05-23T10:00:00Z"
}

Every database record written to the outbox_events table represents a transition in state that needs to be communicated to downstream components. The payload field contains the serialized state, and the status field tracks whether the event poller has successfully published the event to the broker.

High-Level Architecture

The core driver behind the "Death of ACID" in distributed architectures is the Dual-Write Anomaly.

1. The Dual-Write Anomaly

When a service attempts to write to a local database and subsequently publish an event to an external message broker, a network failure or process crash between those two actions will leave the system in a permanently inconsistent state. If the database commit succeeds but the message broker publish fails, the event is lost forever. If the publish succeeds but the database commit fails, downstream consumers process invalid events.

graph TD
    subgraph Service["Order Processing Service"]
        Client[Client Request] --> Code[Process Logic]
        Code -->|Step 1: Commit| DB[(Order Database)]
        Code -->|Step 2: Publish| Broker[Message Broker / Kafka]
    end
    
    subgraph Failures["Possible Outage Points"]
        Crash{"Service Crashes!"}
        NetError{"Network Drops!"}
    end
    
    Code -.-> Crash
    Code -.-> NetError
    
    %% Style annotations
    style Crash fill:#ffebee,stroke:#c62828,stroke-width:2px;
    style NetError fill:#ffebee,stroke:#c62828,stroke-width:2px;

2. The Transactional Outbox Architecture

To eliminate the dual-write problem, the service writes both the business entity (e.g., Order) and a corresponding Event record to the same database using a single, local ACID transaction. An independent background worker then polls the outbox table and streams events to the message broker safely.

sequenceDiagram
    autonumber
    participant Client
    participant Service as Order Service
    participant DB as Order DB (ACID Bounds)
    participant Relay as Outbox Poller / CDC
    participant Broker as Kafka Broker

    Client->>Service: POST /api/v1/orders
    rect rgb(240, 248, 255)
        Note over Service, DB: Atomic Local Transaction
        Service->>DB: Insert Order Record
        Service->>DB: Insert Outbox Event Record
        DB-->>Service: Transaction Committed (ACID)
    end
    Service-->>Client: Returns Order ID (HTTP 201)

    rect rgb(255, 240, 245)
        Note over Relay, Broker: Asynchronous Event Streaming
        Relay->>DB: Read PENDING Events (Locking row)
        Relay->>Broker: Publish Event (At-Least-Once)
        Broker-->>Relay: Publish Confirmed
        Relay->>DB: Update Status to 'PROCESSED'
    end

Using a local transaction ensures that if the database write fails, the entire transaction is rolled back, and no outbox event is ever written. This guarantees that we only publish events for successfully settled business operations, achieving total coordination between DB state and event streams.

Low-Level Design and Schema

Below is a production-ready, compilable Java class utilizing Spring Framework annotations. It executes a business transaction with atomic transactional outbox writes, ensuring zero dual-write vulnerabilities:

package com.codesprintpro.transactions;

import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import java.util.UUID;

public class OrderService {

    private final OrderRepository orderRepository;
    private final OutboxRepository outboxRepository;

    public OrderService(OrderRepository orderRepository, OutboxRepository outboxRepository) {
        this.orderRepository = orderRepository;
        this.outboxRepository = outboxRepository;
    }

    /**
     * Executes the order placement atomically. Writes both the Order entity
     * and the Outbox Event inside the local database transaction bounds.
     */
    @Transactional
    public OrderResponse placeOrder(OrderRequest request) {
        // 1. Instantiating order domain model
        Order order = new Order();
        order.setOrderId(UUID.randomUUID().toString());
        order.setUserId(request.getUserId());
        order.setAmountCents(request.getAmountCents());
        order.setStatus("CREATED");
        
        // 2. Persisting Order inside database
        this.orderRepository.save(order);

        // 3. Creating Outbox Event representing the state transition
        String eventPayload = String.format(
                "{\"order_id\":\"%s\",\"user_id\":\"%s\",\"amount\":%d}",
                order.getOrderId(), order.getUserId(), order.getAmountCents()
        );

        OutboxEvent outboxEvent = new OutboxEvent();
        outboxEvent.setEventId(UUID.randomUUID());
        outboxEvent.setAggregateType("ORDER");
        outboxEvent.setAggregateId(order.getOrderId());
        outboxEvent.setEventType("ORDER_CREATED");
        outboxEvent.setPayload(eventPayload);
        outboxEvent.setStatus("PENDING");

        // 4. Persisting event record. Transaction guarantees both succeed or fail together.
        this.outboxRepository.save(outboxEvent);

        return new OrderResponse(order.getOrderId(), order.getStatus());
    }
}

The transactional annotation guarantees that the framework starts a database transaction context before entering the method. If an exception occurs during execution (e.g. database save errors), the transaction is aborted, discarding both the order record and the outbox event, ensuring database atomicity.

Scaling Challenges and Capacity Estimation

Scaling the Transactional Outbox pattern introduces operational friction at high workloads:

1. Database Table Polling Overhead

If a background worker continuously runs SELECT ... FOR UPDATE SKIP LOCKED on the outbox table, it creates massive read contention and table bloat in databases like PostgreSQL, degrading write performance.

• Mitigation: Transition from polling to Change Data Capture (CDC) using log-based tools like Debezium. Debezium reads the database transaction logs (Write-Ahead Log - WAL) directly, streaming mutations to Kafka without executing database queries or locking tables.

2. Log-based Ingestion Capacity Estimation

Let's calculate the WAL generation and event throughput sizing:

• Throughput Sizing:
  • Assume 10,000 transaction commits per second.
  • Every write produces a WAL payload containing the transaction log and the outbox event payload.
  • Average database WAL entry size for the outbox table is 1 kilobyte.
  • Network Bandwidth Required: $$\text{Bandwidth} = 10,000 \text{ events/sec} \times 1 \text{ KB} = 10 \text{ MB/sec} = 80 \text{ Mbps}.$$
  • The CDC worker must be allocated a minimum dedicated 1 Gbps network card to handle peaking loads and avoid log replication lags.
  • Storage calculations: Sizing the WAL retention buffers requires planning for downstream network outages. If the message broker goes down for 4 hours, the database must retain $10,000 \times 1\text{KB} \times 14400 \approx 144 \text{ gigabytes}$ of WAL logs on disk without running out of disk space.

Failure Scenarios and Resilience

Resilience configurations are required to handle system crashes during outbox processing:

Scenario A: Message Broker Downtime

If Kafka goes down, the outbox relay (e.g., Debezium or poller worker) will fail to publish events, causing the outbox table queue size to grow rapidly.

• Resiliency Mitigation: Configure the relay with backpressure. The relay must pause reading the database WAL or outbox table when Kafka is unavailable, and restart consumption when Kafka recovers, avoiding memory crashes.

Scenario B: Poller Coordinator Death

If the background outbox worker crashes halfway through processing a batch of events, the rows will remain in PENDING state.

• Resiliency Mitigation: Assign leases or heartbeats to the processing workers. If a worker fails to renew its lease, another instance will claim the pending rows and retry the publication safely.

Scenario C: PostgreSQL WAL Rotation

If the Debezium worker lags behind for a long period (e.g. 12 hours) due to down downstream networks, PostgreSQL might purge older WAL files, resulting in lost events.

• Resiliency Mitigation: Configure wal_keep_size to be large enough (e.g. 100 gigabytes) to tolerate extended network dropouts, and set up automated replication lag alerting to trigger human operator interventions before WAL files are pruned.

Architectural Trade-offs

When trading consistency for performance, you must evaluate the coordination strategies:

Selecting the transactional outbox balances implementation effort and consistency safety. It avoids network locks and dual-write data loss but introduces event serialization overhead and CDC operations complexity.

Staff Engineer Perspective

Verbal Script

Interviewer: "Why can't we use standard ACID transactions across modern distributed microservices, and how do we ensure consistency when placing an order and billing a user?"

Candidate: "We cannot use local ACID transactions across microservices because each microservice maintains its own independent database to prevent tight coupling. To enforce global ACID, we would need to coordinate all databases using a distributed locking protocol like Two-Phase Commit (2PC). However, 2PC is a blocking protocol. If the coordinator crashes or a node experiences high network latency, all databases must hold transaction locks indefinitely, degrading availability and causing cascading thread pools failures."

Interviewer: "Correct. So how would you design this transactional checkout instead?"

Candidate: "I would drop strict global atomicity and transition to Eventual Consistency utilizing the Transactional Outbox pattern. When a user places an order, the Order Service executes a single local ACID transaction that saves the order record and inserts an ORDER_CREATED event record into an outbox table. Since both writes are local, they are guaranteed to be atomic. An independent CDC engine like Debezium monitors the database logs and publishes the event to Kafka. The Payment Service consumes this event, processes the charge, and publishes a PAYMENT_COMPLETED event, which the Order Service consumes to transition the order state. If payment fails, we trigger compensating workflows, ensuring resilience without locking databases."