Ensuring Data Consistency with the Transactional Outbox Pattern on AWS

Introduction

In distributed systems, ensuring data consistency across services is crucial. When database transactions and message queues are not atomic, failures can cause inconsistencies.

The Transactional Outbox Pattern ensures database writes and message publishing occur atomically, avoiding lost events.

1. The Problem: Database and Messaging Are Not Atomic

Imagine an Order Service that:
Writes order data to Amazon RDS (PostgreSQL/MySQL).
Publishes an event (OrderPlaced) to Amazon SQS for other services (e.g., Inventory, Shipping).

🔥 The Failure Scenario:
The service writes an order to RDS ✅
The service fails to publish OrderPlaced to SQS ❌ (network error, timeout, etc.)

💥 Problem: The order is stored in the database, but other services (Inventory, Shipping) never receive the event.
🚨 Data inconsistency can lead to:
Customers being charged without order fulfillment.
No inventory updates, leading to overselling.
No shipping triggered, leading to support escalations.

2. The Transactional Outbox Pattern Solution

🔹 Step 1: Define Outbox Table

CREATE TABLE order_outbox (
    id SERIAL PRIMARY KEY,
    order_id UUID NOT NULL,
    event_type TEXT NOT NULL,
    payload JSONB NOT NULL,
    created_at TIMESTAMP DEFAULT NOW(),
    processed_at TIMESTAMP NULL
);

🔹 Step 2: Write Order & Event in One Transaction (TypeScript)

import { Pool } from 'pg';
const pool = new Pool({ connectionString: "your-db-connection" });

async function placeOrder(orderId: string, customerId: string, totalPrice: number) {
    const client = await pool.connect();
    try {
        await client.query('BEGIN');
        await client.query("INSERT INTO orders (order_id, customer_id, total_price) VALUES ($1, $2, $3)",
            [orderId, customerId, totalPrice]);
        await client.query("INSERT INTO order_outbox (order_id, event_type, payload) VALUES ($1, $2, $3)",
            [orderId, "OrderPlaced", JSON.stringify({ orderId, customerId, totalPrice })]);
        await client.query('COMMIT');
    } catch (error) {
        await client.query('ROLLBACK');
        throw error;
    } finally {
        client.release();
    }
}

🔹 Step 3: Poll & Publish Events (AWS Lambda / ECS Task)

import { Pool } from 'pg';
import { SNS } from 'aws-sdk';
const pool = new Pool({ connectionString: "your-db-connection" });
const sns = new SNS();
const topicArn = "arn:aws:sns:us-east-1:your-account-id:order-events";

async function processOutbox() {
    const client = await pool.connect();
    try {
        const { rows } = await client.query("SELECT id, payload FROM order_outbox WHERE processed_at IS NULL");
        for (const event of rows) {
            try {
                await sns.publish({ TopicArn: topicArn, Message: JSON.stringify(event.payload) }).promise();
                await client.query("UPDATE order_outbox SET processed_at = NOW() WHERE id = $1", [event.id]);
            } catch (error) {
                console.error(`Failed to send event ${event.id}:`, error);
            }
        }
    } catch (error) {
        console.error("Error processing outbox:", error);
    } finally {
        client.release();
    }
}

4. Why We Can't Just Rollback Transaction with SNS

🔹 SNS and Databases Don't Share Transactions

• Databases (RDS, PostgreSQL, MySQL) support ACID transactions.
• Amazon SNS is an external service and cannot be part of the same transaction.

🔹 Failure Scenarios Without an Outbox

• DB Insert Succeeds, SNS Fails:
  • Order is stored in RDS ✅
  • SNS message fails ❌
  • Other services never receive the event!
• SNS Message Sent, DB Insert Fails:
  • SNS message sent ✅
  • DB insert fails ❌
  • Other services see an event for an order that does not exist!

🔹 What Should Happen in Case of Failure?

• If SNS fails: The event remains in the outbox and can be retried later.
• If DB transaction fails: Nothing is committed, ensuring consistency.
• Retries & Dead-letter Queue (DLQ): Failed SNS messages can be moved to a DLQ for further debugging.

5. Real-World Use Cases

• E-commerce Order Processing: Ensuring orders are reliably propagated to inventory, billing, and shipping services.
• Payment Processing: Making sure transactions trigger correct notifications and reports.
• User Signups: Ensuring emails or notifications are reliably sent after new user registration.
• IoT Device Events: Ensuring telemetry data from devices is consistently published.

6. Difference from Saga Pattern

Feature	Transactional Outbox	Saga Pattern
Approach	Ensures event publishing is atomic	Splits transactions into smaller steps
Use Case	Ensures reliable messaging	Handles multi-step business processes
Complexity	Simpler	More complex
Example	Reliable event publishing to SNS	Hotel & flight booking with rollback steps

Conclusion

The Transactional Outbox Pattern ensures event consistency by making database transactions and event publishing atomic. Implementing it with Amazon RDS, SNS, and Lambda/ECS provides a reliable, scalable, and decoupled solution.

🚀 Have you implemented this pattern? Share your experience in the comments!

🔔 Follow for more AWS architecture insights!