How to Keep Redis and MySQL Data Consistent: Proven Strategies and Pitfalls

Problem Background

When Redis is placed in front of MySQL as a read‑through cache, the read flow becomes:

If the key exists in Redis, the value is returned directly.

If the key is missing, the application reads the value from MySQL, writes it into Redis, and returns it.

This introduces two consistency states:

Cache and MySQL hold the same data.

Cache is empty while MySQL holds the latest data.

Because Redis and MySQL do not share a transactional boundary, any interruption of these states can produce stale data in the cache.

1. Delete Cache Before Updating the Database

Typical flow:

Thread 1: DEL key   // remove cache
Thread 1: UPDATE MySQL ...
Thread 2: GET key   // cache miss
Thread 2: SELECT FROM MySQL (old value)
Thread 2: SET key (old value)   // repopulates cache
Thread 1: COMMIT

The race condition occurs because Thread 2 can read the old database value before Thread 1 commits, causing the cache to be repopulated with stale data.

2. Delayed Double Delete (Cache‑Delay‑Delete)

To mitigate the race, a second DEL is issued after a short pause:

// after updating DB
DEL key          // first delete
sleep(Δt)       // Δt > time for a concurrent read‑populate
DEL key          // second delete

Choosing Δt is critical:

If Δt is too short, the second delete may run before the concurrent thread has written the stale value, leaving the stale entry in the cache.

If Δt is too long, overall throughput and latency suffer.

The optimal value is usually a few tens of milliseconds, measured by profiling the maximum time a read‑populate operation takes under peak load.

3. Update Database First, Then Delete Cache

In this variant the DB transaction is committed before the cache is cleared:

BEGIN;
UPDATE MySQL ...;
COMMIT;          // DB now holds new value
DEL key;         // remove stale cache entry

Even with a short window between COMMIT and DEL, a race can happen:

Thread 1 updates the DB but has not yet executed DEL.

Thread 2 experiences a cache miss, reads the old DB value, and writes it back to Redis.

Thread 1 finally deletes the key, but the stale value written by Thread 2 remains if the delete fails or is delayed.

To reduce this window, a short‑lived lock can be acquired atomically with a Lua script:

-- lock.lua
local lockKey = KEYS[1]
local ttl = tonumber(ARGV[1])
if redis.call('SETNX', lockKey, '1') == 1 then
  redis.call('PEXPIRE', lockKey, ttl)
  return 1
else
  return 0
end

Application flow:

if redis.call('EVALSHA', lockSha, 1, 'lock:'..key, 100) == 1 then
  -- safe to delete after DB commit
  DEL key
  DEL lock:key   -- optional explicit unlock
else
  -- another instance holds the lock; wait or abort
end

The lock TTL (e.g., 100 ms) should be longer than the expected DB commit time but short enough to avoid blocking other requests.

4. Message‑Queue‑Based Retry Deletion

If a cache deletion fails (network glitch, Redis restart, etc.), a reliable pattern is to publish a deletion request to a message queue after the DB transaction commits. A consumer repeatedly attempts the delete until it succeeds, guaranteeing eventual consistency.

// Producer (in the same transaction or immediately after commit)
UPDATE MySQL ...;
DEL key;               // best‑effort delete
PUBLISH mq_topic {"key":"myKey"};

// Consumer
while true {
  msg = POP mq_topic;
  if REDIS.DEL(msg.key) == 1 {
    ACK msg;   // deletion succeeded
    break;
  }
  // optional back‑off before retry
}

This approach decouples the retry logic from business code, but it does introduce a dependency on a messaging system (Kafka, RabbitMQ, etc.). The delete‑and‑publish steps should be wrapped in a single atomic operation if the DB supports it (e.g., using out‑box pattern).