Using MySQL for Persistent Data and Redis for Read‑Only Data: Consistency Strategies and High‑Concurrency Solutions

MySQL stores persistent data, while Redis provides read‑only data; after Redis starts, it loads data from the database.

Read requests: Non‑strongly consistent reads go to Redis, while strongly consistent reads query MySQL directly.

Write requests: Data is first written to the database, then Redis is updated (writing to Redis before MySQL can cause dirty cache if the transaction rolls back).

MySQL and Redis handle different data types

MySQL processes real‑time data such as financial or transaction records.

Redis handles data with lower real‑time requirements, like hot post rankings or friend lists.

In low‑concurrency situations, read operations prioritize Redis; if the data is missing, MySQL is queried and the result is written back to Redis. Write operations update MySQL first, then Redis (using MySQL triggers or Redis binlog parsing).

In high‑concurrency situations, reads remain the same, but writes become asynchronous: data is written to Redis and returned immediately, with periodic synchronization to MySQL.

Examples

1. When updating a product's inventory from 100 to 99, if the cache deletion fails after the database update, the cache still holds 100, causing inconsistency.

Solution: Delete the cache before updating the database; if cache deletion fails, abort the DB update. If DB update fails after successful cache deletion, the cache will be repopulated on the next read, preserving consistency.

2. Under high concurrency, if a cache deletion occurs before the database update completes, another request may read stale data from the database and repopulate the cache, leading to inconsistency.

Solution: Use a set of queues (e.g., 20) hashed by product ID. Push update requests into the appropriate queue, process them sequentially, and check the cache or queue for pending updates before serving reads. If a read finds no cache entry, it can wait on the queue or poll the cache for a short period (e.g., 200 ms) before falling back to the old database value.

High‑concurrency considerations

1. Read request latency: Ensure reads return within timeout limits; excessive update traffic can fill queues and cause timeouts, forcing reads back to the database.

2. Request volume: Conduct thorough load testing to determine peak QPS and adjust resources accordingly.

3. Routing for multiple service instances: All update and cache‑refresh requests must be routed to the same service instance (e.g., via Nginx) to maintain consistency.

4. Hot‑item routing: Popular items may overload a single server if their requests are not evenly distributed; however, if update frequency is low, the impact is limited.