How to Keep MongoDB and Relational DB Dual Writes Consistent

Introduction

Ensuring that data written simultaneously to MongoDB and a relational database remains consistent is a frequent problem in modern backend systems. The article examines typical misunderstandings, outlines the distinct roles of cache and MongoDB, and presents concrete solutions for maintaining dual‑write integrity.

Common Misconceptions

Many developers assume that the consistency issues of Redis‑cache and relational‑database dual writes are identical to those of MongoDB‑database dual writes. While the underlying principle—keeping two storage layers in sync—is similar, the usage scenarios differ, leading to different optimal solutions.

How We Use Cache (Redis)

Redis is employed to improve performance by serving data from memory before falling back to the database. The typical flow is:

Check Redis; if the key exists, return the cached value.

If the key is missing, query the database, store the result in Redis, and return it.

The most common technique to keep cache and database consistent is the delayed double delete pattern.

How We Use MongoDB

MongoDB serves as a high‑availability, distributed document store for large or JSON‑like payloads. Core data is stored in the relational database, while non‑core, larger data resides in MongoDB. The read path first retrieves core data from the relational table, then uses the stored mongo id to fetch the corresponding document from MongoDB.

Ensuring Dual‑Write Consistency

Two primary strategies are discussed:

1. Write to the relational database first, then to MongoDB

This simple approach works when non‑core data loss is acceptable. However, if the network fails after the relational write, the MongoDB write may not occur, leaving the system with missing non‑core data and no rollback capability.

2. Write to MongoDB first, then to the relational database

Here, non‑core data is persisted in MongoDB before the core data is saved in the relational table. If the relational write fails, the MongoDB document becomes orphaned (garbage data). The article highlights two new challenges:

How to handle user‑initiated updates.

How to clean up orphaned MongoDB documents.

Handling User Updates

When a user modifies data, the process should create a new MongoDB document (generating a new mongo id) rather than updating the existing one. The relational record is then updated to reference the new mongo id. If the relational update fails, the old record still points to a valid MongoDB document, preserving readability.

To remove the now‑obsolete MongoDB document, a retry mechanism—implemented via a job, message queue, or RocketMQ consumer—can be used. Failed deletions are sent to a dead‑letter queue, and an alert email is triggered for manual inspection.

Cleaning Up Orphaned Data

Two cleanup strategies are proposed for MongoDB documents that lack a corresponding relational entry:

4.1 Scheduled Deletion

A periodic job scans recent MongoDB documents (e.g., those created 1–25 hours ago), checks for the existence of the associated mongo id in the relational table, and deletes those without matches. To improve performance, the scan range can be narrowed and parallelized with multiple threads.

4.2 Random Deletion

Inspired by Redis’s random eviction strategy, a lightweight job can randomly pick a small batch of candidate documents every few hundred milliseconds, verify their relational linkage, and delete the orphaned ones. This approach reduces CPU usage while gradually cleaning up garbage data.

Both methods can be combined with retry logic and dead‑letter handling to ensure eventual consistency.

Conclusion

By separating core and non‑core data, ordering writes appropriately, employing retry mechanisms, and scheduling targeted cleanup jobs, systems can achieve reliable dual‑write consistency between MongoDB and relational databases while minimizing the risk of orphaned data.

in_time < 当前时间-1 and in_time >= 当前时间-25