How to Keep MongoDB and Relational DB Writes Consistent

Introduction

In a recent technical discussion, a colleague asked how to guarantee data consistency when writing simultaneously to MongoDB and a relational database. The author shares practical solutions based on real‑world experience.

Common Misconceptions

Many assume that dual‑write consistency issues for Redis and a database are identical to those for MongoDB and a database, but their usage scenarios differ, leading to distinct solutions.

How We Use Cache (Redis)

Redis cache improves system performance by serving data from cache first and falling back to the database when needed. The most common technique to keep cache and database consistent is the delayed double delete pattern.

For more details, see the author’s other article on cache‑database consistency.

How We Use MongoDB

MongoDB is a high‑availability, distributed document database that stores large JSON‑like data. In the author’s architecture, core data is written to the relational database, while non‑core (large) data is stored in MongoDB. The relational table keeps the MongoDB document ID to enable joint reads.

Ensuring Dual‑Write Consistency

1. Write Relational DB First, Then MongoDB

This simple approach writes core data to the database before inserting non‑core data into MongoDB. It works when non‑core data is optional, but if the database write succeeds and the MongoDB write fails, the system ends up with missing non‑core data, leading to inconsistency.

2. Write MongoDB First, Then Relational DB

Here, non‑core data is stored in MongoDB before the core data is saved in the relational database. If the database write fails, the MongoDB document remains without a corresponding relational record, creating orphaned data. Additionally, if the MongoDB document ID is not saved in the database, the data becomes unretrievable.

This method also raises two challenges:

How to handle user update operations?

How to clean up orphaned (garbage) data?

User Update Operations

When a user updates data, the author recommends inserting a new MongoDB document (generating a new ID) and simultaneously updating the relational table’s MongoDB ID field. If the database update fails, the old record still points to a valid MongoDB document, preserving readability.

To remove the now‑obsolete MongoDB document, a retry mechanism (using a job or MQ) is employed, with RocketMQ’s built‑in retry queue and a dead‑letter queue for persistent failures.

Cleaning Up Orphaned Data

4.1 Scheduled Deletion

A periodic job scans MongoDB documents, extracts their IDs, and checks the relational database. If the ID is absent, the MongoDB document is deleted. To improve performance, the scan limits the time window (e.g., documents created 25 hours to 1 hour ago) and can use multithreading.

4.2 Random Deletion

Inspired by Redis’s random‑deletion strategy for large‑scale cleanup, another job runs every 500 ms, randomly picking a small batch of documents within the time window for deletion, reducing CPU load.