Transactional Outbox Pattern

Introduction

The service performs both database operations (insert/update/delete) and event notifications in a single operation within distributed systems. A dual write operation occurs when an application writes to two different systems, such as a database and an SNS topic. For instance, a microservice might need to write data to a database and notify other systems about the changes. If one of these operations fails, it could lead to inconsistent data.

Let's consider the below example,

The transaction is committed, but the notification fails due to service unavailability or another issue. In this scenario, the consumer does not receive the message, although the data is stored in the database. In this article, we will explore how to resolve this data inconsistency issue.

Perform both DB operation and message notification in the single transaction

We can perform both database operations and send notifications to another system in a single database transaction.

public async Task CreateOrder(Order orderDetails)
{
    using (IDbContextTransaction transaction = await _context.Database.BeginTransactionAsync())
    {
        try
        {
            // Write operation in the database
            Order order = await repository.Save(orderDetails);

            // Send notification
            await eventPublisher.PublishEvent(order);

            await transaction.CommitAsync();
        }
        catch (Exception)
        {
            await transaction.RollbackAsync();
        }
    }
}

Disadvantages

• It is not a bad practice to send an HTTP request as part of the transaction.
• The below possible issues that could happen,

The message notification will be sent successfully, but the database transaction might fail to commit due to a database error or a service crash. In this situation, the consumer will receive the notification, but the data will not be stored in the database.

Transactional Outbox Pattern

The transactional outbox pattern ensures that database updates and notifications are performed atomically in a single operation, maintaining data consistency.

The components are,

1. Service (Sender): The service that performs database operations and sends messages.
2. Database: The database that stores business entities and the message outbox.
3. Outbox Table: A table that stores messages to be sent.
4. Outbox Processor: A service that reads the outbox table, sends messages to the message broker, and updates the message status or deletes the message from the table.

The Outbox Pattern ensures reliable event publishing. Instead of publishing events directly to the message broker, messages are written to an “outbox” table within the same database. Both the business data and outbox data are written in a single transaction. The outbox processor reads messages with a “pending” status from the outbox table, publishes them to the message broker, and then updates the message status to “Completed” or deletes the message from the table to prevent data growth.

Example Code

Service

public async Task CreateOrder(Order orderDetails)
{
    using (IDbContextTransaction transaction = await _context.Database.BeginTransactionAsync())
    {
        try
        {
            // Write into Order table
            Order order = await orderRepository.Save(orderDetails);

            // Build the message
            OutboxMessage msg = BuildMessage(order);

            // Write into outbox table
            await outboxRepository.Save(msg);

            await transaction.CommitAsync();
        }
        catch (Exception)
        {
            await transaction.RollbackAsync();
        }
    }
}

Outbox Processor

public async Task OutboxMsgPolling()
{
    var messageResponse = _dbRepository.PollOutboxMessage();

    foreach (var message in messageResponse)
    {
        try
        {
            // Publish message into SNS (Message broker)
            await _snsProcessor.PublishMessage(message.MsgData);

            // Update message status in the DB
            await _dbRepository.UpdateMessageStatus(message.MsgId);
        }
        catch (Exception ex)
        {
            Logger.LogError(ex.Message);
        }
    }
}

Advantages

• Messages are guaranteed to be sent only if the database transaction is committed.
• Messages are sent to the message broker (e.g., SNS) in the order they were sent by the application.

Disadvantages

• The outbox processor might publish a message more than once. For instance, if the outbox processor successfully publishes a message but crashes before updating the message status, the same message (duplicate) will be sent to the consumer again. However, there is no data or message loss. The consumer side might need to handle duplicate messages.

Summary

In this article, you have learned the following topics,

• What is a Transactional Outbox Pattern?
• How to Implement Transactional Outbox Pattern and its Pros and Cons