How to Ensure Data Consistency Between Database and Cache: Common Pitfalls and Best Solutions

1. Common Solutions

Typical cache usage: check cache, if hit return; if miss query the database, store the result in cache, then return. This simple flow can cause stale data when the database row is updated after being cached.

Four strategies for handling cache updates after a write operation:

Write cache first, then write database.

Write database first, then write cache.

Delete cache first, then write database.

Write database first, then delete cache.

2. Write Cache First, Then Write Database

Updating the cache directly during a write can lead to dirty cache if the subsequent database write fails, resulting in severe inconsistency where the cache holds data that does not exist in the database.

This approach is rarely used in production because of the risk of stale cache.

3. Write Database First, Then Write Cache

Writing the database before the cache avoids the "dirty data" problem but introduces new challenges, especially under high concurrency.

3.1 Cache Write Failure

If the cache write fails after the database commit, the database contains the new value while the cache still holds the old value, causing data inconsistency.

3.2 High‑Concurrency Issue

When multiple write requests operate concurrently, a later request may write a newer value to the database, while an earlier request later overwrites the cache with an older value, leaving the cache stale.

3.3 Resource Waste

Writing the cache after every database update can waste CPU and memory, especially when the cached value requires expensive computation.

4. Delete Cache First, Then Write Database

Deleting the cache before the database write can still lead to inconsistency in concurrent scenarios.

4.1 High‑Concurrency Issue

A read request may repopulate the cache with stale data before the write completes, causing the cache to hold an outdated value.

4.2 Cache Double Delete

Perform a second cache deletion after a short delay (e.g., 500 ms) to ensure that any stale value written by a concurrent read is removed.

5. Write Database First, Then Delete Cache

This approach has the lowest probability of inconsistency, but cache‑deletion failures must still be handled.

6. What to Do When Cache Deletion Fails?

Introduce a retry mechanism. Synchronous retries (e.g., three attempts) can be used for low‑traffic interfaces, while high‑traffic services should employ asynchronous retries via separate threads, thread pools, retry tables, message queues, or binlog listeners.

7. Scheduled Tasks

Use a scheduled job to retry cache deletions, recording a retry count in a dedicated table and attempting up to five times before marking the operation as failed.

8. Message Queues

When cache deletion fails, send a MQ message (e.g., RocketMQ). Consumers retry deletion several times; if all attempts fail, the message is moved to a dead‑letter queue for manual handling.

9. Binlog Listening

After a database write, rely on MySQL binlog (e.g., via Canal) to trigger cache deletion. Combine this with the same retry mechanisms used for MQ to handle deletion failures.