Ensuring Data Consistency Between MySQL and Redis in High‑Concurrency Scenarios

In high‑concurrency environments, direct access to MySQL for a large number of requests can cause performance bottlenecks, so Redis is often introduced as a cache layer to reduce database load. However, because MySQL and Redis are separate data stores, keeping them consistent becomes a critical challenge.

1. Causes of Data Inconsistency

Heavy concurrent access makes the database the weakest link.

Using Redis as a buffer means reads go to the cache while writes must update both cache and database.

When a write updates the database, the corresponding cache entry may become stale, leading to inconsistency.

2. Cache‑Delete Order Problems

Both "delete‑first then write" and "write‑first then delete" can produce stale data:

2.1 Delete‑First

If the cache is deleted before the database write completes, another thread may read the empty cache, fetch old data from MySQL, and repopulate the cache with stale information.

Subsequent database updates then cause divergence between Redis and MySQL.

2.2 Write‑First

If the database write succeeds but the subsequent cache deletion fails (e.g., the thread crashes), subsequent reads will retrieve the old cached value, again causing inconsistency.

Concurrent reads and writes cannot guarantee ordering, so the cache may diverge from the database.

3. Solutions

3.1 Delayed Double‑Delete Strategy

3.1.1 Basic Idea

Delete the cache both before and after the database write, with a short sleep between the two deletions to allow in‑flight reads to finish.

Pseudo‑code:

public void write(String key, Object data) {
    redis.delKey(key);
    db.updateData(data);
    Thread.sleep(500); // sleep time should be tuned based on business read latency
    redis.delKey(key);
}

3.1.2 Detailed Steps

Delete the cache.

Write to the database.

Sleep for a configurable period (e.g., 500 ms or longer based on read‑operation latency).

Delete the cache again.

The sleep duration should be determined by measuring the typical read‑request processing time and adding a few hundred milliseconds; in many cases a 1‑second pause is sufficient.

3.1.3 Cache Expiration as a Key Point

Setting an appropriate TTL for cached entries ensures eventual consistency: once the TTL expires, the cache is refreshed from the database.

All writes are authoritative in the database; after expiration, fresh data is loaded back into Redis.

3.1.4 Drawbacks

In the worst case, data may be inconsistent during the cache TTL window.

The extra delete and sleep increase write latency.

3.2 Asynchronous Cache Update (Based on MySQL Binlog)

3.2.1 Overall Idea

Capture data‑modifying operations via MySQL binlog.

Publish binlog events to a message queue (e.g., Kafka, RabbitMQ).

Consumers read the events and apply incremental updates to Redis.

Read path continues to hit Redis for hot data; write path remains on MySQL.

3.2.2 Redis Update Process

Two modes are possible:

Full load – write the entire dataset to Redis in one batch.

Incremental – apply only the changes (INSERT, UPDATE, DELETE) extracted from the binlog.

This mechanism mirrors MySQL’s master‑slave replication, using the binlog to keep cache and database synchronized.

4. Summary

When high consistency is required, first identify the root causes of cache‑database divergence.

Two practical approaches are the delayed double‑delete strategy and asynchronous cache updates via binlog.

Setting an appropriate cache expiration time is essential for eventual consistency and must be tuned to the specific business workload.