Implementing Distributed Transactions Using the Two-Phase Commit Protocol

In a microservices architecture, business operations frequently span across multiple independent services, each backed by its own isolated database. While this decoupling provides scalability and bounded operational contexts, it eliminates the luxury of native, single-database ACID transactions.

When a single business operation requires coordinated updates across multiple data stores—such that either all updates succeed or none take effect—you face the classic challenge of Distributed Transactions.

This guide breaks down how to design, coordinate, and implement a distributed transaction mechanism from the ground up using the Two-Phase Commit (2PC) pattern, using a real-world scenario: guaranteeing ultra-fast, 10-minute food delivery.

---

1. The Core Problem: Guarantees Under Resource Contention

Consider an ultra-fast food delivery platform (like Zomato’s 10-minute delivery model):

1. Food is prepared in bulk in advance and kept hot in local micro-warehouses (dark stores).
2. A fleet of delivery partners waits at the store to pick up and deliver the order.
3. The product promise requires delivering within 10 minutes from the moment the user clicks Place Order.

                               +------------------+
                               |   User Client    |
                               +--------+---------+
                                        |
                                        | 1. Place Order
                                        v
                              +-------------------+
                              |   Order Service   |
                              |   (Coordinator)   |
                              +----+---------+----+
                                   |         |
                   2. Reserve Food |         | 3. Reserve Delivery Agent
                                   v         v
              +----------------------+     +-------------------------+
              |    Store Service     |     |    Delivery Service     |
              |    (Participant)     |     |      (Participant)      |
              +----------+-----------+     +------------+------------+
                         |                              |
                         v                              v
                  [( Store DB )]                 [( Delivery DB )]

The Atomicity Invariant

To guarantee fulfillment within 10 minutes:

• A food packet must physically exist in the store.
• A delivery agent must be present and immediately available.

If the platform confirms the order while the delivery agent is unavailable, the food sits waiting, spoiling the 10-minute promise. If an agent is assigned but the food packet is out of stock, the agent is blocked and idle.

Therefore, the order placement must be atomic across service boundaries: $\text{Order Placed} \iff (\text{Food Reserved} \land \text{Agent Reserved})$

If either resource is unavailable, the transaction must fail cleanly, and the user must be informed that the order could not be placed.

---

2. Distributed Resource Contention & 2PC Mechanics

When hundreds of users place orders simultaneously, concurrent transactions compete for a limited pool of inventory and delivery agents. Direct assignment without a two-phase protocol leads to race conditions, double bookings, and inconsistent system states.

The Two-Phase Commit Protocol decomposes the transaction into two distinct operational steps:

Order Service (Coordinator)           Store Service                Delivery Service
           |                                |                             |
           |--- Phase 1: Prepare/Reserve -->|                             |
           |    (Reserve Food Packet)       |                             |
           |<-- Success / Failure ----------|                             |
           |                                                              |
           |--- Phase 1: Prepare/Reserve -------------------------------->|
           |    (Reserve Delivery Agent)                                  |
           |<-- Success / Failure ----------------------------------------|
           |
     [All Prepared?]
      /          \
   (YES)         (NO)
    /              \
   v                v
Phase 2: Commit   Phase 2: Abort / Rollback
(Assign Resources) (Release Reserved Resources)

Phase 1: Preparation (Reservation Phase)

Instead of directly binding a resource to an order, the system temporarily holds (reserves) the resource for an unassigned pending transaction.

• The Order Service asks the Store Service to reserve a food packet.
• The Order Service asks the Delivery Service to reserve an agent.
• At this stage, resources are marked as exclusive and unavailable to any other concurrent transaction, but they are not yet permanently committed to any specific order ID.

Phase 2: Commit (Assignment Phase)

Once all participants confirm that they have successfully prepared (reserved) their respective resources, the coordinator triggers the commit step:

• The reserved food packet is assigned to the generated order_id.
• The reserved delivery agent is assigned to the generated order_id.
• The user is shown “Order Placed”.

