How to Solve Double‑Write Consistency Issues with Message Queues

Introduction

A fictional interview illustrates a common mistake: writing to a database and Elasticsearch in the same code without guaranteeing atomicity.

Interviewer: "Introduce yourself!" Candidate: "I worked at an e‑commerce company and wrote to both the database and Elasticsearch directly." Interviewer: "How do you ensure atomicity?" Candidate: "I wait for the notification."

The article explores a generic data‑synchronization strategy, divided into three parts: background, drawbacks of double‑write, and an improved solution.

Background

The company started with a single database, added Redis for caching, introduced Elasticsearch for full‑text search, and later used Hadoop for analytical workloads. As data grew, the need for a reliable synchronization method became evident.

Core Questions

1. Are the data in the database, Redis, Elasticsearch, and Hadoop related or independent? They are related; they represent the same business entities in different formats. For example, a product record in the database is stored in Redis as the key product:pId:1 with the value:

{
    "pId": "1",
    "productName": "macbook"
}

2. How to guarantee consistency across these data sources? A naïve double‑write approach looks like:

ProductService{
    // ...
    public void syncData(){
        x1.writeDataSource1();
        x2.writeDataSource2();
    }
}

Drawbacks of Double Write

Consistency issue : Two concurrent clients may write different values, leading to divergent data as shown in the diagram below.

Without a subsequent update, the inconsistency may persist indefinitely.

Atomicity issue : Both writes must succeed or both fail, which double‑write cannot guarantee.

Improved Solution

Record every change in a message queue and let each downstream system replay the events in order.

With this architecture, consistency is ensured because all sources apply the same ordered stream of changes.

Atomicity is handled by tracking offsets; if a write fails, processing stops and resumes from the last successful offset.

For low‑latency scenarios, extract changes from the primary database (e.g., Oracle GoldenGate, MySQL Canal) and push them to Kafka, avoiding direct double writes.

Conclusion

The article discussed common data‑synchronization problems in multi‑store systems and presented a generic, queue‑based solution that addresses both consistency and atomicity challenges.