Ensuring Consistency Between MySQL and Redis: Theory, Schemes, and Practical Implementation

Theory

The article begins by recalling six common theoretical solutions for keeping MySQL and Redis data consistent, illustrated with sequence diagrams that show how concurrent requests can cause inconsistencies.

Bad Schemes

1. Write MySQL then Redis

Both requests write to MySQL first and then to Redis; under high concurrency, a delayed Redis write can cause stale data in the cache.

2. Write Redis then MySQL

Similar to the first scheme; the diagram makes the problem obvious.

3. Delete Redis then Write MySQL

If the delete operation stalls, the cache may be empty while MySQL is still being updated, leading to inconsistency.

Good Schemes

4. Delete Redis → Write MySQL → Delete Redis (Cache Double Delete)

After deleting the cache, write to MySQL, then delete the cache again. The article advises against a fixed 500 ms sleep and recommends an asynchronous, serialized delete via a message queue.

5. Write MySQL → Delete Redis

This approach tolerates a single momentary inconsistency and is suitable for scenarios without strict real‑time guarantees.

6. Write MySQL → Binlog → Asynchronous Redis Update

By listening to MySQL binlog and asynchronously updating Redis (e.g., via Kafka), eventual consistency is achieved, though real‑time reads may still see stale data.

Scheme Comparison

The six methods are compared, highlighting why the author rejects some (e.g., writing Redis first) and prefers others based on concurrency, failure tolerance, and operational complexity.

Project Practice

Data Update

In a high‑throughput project the author adopts scheme 5 (write MySQL then delete Redis). Sample Java code shows a transactional method that updates MySQL and subsequently deletes the related Redis key, rolling back on any exception.

@Override
@Transactional(rollbackFor = Exception.class)
public void saveTag(TagReq tagReq) {
    TagDO tagDO = ArticleConverter.toDO(tagReq);
    // Write MySQL first
    if (NumUtil.nullOrZero(tagReq.getTagId())) {
        tagDao.save(tagDO);
    } else {
        tagDO.setId(tagReq.getTagId());
        tagDao.updateById(tagDO);
    }
    // Then delete Redis
    String redisKey = CACHE_TAG_PRE + tagDO.getId();
    RedisClient.del(redisKey);
}

Data Retrieval

Read‑through cache logic: try Redis first, if miss then query MySQL, store the result in Redis with an expiration time.

@Override
public TagDTO getTagById(Long tagId) {
    String redisKey = CACHE_TAG_PRE + tagId;
    // Try cache
    String tagInfoStr = RedisClient.getStr(redisKey);
    if (tagInfoStr != null && !tagInfoStr.isEmpty()) {
        return JsonUtil.toObj(tagInfoStr, TagDTO.class);
    }
    // Fallback to DB and cache the result
    TagDTO tagDTO = tagDao.selectById(tagId);
    tagInfoStr = JsonUtil.toStr(tagDTO);
    RedisClient.setStrWithExpire(redisKey, tagInfoStr, CACHE_TAG_EXPRIE_TIME);
    return tagDTO;
}

Test Cases

JUnit tests demonstrate saving a tag and querying it, verifying the Redis entry after the operation.

@Test
public void save() {
    TagReq tagReq = new TagReq();
    tagReq.setTag("Java");
    tagReq.setTagId(1L);
    tagSettingService.saveTag(tagReq);
    log.info("save success:{}", tagReq);
}

@Test
public void query() {
    TagDTO tagDTO = tagSettingService.getTagById(1L);
    log.info("query tagInfo:{}", tagDTO);
}

Conclusion

For real‑time consistency the author recommends "write MySQL then delete Redis" as the optimal trade‑off, while for eventual consistency the "write MySQL → Binlog → async Redis" approach is deemed the best solution.

Postscript

The article invites readers to download the full project from GitHub and mentions future integrations (RabbitMQ, Elasticsearch, Nacos, MongoDB, Prometheus) for further exploration.