Why Separate Reservation from Assignment?

Decoupling reservation from assignment minimizes the blast radius of failures:

1. High-Contention vs. Low-Contention Operations: Finding and claiming an unreserved resource is a contention-heavy operation requiring locks. Once reserved, contention drops to zero because no other thread is attempting to modify that specific row.
2. High Commit Probability: Because resources are locked during Phase 1, Phase 2 consists only of a trivial row update by ID (setting order_id). The likelihood of failure in Phase 2 is reduced to transient infrastructure issues (e.g., node downtime or network drops).

---

3. Database Schema Design

To implement fine-grained locking and avoid table-level bottlenecks, tables must be designed at the individual resource granularity rather than aggregate counters.

Store Service Schema

Instead of a simple table tracking remaining_count: 5, inventory is tracked as discreet physical, barcoded items:

-- High-level menu item / catalog
CREATE TABLE food (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL
);

-- Individual physical packets in the micro-warehouse
CREATE TABLE packets (
    id INT PRIMARY KEY AUTO_INCREMENT,
    food_id INT NOT NULL,
    is_reserved BOOLEAN NOT NULL DEFAULT FALSE,
    order_id INT DEFAULT NULL,
    INDEX idx_food_status (food_id, is_reserved, order_id),
    CONSTRAINT fk_packets_food FOREIGN KEY (food_id) REFERENCES food(id)
);

Delivery Service Schema

The delivery service maintains physical agents and their availability flags:

CREATE TABLE agents (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    is_reserved BOOLEAN NOT NULL DEFAULT FALSE,
    order_id INT DEFAULT NULL,
    INDEX idx_agent_status (is_reserved, order_id)
);

Both schemas leverage two state-tracking columns:

• is_reserved: A boolean indicating whether the resource is locked in Phase 1.
• order_id: The identifier of the finalized order assigned during Phase 2.

---

4. Low-Level Implementation (Go + SQL)

Step 1: Reserving Resources (Phase 1 / Prepare)

During reservation, an atomic SQL transaction selects the first available resource using SELECT ... FOR UPDATE (or a row lock) and marks it reserved.

Store Service: Reserve Food Packet

func (s *StoreService) ReserveFood(ctx context.Context, foodID int) (int, error) {
    tx, err := s.db.BeginTx(ctx, nil)
    if err != nil {
        return 0, err
    }
    defer tx.Rollback()

    // Find the first unreserved packet not linked to any order with row-level lock
    var packetID int
    query := `
        SELECT id FROM packets 
        WHERE food_id = ? AND is_reserved = FALSE AND order_id IS NULL 
        LIMIT 1 
        FOR UPDATE`
    
    err = tx.QueryRowContext(ctx, query, foodID).Scan(&packetID)
    if err != nil {
        return 0, fmt.Errorf("no food packets available")
    }

    // Mark as reserved
    _, err = tx.ExecContext(ctx, 
        "UPDATE packets SET is_reserved = TRUE WHERE id = ?", packetID)
    if err != nil {
        return 0, err
    }

    return packetID, tx.Commit()
}

Delivery Service: Reserve Delivery Agent

func (s *DeliveryService) ReserveAgent(ctx context.Context) (int, error) {
    tx, err := s.db.BeginTx(ctx, nil)
    if err != nil {
        return 0, err
    }
    defer tx.Rollback()

    var agentID int
    query := `
        SELECT id FROM agents 
        WHERE is_reserved = FALSE AND order_id IS NULL 
        LIMIT 1 
        FOR UPDATE`

    err = tx.QueryRowContext(ctx, query).Scan(&agentID)
    if err != nil {
        return 0, fmt.Errorf("no delivery agents available")
    }

    _, err = tx.ExecContext(ctx, 
        "UPDATE agents SET is_reserved = TRUE WHERE id = ?", agentID)
    if err != nil {
        return 0, err
    }

    return agentID, tx.Commit()
}

