Outbox pattern - Message Relay without duplicates and unordering for any SQL and NoSQL DB

Question

Dual write is a problem when we need to change data in 2 systems: a database (SQL or NoSQL) and Apache Kafka (for example). The database has to be updated and messages published reliably/atomically. Eventual consistency is acceptable but inconsistency is not.

Without 2 phase commit (2PC) dual write leads to inconsistency.

But in most cases 2PC is not an option.

Transactional Outbox is a microservice architecture pattern where a separate Message Relay process publishes the events inserted into database to a message broker.

Multiple Message Relay processes running in parallel lead to publishing duplicates (2 processes read the same records in the OUTBOX table) or unordering (if every process reads only portion of the OUTBOX table).

A single Message Relay process might publish messages more than once also. A Message Relay might crash after processing an OUTBOX record but before recording the fact that it has done so. When Message Relay restarts, it will then publish the same message again.

How to implement a Message Relay in Transactional Outbox patterns, so that risk of duplicate messages or unordering is minimal and the concept works with all SQL and NoSQL databases?

Answer 1

Exactly-once delivery guarantee instead of at-least-once with Transactional Outbox pattern can hardly be achieved.

Consumers of messages published by a Message Relay have to be idempotent and filter duplicates and unordered messages.

Messages must include

• current state of an entity (instead of only changed fields aka change event, "delta"),
• ID header or field,
• version header or field.

ID header/field can be used to detect duplicates (determine that the message has been processed already).

Version header/field can be used to determine that more recent version of the message has been processed already (if a consumer received msg_a: v1, v2, v4 then it have to drop v3 of msg_a when it will arrive because more recent version v4 of msg_a has been processed already).

Message Relay extracted into a separate microservice and run in a single replica (.spec.replicas=1 in Kubernetes) and updated using Recreate Deployment strategy (.spec.strategy.type=Recreate in Kubernetes) when all existing Pods are killed before new ones are created (instead of RollingUpdate Deployment strategy) doesn't help to solve problem with duplicates. A Message Relay might crash after processing an OUTBOX record but before recording the fact that it has done so. When Message Relay restarts, it will then publish the same message again.

Having multiple active-active Message Relay instances allows achieving higher availability but increases probability of publishing duplicates and unordering.

For fast fail-over active-standby cluster of Message Relays can be implemented based on

• Kubernetes Leader Election using sidecar k8s.io/client-go/tools/leaderelection
• Redis Distributed Lock (Redlock)
• SQL lock using SELECT ... FOR UPDATE NOWAIT
• etc.

As explained by Martin Klappmann distributed locks without fencing are broken and only minimizes the chance of multiple leaders (for a short time) in leader election.