Database Read-Write Separation Architecture Design and Data Consistency Guarantee

Problem Description
Read-write separation is a common database architecture optimization strategy. It improves system throughput and concurrent processing capabilities by distributing read and write operations to different database nodes. However, this architecture also introduces challenges to data consistency. This topic requires you to deeply understand the design principles of read-write separation, master its typical architecture patterns, and focus on analyzing how to guarantee data consistency in different business scenarios.

Solution Process Explanation

1. Basic Principles of Read-Write Separation

• Core Objective: Reduce the pressure on the primary database by separating read/write loads. Write operations are concentrated on the master node, while read operations are distributed to multiple slave nodes.
• Technical Basis: Based on the master-slave replication mechanism of databases (such as MySQL's binlog replication), the master node synchronizes data changes to slave nodes.
• Key Components:
  • Load Balancer: Routes requests based on SQL type (read/write).
  • Data Synchronization Channel: Ensures eventual consistency between master and slave (but with inherent delay).

2. Typical Architecture Design Patterns

• Single Master, Single Slave: Suitable for simple scenarios with more reads than writes; the slave node can also serve as a cold backup for the master.
• Single Master, Multiple Slaves: Horizontally scales read performance by adding slave nodes, but attention must be paid to cumulative synchronization delay.
• Dual-Master Replication: Two master nodes act as master and slave to each other, providing high availability for writes, but requires conflict resolution (e.g., avoiding cross-writes through partitioning).
• Cascading Replication: Master → Slave1 → Slave2, reduces synchronization pressure on the master node but increases delay risk due to intermediate nodes.

3. Root Causes of Data Consistency Challenges

• Master-Slave Synchronization Delay: Reading from a slave immediately after a write operation may return stale data (dirty read).
• Solution Categories:
  • Strong Consistency: Sacrifices performance to ensure read operations always retrieve the latest data.
  • Eventual Consistency: Accepts temporary inconsistency and employs strategies to minimize its impact.

4. Core Strategies for Guaranteeing Consistency
Strategy 1: Force Read from Master

• Principle: For a specific period after a write operation (e.g., within 3 seconds), force read requests to be routed to the master node.
• Implementation: Record the timestamp of the last write operation at the business layer or use middleware (e.g., ShardingSphere's hint-based forced routing).
• Applicable Scenarios: Core business operations with high consistency requirements (e.g., checking balance after payment).

Strategy 2: Delay Monitoring and Intelligent Routing

• Principle: Monitor master-slave delay in real-time (e.g., Seconds_Behind_Master). If the delay exceeds a threshold, automatically route read requests to the master node.
• Tools: Implemented via database monitoring systems (e.g., Prometheus) or built-in middleware features.
• Advantage: Dynamically adapts to network fluctuations, balancing consistency and performance.

Strategy 3: GTID or Binlog Position Verification

• Principle: After a write operation, record the Global Transaction ID (GTID) or binlog position. Read requests carry this information. If the slave node's synchronization progress hasn't reached that point, the request is routed to the master.
• Example:
  1. A user places an order (write to master), and the response carries GTID="uuid:100".
  2. When querying the order, the middleware compares the slave node's GTID progress. If it hasn't reached 100, read from the master.
• Applicable Scenarios: Financial-grade consistency requirements, but implementation complexity is high.

Strategy 4: Semi-Synchronous Replication

• Principle: When the master node commits a transaction, it waits until at least one slave node has received the binlog before returning success (though the slave may not have fully applied it).
• Effect: Reduces the probability of master-slave delay but cannot completely avoid it (as the slave still needs time to apply the log).
• Note: MySQL semi-synchronous replication requires enabling plugins (e.g., rpl_semi_sync_master).

5. Business Layer Compromise Solutions

• Hierarchical Read Strategy:
  • Force read from master for critical business (e.g., account queries).
  • Allow read from slave for non-critical business (e.g., product reviews).
• Asynchronous Design:
  • After a write operation, notify the read service via a message queue to update the cache, avoiding direct database reads.
  • Example: After successful payment, send an MQ message; the read service consumes it and updates the order status in Redis.

6. Design Trade-off Summary

7. Practical Considerations

• Monitoring & Alerting: Must deploy master-slave delay monitoring and set thresholds (e.g., alert when delay >5 seconds).
• Failover: When the master node fails, ensure data integrity on slave nodes to prevent dirty data from being promoted to master.
• Testing & Validation: Test business logic fault tolerance by injecting delays (e.g., simulating network delay with tc).

Through the above steps, you can flexibly choose consistency strategies based on business scenarios, enjoying the performance benefits of read-write separation while effectively controlling the risk of data inconsistency.