---

Step 2: Committing Resources (Phase 2 / Commit)

Once both participants acknowledge successful reservations, the coordinator invokes the commit endpoints to attach the order ID and release the temporary reservation flag.

Store Service: Book Food Packet

func (s *StoreService) BookFood(ctx context.Context, foodID int, orderID int) error {
    tx, err := s.db.BeginTx(ctx, nil)
    if err != nil {
        return err
    }
    defer tx.Rollback()

    var packetID int
    query := `
        SELECT id FROM packets 
        WHERE food_id = ? AND is_reserved = TRUE AND order_id IS NULL 
        LIMIT 1 
        FOR UPDATE`
    
    err = tx.QueryRowContext(ctx, query, foodID).Scan(&packetID)
    if err != nil {
        return fmt.Errorf("no reserved packet found to book")
    }

    _, err = tx.ExecContext(ctx, 
        "UPDATE packets SET is_reserved = FALSE, order_id = ? WHERE id = ?", 
        orderID, packetID)
    if err != nil {
        return err
    }

    return tx.Commit()
}

Delivery Service: Book Agent

func (s *DeliveryService) BookAgent(ctx context.Context, orderID int) error {
    tx, err := s.db.BeginTx(ctx, nil)
    if err != nil {
        return err
    }
    defer tx.Rollback()

    var agentID int
    query := `
        SELECT id FROM agents 
        WHERE is_reserved = TRUE AND order_id IS NULL 
        LIMIT 1 
        FOR UPDATE`

    err = tx.QueryRowContext(ctx, query).Scan(&agentID)
    if err != nil {
        return fmt.Errorf("no reserved agent found to book")
    }

    _, err = tx.ExecContext(ctx, 
        "UPDATE agents SET is_reserved = FALSE, order_id = ? WHERE id = ?", 
        orderID, agentID)
    if err != nil {
        return err
    }

    return tx.Commit()
}

---

Step 3: The Coordinator Workflow

The OrderService orchestrates the two phases over HTTP or RPC:

func (o *OrderService) PlaceOrder(foodID int, orderID int) error {
    // --- PHASE 1: PREPARE ---
    // 1. Reserve Food
    if err := o.callReserveFood(foodID); err != nil {
        return fmt.Errorf("order failed: food item unavailable")
    }

    // 2. Reserve Agent
    if err := o.callReserveAgent(); err != nil {
        // In production, trigger an explicit abort/unreserve for food here
        return fmt.Errorf("order failed: no delivery agents available")
    }

    // --- PHASE 2: COMMIT ---
    // 3. Book Food
    if err := o.callBookFood(foodID, orderID); err != nil {
        return fmt.Errorf("critical: food assignment failed")
    }

    // 4. Book Agent
    if err := o.callBookAgent(orderID); err != nil {
        return fmt.Errorf("critical: agent assignment failed")
    }

    return nil // Order placed successfully
}

---

5. Simulating Failures & Edge Cases

Consider a system state initialized with:

• 15 Food Packets (Food ID = 1)
• 10 Delivery Agents

If 10 concurrent requests hit PlaceOrder:

• All 10 succeed. 10 food packets are assigned (is_reserved=false, order_id!=null), and all 10 delivery agents are assigned.
• Remaining resources: 5 unreserved food packets, 0 delivery agents.

Now, fire 10 more concurrent requests:

