Data consistency and the Outbox pattern

One of the most important aspects of every application that writes data is guaranteeing data consistency.

A basic example is the creation of an order that has items. Considering a relational database, the data is stored in two tables, Orders and OrderItems. Imagine that the application performs an INSERT into the Orders table and receives an error when trying to perform the INSERT into the OrderItems table. The system is now inconsistent: the user has an order with no items, and this may even prevent them from placing the order again.

An Orders application storing data in the database

What we want is for the order to be saved completely or for nothing to be saved at all. In relational databases the solution is to use transactions.

BEGIN TRANSACTION
  INSERT INTO TABLE Orders...
  INSERT INTO TABLE OrderItems...
COMMIT

If the second INSERT does not complete, the transaction must be rolled back, keeping the “all or nothing” behavior.

The dual write problem

Now let’s expand our scenario: an Orders microservice is responsible for keeping order data and notifying the other systems about placed orders. To communicate with the other services, a messaging solution will be used.

The application saves orders to the database and sends a message to a messaging system

So, when receiving a new order, a write must be made to the Orders table in the database and an order.created message must be sent to a queue. How do we keep consistency between the database and the messaging system? If, after inserting a record into the database, sending the message to the queue is not successful, the applications waiting for the message become inconsistent. Inverting the logic and sending the message before saving to the database produces a bigger problem, because downstream applications may receive messages about operations that were never recorded in the application that owns that data.

Keeping data consistent in distributed systems is a major challenge and several approaches have different trade-offs. Commonly, it is acceptable to guarantee that the system will be consistent at some point in the future. This approach is called eventual consistency.

Outbox Pattern

The idea of the outbox pattern is to use the database to guarantee that the message will be sent. The messages that will be sent to the messaging system must be stored in a table along with the sending status. When inserting a record into the Orders table, the message must be inserted too, all within a transaction.

BEGIN TRANSACTION
  INSERT INTO Orders...
  INSERT INTO OutboxMessages(message, status)...
COMMIT

This OutboxMessages table must be checked periodically for messages with pending status so they can be sent to the queue. This way there is a guarantee that the message will be sent at least once. Why at least once? Because the process of sending the message and updating the record’s sending status in the database is itself a dual write and is subject to failures. The operation to update the status as sent may fail, and therefore during the next search for pending messages that message would be sent again.

Idempotency

Considering the previous problem, the same message may be sent repeatedly to the queue. Thus, the systems that process these messages must be idempotent, that is, repeated executions of operations must not affect the result of the first operation. Some operations are idempotent by nature, such as assigning a specific value to a field of a table record. If executed multiple times, the final result is the same as executing it just once. In other situations, not guaranteeing idempotency can be catastrophic. Expanding our example, consider an application that receives messages about placed orders and decrements the stock of the sold products. If the order message is duplicated, the order will decrement twice the amount of purchased products from the stock.

The Products service processes the message of placed orders and updates its database

One way to implement idempotency for these cases is to use the database again. The application that consumes the messages must have a table to store the ids of processed messages. When executing the operation on the database — in our example an UPDATE on the Products table — the message id must be inserted into the processed messages table. Both operations must be performed within a transaction, because if the primary key constraint of the ProcessedMessages table returns an error (meaning the message was already processed), the product update must be rolled back.

BEGIN TRANSACTION
  UPDATE Products...
  INSERT INTO ProcessedMessages(Id) ...
COMMIT

Implementation

Using C#, there are a few library options that implement the Outbox pattern, such as CAP and Mass Transit. For idempotency I created Ziggurat, which implements the solution described in this post. You can see an example of CAP combined with Ziggurat here.

In Python there is the django-outbox-pattern library, which implements the outbox pattern and also guarantees idempotency in consumers.