MySQL & Redis Dual-Write Consistency: 4 Proven Strategies with Code & Interview Guide
This comprehensive guide analyzes four production-ready strategies for MySQL-Redis dual-write consistency, from basic cache-aside with delayed double-delete to binlog-based CDC, including Spring Boot code examples, concurrency pitfalls, and interview-focused scenario breakdowns.
MySQL and Redis Dual-Write Consistency: Core Problem
The challenge of keeping MySQL and Redis consistent is fundamentally a distributed systems data consistency problem. Because MySQL and Redis are independent storage systems, atomic updates across both are impossible. The goal is to achieve eventual consistency while maintaining performance and availability.
Approaches That Must Be Avoided
Update Redis first, then MySQL : Redis succeeds but MySQL fails → permanent inconsistency.
Update MySQL first, then Redis : Concurrent writes cause "write overwrite" race, producing dirty data.
Use distributed locks for both writes : Performance collapses, defeating the purpose of Redis caching.
Four Mainstream Correct Solutions
Solution 1: Update Database, Then Delete Cache (Most Common)
Pros: Simple, good performance, inconsistency probability extremely low.
Cons: Edge case remains: DB update succeeds but cache delete fails.
Applicable: 90%+ of business scenarios.
Solution 2: Delete Cache First, Then Update Database
Pros: Safer than "update DB then delete cache".
Cons: Concurrent reads cause "cache penetration" (stale data written back).
Fix: Use "delayed double delete" strategy.
Solution 3: Update Database + Message Queue Async Cache Delete
Pros: Solves "cache delete failure" with retry mechanism.
Cons: Adds MQ complexity, introduces latency.
Applicable: Businesses with higher consistency requirements.
Solution 4: MySQL Binlog-Based Eventual Consistency (Ultimate)
Pros: Fully decoupled, zero business code intrusion, highest consistency.
Cons: Most complex architecture, high ops cost.
Applicable: Large-scale internet companies, core high-concurrency high-consistency services.
Interview Deep-Dive: Extreme Scenario Analysis
Scenario 1: Why Delete Cache Instead of Updating It?
Concurrent writes cause "write overwrite" if updating cache.
Many cached values are computed aggregates, not simple DB mappings; recomputation is expensive.
Lazy-loading: recalculate only when cache is read, saving resources.
Scenario 2: Extreme Inconsistency in "Update DB Then Delete Cache"
Conditions:
Cache just expired.
Thread A reads DB, gets stale value.
Thread B updates DB, then deletes cache.
Thread A writes stale value back to cache.
Result: Cache holds stale data until next update or expiration.
Mitigations:
Set reasonable cache TTL (fallback).
Use delayed double delete.
Adopt binlog async delete.
Production Best Practices
Base: Update MySQL, then delete Redis cache.
Fallback: All caches have TTL (15 min – 2 hours).
Enhanced: On cache delete failure, retry via MQ.
Ultimate: Core services use Canal to listen binlog and async update cache.
Production-Grade Code Implementations
Based on Spring Boot 3.x + Redis 7.x + RabbitMQ 3.x
1. Base Solution: Update DB Then Delete Cache (with Retry)
@Service
@Slf4j
public class UserService {
@Autowired
private UserMapper userMapper;
@Autowired
private RedisTemplate<String, Object> redisTemplate;
@Autowired
private ThreadPoolTaskExecutor cacheExecutor;
@Transactional(rollbackFor = Exception.class)
public void updateUser(User user) {
// 1. Update database first
int rows = userMapper.updateById(user);
if (rows == 0) {
log.warn("Update user failed, user not found: {}", user.getId());
return;
}
// 2. Async delete cache, non-blocking
String cacheKey = "user:info:" + user.getId();
cacheExecutor.execute(() -> {
try {
redisTemplate.delete(cacheKey);
log.info("Cache deleted: {}", cacheKey);
} catch (Exception e) {
log.error("First cache delete failed, retrying: {}", cacheKey, e);
try {
redisTemplate.delete(cacheKey);
log.info("Retry cache delete succeeded: {}", cacheKey);
} catch (Exception ex) {
log.error("Retry cache delete failed, alert needed: {}", cacheKey, ex);
alertService.sendAlert("Cache delete failed", cacheKey);
}
}
});
}
}2. Enhanced: Delayed Double Delete (Solves Concurrent Read/Write Dirty Data)
@Service
@Slf4j
public class UserService {
@Value("${cache.delay-delete-time:500}")
private long delayDeleteTime;
@Transactional(rollbackFor = Exception.class)
public void updateUserWithDelayDelete(User user) {
String cacheKey = "user:info:" + user.getId();
// 1. First delete
redisTemplate.delete(cacheKey);
// 2. Update DB
userMapper.updateById(user);
// 3. Schedule second delete after delay
cacheExecutor.schedule(() -> {
if (Boolean.TRUE.equals(redisTemplate.hasKey(cacheKey))) {
redisTemplate.delete(cacheKey);
log.info("Delayed cache delete succeeded: {}", cacheKey);
}
}, delayDeleteTime, TimeUnit.MILLISECONDS);
}
}3. High-Reliability: MQ Async Delete (Solves Delete Failure)
// Producer
@Service
@Slf4j
public class CacheDeleteProducer {
@Autowired
private RabbitTemplate rabbitTemplate;
public void sendDeleteMessage(String cacheKey) {
try {
CacheDeleteMessage message = new CacheDeleteMessage(
UUID.randomUUID().toString(), cacheKey);
rabbitTemplate.convertAndSend("cache-exchange", "cache.delete", message);
log.info("Sent cache delete message: {}", message);
} catch (Exception e) {
log.error("Failed to send cache delete message: {}", cacheKey, e);
throw new RuntimeException("Send cache delete message failed", e);
}
}
}
// Consumer
@Component
@Slf4j
public class CacheDeleteConsumer {
@Autowired
private RedisTemplate<String, Object> redisTemplate;
@RabbitListener(queues = "cache-delete-queue")
public void handleDeleteMessage(CacheDeleteMessage message) {
String cacheKey = message.getCacheKey();
String messageId = message.getMessageId();
// Idempotency check
String idempotentKey = "cache:delete:idempotent:" + messageId;
if (Boolean.TRUE.equals(redisTemplate.hasKey(idempotentKey))) {
log.info("Message already processed, skip: {}", messageId);
return;
}
try {
redisTemplate.delete(cacheKey);
log.info("Consumed message, cache deleted: {}", cacheKey);
redisTemplate.opsForValue().set(idempotentKey, "1", 24, TimeUnit.HOURS);
} catch (Exception e) {
log.error("Failed to delete cache via message: {}", message, e);
throw new RuntimeException("Process cache delete message failed", e);
}
}
}
@Data
@AllArgsConstructor
@NoArgsConstructor
public class CacheDeleteMessage implements Serializable {
private String messageId;
private String cacheKey;
}4. Ultimate: Canal Binlog Listener (Zero Business Intrusion)
@Component
@Slf4j
public class CanalBinlogListener {
@Autowired
private RedisTemplate<String, Object> redisTemplate;
@PostConstruct
public void startCanalClient() {
CanalConnector connector = CanalConnectors.newSingleConnector(
new InetSocketAddress("127.0.0.1", 11111),
"example", "", "");
try {
connector.connect();
connector.subscribe(".*\\..*");
connector.rollback();
while (true) {
Message message = connector.getWithoutAck(100);
long batchId = message.getId();
int size = message.getEntries().size();
if (batchId == -1 || size == 0) {
Thread.sleep(1000);
continue;
}
for (CanalEntry.Entry entry : message.getEntries()) {
if (entry.getEntryType() == CanalEntry.EntryType.ROWDATA) {
CanalEntry.RowChange rowChange = CanalEntry.RowChange.parseFrom(entry.getStoreValue());
CanalEntry.EventType eventType = rowChange.getEventType();
if (eventType == CanalEntry.EventType.UPDATE || eventType == CanalEntry.EventType.DELETE) {
String tableName = entry.getHeader().getTableName();
for (CanalEntry.RowData rowData : rowChange.getRowDatasList()) {
String id = getPrimaryKey(rowData, tableName);
String cacheKey = tableName + ":info:" + id;
redisTemplate.delete(cacheKey);
log.info("Canal deleted cache: {}", cacheKey);
}
}
}
}
connector.ack(batchId);
}
} catch (Exception e) {
log.error("Canal client error", e);
} finally {
connector.disconnect();
}
}
private String getPrimaryKey(CanalEntry.RowData rowData, String tableName) {
for (CanalEntry.Column column : rowData.getBeforeColumnsList()) {
if (column.getName().equals("id")) {
return column.getValue();
}
}
throw new RuntimeException("Primary key not found: " + tableName);
}
}Core Technical Challenges & Solutions Summary
Cache delete failure : Network glitch or Redis downtime causes delete to fail, permanent inconsistency. Solution : 1. Immediate retry once 2. MQ async retry (max 3×) 3. Scheduled scan for expired cache 4. Dead-letter queue alerts for manual intervention. Highlight : Multi-level retry, eventual consistency safety net.
Concurrent read/write dirty data : Cache expires, reader gets stale value, writer updates then deletes, reader writes stale back. Solution : 1. Cache TTL fallback 2. Delayed double delete 3. Read-write lock (read-heavy). Highlight : Delayed double delete lowest cost, best effect.
Delay time hard to tune : Too short: reader hasn't written back; too long: hurts consistency. Solution : 1. Load-test avg DB query latency, set 2-3× 2. Dynamic config via config center, no restart 3. Core services use Canal. Highlight : Dynamic config + load-test data, flexible.
MQ message loss / duplicate consumption : MQ crash or network partition loses messages; retries cause duplicates. Solution : 1. Persistent messages + producer confirms 2. Consumer manual ACK 3. Message-ID idempotency 4. Dead-letter queue for failures. Highlight : Idempotency is root fix for duplicates.
Large key deletion blocks Redis : Deleting big keys (lists, hashes) blocks Redis main thread. Solution : 1. Redis 4.0+ UNLINK async delete 2. Split large keys, batch delete 3. Avoid caching large objects. Highlight : Async delete keeps Redis available.
Cache penetration / breakthrough / avalanche : These cache issues often accompany dual-write problems, worsening inconsistency. Solution : 1. Bloom filter for penetration 2. Mutex lock for breakthrough 3. Random TTL offset for avalanche. Highlight : Combine multiple strategies for full protection.
Distributed transaction : DB update and cache delete cannot be atomic. Solution : 1. Accept eventual consistency 2. Seata TCC (not recommended, poor perf) 3. Binlog CDC (recommended). Highlight : CDC fully decoupled, best performance.
Sharding scenario : Data changes spread across multiple shards, cache sync complex. Solution : 1. Canal listens all shard binlogs 2. Unified cache key naming 3. Per-table cache sync logic. Highlight : Business unaware, supports horizontal scaling.
Interview Bonus Points
Explain why the extreme inconsistency in "update DB then delete cache" has extremely low probability : requires simultaneous cache expiry + slow read + fast write.
Explain why updating cache is discouraged : besides write overwrite, low cache utilization and high compute cost.
Map solution selection to business scenarios from project experience: non-core uses base, core uses MQ+Canal.
Mention degradation strategy : when Redis unavailable, read directly from DB to preserve availability.
Cite monitoring metrics : cache hit rate, cache delete failure rate, MQ backlog, Canal sync latency.
Real Interview Simulation
Interviewer:
"You've used Redis as cache. Design a high-concurrency read/write scenario: how to guarantee MySQL-Redis dual-write consistency? Walk me through your thinking."
Candidate:
"We've hit this in production and built a battle-tested approach. First, let me highlight two classic mistakes because understanding why they're wrong matters more than just stating the right answer."
Interviewer:
"Go ahead."
Candidate:
"First trap: 'Delete cache, then update DB' . A sequence diagram shows the race: a read request slips between delete and DB update, writes stale data back to cache, causing long-term dirty data. This reproduces easily under high concurrency."
Candidate:
"Second trap: 'Update DB, then update cache' . Concurrent writes cause ordering issues."
"Final cache holds 10, DB holds 20 — inconsistency. Plus cache update adds serialization overhead and write contention, so we abandoned updating cache entirely."
Interviewer:
"So how did you land in production?"
Candidate:
"Core is a Cache-Aside variant — just two steps:
1. Update database first 2. Then delete cache (note: delete, not update)
Read path stays check cache → miss → query DB → write back, with TTL on every key as safety net.
Why delete not update? Delete is idempotent, avoids concurrent write overwrite, and enables lazy loading — no read, no memory cost."
Interviewer:
"Any edge case where 'update DB then delete cache' still fails?"
Candidate:
"Yes, but probability is tiny. Requires read/write concurrency with perfect bad timing: - Read misses cache, reads stale DB (writer hasn't deleted cache yet) - Writer updates DB, deletes cache - Read writes its stale value back to cache Window is extremely narrow, but we still protect with delayed double delete ."
Interviewer:
"How to implement delayed double delete? Synchronous sleep?"
Candidate:
"Absolutely not synchronous sleep — blocks main thread. We use thread pool async :
update DB → delete cache → submit async task (sleep 300ms) → delete cache againIf second delete fails, push to MQ for retry, guaranteeing eventual delete. 300ms derived from API P99 latency, covers read-write-back window."
Interviewer:
"If cache update logic is complex or spans services, delete-only isn't enough?"
Candidate:
"We adopted MySQL Binlog async update . Business code only writes DB, ignores cache. Canal subscribes binlog, pushes to RocketMQ, dedicated cache-update service consumes. Architecture:"
"Pros: zero business intrusion, MQ retry + DLQ guarantee eventual consistency. Cons: ~100ms latency, only for eventual-consistency scenarios."
Interviewer:
"What about funds/inventory where even brief inconsistency is unacceptable?"
Candidate:
"For those, we don't chase cache strong consistency. - Treat Redis as read-only cache with very short TTL (e.g., 1s); source of truth stays MySQL. - Writes go straight to DB; reads on cache miss query DB, using distributed lock to prevent hot-key breakthrough. - True consistency guaranteed by DB transactions; Redis is just a performance layer, not part of core consistency logic."
Interviewer:
"Summarize your design philosophy."
Candidate:
"Three sentences: 1. 🚫 Never update cache, only delete — leverage delete idempotency to avoid write overwrite. 2. ⏳ Every cache must have TTL — even if all sync mechanisms fail, TTL is the ultimate fallback. 3. 📭 Async retry + message compensation — guarantee eventual consistency, don't sacrifice availability for strong consistency. Solution selection depends on business tolerance:"
General read-heavy : Update DB + delete cache + delayed double delete → Eventual (tiny window)
Cross-system / complex updates : Binlog + MQ async refresh → Eventual (~100ms delay)
Funds / inventory core : Short TTL cache + rely on DB transaction → Strong
That's our production-validated full stack."
Core Code Snippets
Delayed Double Delete (Async + Fallback Retry)
@Service
public class CacheAsideService {
@Autowired
private RedisTemplate<String, Object> redisTemplate;
@Autowired
private ThreadPoolTaskExecutor delayDeleteExecutor;
@Autowired
private RocketMQTemplate rocketMQTemplate;
@Transactional(rollbackFor = Exception.class)
public void updateDataAndDeleteCache(String key, Object newData) {
// 1. Update MySQL (transactional)
updateDatabase(newData);
// 2. First cache delete
redisTemplate.delete(key);
// 3. Submit async delayed second delete
delayDeleteExecutor.execute(() -> {
try {
Thread.sleep(300); // based on API P99
redisTemplate.delete(key);
} catch (Exception e) {
// 4. Second delete failed → send to MQ for final compensation
rocketMQTemplate.convertAndSend("cache-delete-retry-topic", key);
}
});
}
}Highlights:
Delete action outside transaction boundary, but @Transactional ensures DB success before cache delete.
Async thread pool isolates, doesn't block business thread.
MQ compensation ensures "cache will eventually be deleted", no omissions.
Binlog Consumer Idempotent Processing
@Component
@RocketMQMessageListener(topic = "binlog-cache-sync", consumerGroup = "cache-sync-group")
public class BinlogCacheConsumer implements RocketMQListener<MessageExt> {
@Autowired
private RedisTemplate<String, Object> redisTemplate;
@Autowired
private BloomFilterManager bloomFilterManager;
@Override
public void onMessage(MessageExt msg) {
BinlogEvent event = JSON.parseObject(msg.getBody(), BinlogEvent.class);
String cacheKey = event.getCacheKey();
// Idempotency: unique msgId + Bloom filter
String msgId = msg.getMsgId();
if (bloomFilterManager.mightContain("consumed_msg", msgId)) {
if (redisTemplate.opsForValue().get("msg_consumed:" + msgId) != null) {
return;
}
}
// Process cache update/delete
if (event.getType() == EventType.DELETE) {
redisTemplate.delete(cacheKey);
} else {
redisTemplate.opsForValue().set(cacheKey, event.getData(), 30, TimeUnit.MINUTES);
}
// Mark consumed (Bloom + Redis dual record)
bloomFilterManager.put("consumed_msg", msgId);
redisTemplate.opsForValue().set("msg_consumed:" + msgId, "1", 2, TimeUnit.HOURS);
}
}Highlights:
Bloom filter + Redis dual idempotency, tiny memory overhead, guarantees duplicate consumption safety.
Consumption failure triggers RocketMQ auto-retry, DLQ for manual fallback.
Operations are idempotent ( delete, set), so even extreme re-consumption is safe.
Hot-Key Breakthrough Protection (Distributed Lock + Cache Null)
public Object getDataWithCache(String key) {
Object value = redisTemplate.opsForValue().get(key);
if (value != null) return value;
// Distributed lock to prevent cache breakthrough
String lockKey = "lock:" + key;
boolean locked = redisTemplate.opsForValue().setIfAbsent(lockKey, "1", 5, TimeUnit.SECONDS);
if (locked) {
try {
// Double-check
value = redisTemplate.opsForValue().get(key);
if (value != null) return value;
// Query DB
value = queryFromDatabase(key);
// Cache null value to prevent penetration
redisTemplate.opsForValue().set(key, value == null ? "null" : value,
value == null ? 1 : 30, TimeUnit.MINUTES);
return value;
} finally {
redisTemplate.delete(lockKey);
}
} else {
// Didn't get lock, short wait then retry
Thread.sleep(50);
return getDataWithCache(key);
}
}Highlights: SETNX lightweight distributed lock prevents breakthrough.
Cache null value prevents penetration, short TTL (1 min) reduces memory.
Lock auto-expires in 5s, deadlock safety net.
Technical Challenges & Solutions
Concurrent write overwrite : Update DB then update cache, race causes stale overwrite. Solution : Never update cache, only delete — delete idempotency avoids.
Read/write dirty data : Gap between DB update and cache delete lets reader write stale back. Solution : Delayed double delete + async compensation MQ.
Cache delete failure : Network jitter or Redis fault causes delete fail, dirty data persists. Solution : Async retry + MQ dead-letter queue, guarantee "eventual delete".
Binlog consumption order : Same key updates may be shuffled across partitions. Solution : Hash partition by key, same consumer processes sequentially.
Binlog consumption idempotency : RocketMQ at-least-once delivery causes duplicates. Solution : Bloom filter + Redis marker, dual idempotency.
Cache penetration : Malicious queries for non-existent data hit DB directly. Solution : Cache null object + Bloom filter pre-filter.
Cache breakthrough : Hot key expiry floods DB with requests. Solution : Distributed lock (SETNX) + double-check.
Cache avalanche : Mass keys expire simultaneously, DB overwhelmed. Solution : TTL with random offset, avoid same expiry.
Brief inconsistency window : Eventual consistency means users may read stale data. Solution : Business accepts + short TTL fallback, core paths read DB directly.
In interviews I stress: "Designing a consistency scheme is essentially trading space/complexity for business-acceptable eventual consistency. There's no perfect strong consistency, only scenario-matched trade-offs. Every Thread.sleep(300) and every MQ compensation in our code is a battle against distributed uncertainty."
Signed-in readers can open the original source through BestHub's protected redirect.
This article has been distilled and summarized from source material, then republished for learning and reference. If you believe it infringes your rights, please contactand we will review it promptly.
How this landed with the community
Was this worth your time?
0 Comments
Thoughtful readers leave field notes, pushback, and hard-won operational detail here.