Order Request 11 -> Reserve Food: SUCCESS (Packet #11 reserved)
Order Request 11 -> Reserve Agent: FAILED (0 agents available)
--> Result: Order Rejected ("Delivery agent not available")

The Perpetual Reservation Problem

When Request 11 fails at the agent reservation stage, what happens to Packet #11?

Because the preparation phase updated is_reserved = TRUE, and Phase 2 was never triggered, Packet #11 remains perpetually reserved. When subsequent requests arrive, they cannot use Packet #11, even though it was never tied to a finalized order.

+-----------+-----------------+--------------+--------------------------------------+
| Packet ID |  is_reserved    |   order_id   |                Status                |
+-----------+-----------------+--------------+--------------------------------------+
|   1 - 10  |      FALSE      |   1001-1010  | Fully Booked                         |
|  11 - 15  |      TRUE       |     NULL     | Perpetually Reserved (Leaked Lock)   |
+-----------+-----------------+--------------+--------------------------------------+

Eventually, all remaining 5 packets get stuck in a reserved state. New orders will now fail with food not available, even though the store physically still has 5 burgers sitting on the shelf.

---

6. Production Mitigations: Preventing Resource Leaks

In standard 2PC theory, a coordinator failure leaves participants in an in-doubt state, holding locks indefinitely. To make 2PC resilient in production systems, three core mechanisms are used:

1. Reservation Timers / TTL

Never reserve resources indefinitely. Every reservation must carry an expiration timestamp:

ALTER TABLE packets ADD COLUMN reserved_until TIMESTAMP NULL;
ALTER TABLE agents ADD COLUMN reserved_until TIMESTAMP NULL;

A query searching for available items simply considers expired reservations as unreserved:

SELECT id FROM packets 
WHERE food_id = ? 
  AND (is_reserved = FALSE OR reserved_until < NOW()) 
  AND order_id IS NULL 
LIMIT 1 FOR UPDATE;

2. Explicit Rollback (Compensation on Abort)

If the coordinator encounters an error during Phase 1 (e.g., agent reservation fails after food reservation succeeds), it must issue explicit HTTP/RPC rollback requests to release previously reserved resources:

if err := o.callReserveAgent(); err != nil {
    // Compensating Action
    go o.callUnreserveFood(foodID, reservedPacketID)
    return fmt.Errorf("order failed: delivery agent unavailable")
}

3. Background Sweeper / Janitor Daemon

A periodic reconciliation background job scans for orphaned reservations (is_reserved = TRUE AND order_id IS NULL AND reserved_until < NOW()) and resets is_reserved = FALSE. This guarantees that transient network partitions or process crashes cannot permanently exhaust inventory.

---

7. Trade-offs: Two-Phase Commit vs. The Saga Pattern

Dimension	Two-Phase Commit (2PC)	Saga Pattern (Choreography / Orchestration)
Consistency	Strong Consistency (Isolation via row locks)	Eventual Consistency
Resource Locking	Resources locked between Phase 1 and Phase 2	No distributed locks; updates committed immediately
Throughput	Lower due to database lock durations	High throughput, highly concurrent
User Experience	Order confirmation is strictly atomic	Order confirmed early, compensated later if failure occurs
System Complexity	Simpler state transitions, vulnerable to coordinator failures	Requires writing complex compensating workflows for every step

For ultra-low-latency physical fulfillment promises (such as a 10-minute SLA), 2PC or a reservation-based variant is frequently preferred over eventual consistency. Compensating a failed order after the food has already been cooked and the user notified creates a terrible customer experience. Reserving upfront guarantees capacity before making commitments.

---

Summary & Key Takeaways

1. Atomic Guarantees Over Microservices: When an operation cannot tolerate partial success across decoupled services, a two-phase protocol ensures that all preconditions are validated before the business commit occurs.
2. Isolate Contention in Phase 1: Use the preparation phase to handle contention-heavy lookups (FOR UPDATE) and lock the exact resources needed. This reduces Phase 2 to a deterministic, low-risk key-value update.
3. Granular Schemas: Model individual inventory units (e.g., barcoded packets) rather than single aggregate counters to leverage native row-level database locking.
4. Mandatory Timeouts: A distributed transaction protocol without reservation TTLs or janitor reconciliation processes will inevitably leak resources during network partitions or node failures.
5. Client Transparency: The client either receives a definitive success receipt or a clear, deterministic failure message; the system never makes fulfillment promises it lacks the capacity to deliver.