Data Consistency Across Services

Scenario

In a microservices system, the Order, Payment, and Inventory services maintain separate databases.

A single business operation may require:

1. Creating an order

2. Processing payment

3. Reserving stock

A normal SQL transaction cannot safely cover all independent services and databases. Therefore, consistency is commonly maintained using eventual consistency and the Saga pattern.

1. Keep local transactions atomic

Each service owns its database and performs its changes using a local transaction.

For example, the Order service creates an order:

BEGIN TRANSACTION;

INSERT INTO dbo.Orders
(
    OrderId,
    CustomerId,
    Status
)
VALUES
(
    @OrderId,
    @CustomerId,
    'Pending'
);

COMMIT TRANSACTION;

Strong consistency is maintained inside the individual service.

2. Use the Saga pattern

A Saga divides the business process into multiple local transactions.

Order Created
    ↓
Payment Completed
    ↓
Inventory Reserved
    ↓
Order Confirmed

If one step fails, a compensating action reverses the earlier completed steps.

Example:

Order created
Payment completed
Inventory reservation failed
        ↓
Refund payment
Cancel order

There are two approaches:

• Choreography: services react to events without a central coordinator.

• Orchestration: a central Saga coordinator controls the steps.

Orchestration is often easier to understand for complex business workflows.

3. Use the Transactional Outbox pattern

A major problem occurs when the database update succeeds but event publishing fails.

Unsafe approach

Create order in database → succeeds
Publish OrderCreated event → fails

The Order service has data, but other services never receive the event.

Instead, save the business record and event in the same local transaction:

BEGIN TRANSACTION;

INSERT INTO dbo.Orders
(
    OrderId,
    CustomerId,
    Status
)
VALUES
(
    @OrderId,
    @CustomerId,
    'Pending'
);

INSERT INTO dbo.OutboxMessages
(
    MessageId,
    EventType,
    Payload,
    CreatedDate
)
VALUES
(
    NEWID(),
    'OrderCreated',
    @EventPayload,
    SYSDATETIME()
);

COMMIT TRANSACTION;

A background process reads the outbox table and publishes the event to a message broker.

This guarantees that the business change and its event are stored together.

4. Make consumers idempotent

Message brokers may deliver the same event more than once. Every consumer must safely handle duplicates.

CREATE UNIQUE INDEX UX_ProcessedMessages_MessageId
ON dbo.ProcessedMessages(MessageId);

Before processing, the consumer records the message ID. A duplicate message is ignored.

This prevents:

• Duplicate payments

• Duplicate stock reductions

• Duplicate orders

• Repeated email notifications

5. Handle failures safely

Use:

• Retry with exponential backoff

• Dead-letter queue for repeatedly failing messages

• Compensating transactions

• Timeouts

• Correlation IDs

• Monitoring and alerts

• Periodic reconciliation jobs

Do not retry permanent errors such as invalid payment information indefinitely.

Example flow

Order Service:
Create order + save OrderCreated event

Payment Service:
Process payment + publish PaymentCompleted

Inventory Service:
Reserve stock + publish StockReserved

Order Service:
Change order status to Confirmed

If payment fails:

PaymentFailed event
    ↓
Order Service changes order to Cancelled

If inventory fails after payment:

InventoryReservationFailed
    ↓
Payment Service issues refund
    ↓
Order Service cancels order

Interview Answer

I would allow each service to own its database and maintain strong consistency through local transactions. For operations spanning multiple services, I would use the Saga pattern with compensating actions. I would use the Transactional Outbox pattern to reliably publish database changes as events and make every consumer idempotent to prevent duplicate processing. Retries, dead-letter queues, correlation IDs, monitoring and reconciliation jobs would handle failures and temporary inconsistencies.

Key Points

• Avoid sharing one database between microservices.

• Use local transactions within each service.

• Use Saga for multi-service business operations.

• Use Transactional Outbox for reliable event publishing.

• Make message consumers idempotent.

• Use compensating actions instead of distributed rollback.

• Accept and monitor controlled eventual consistency.