Understanding Master/Slave Read‑Write Splitting and Data Consistency with Sharding‑JDBC

When MySQL tables grow to tens of millions of rows, query performance degrades; beyond indexing and SQL optimization, a master‑slave (read‑write splitting) architecture is often adopted, but it introduces a common consistency issue: delayed data propagation from the master to the slaves.

The master writes data and records it in the binary log (BinLog), which an I/O thread asynchronously replicates to the slave. Read queries are routed to the slave, so after a write the latest data may not yet be visible on the slave, leading to occasional “stale read” problems that worsen as traffic increases.

Sharding‑JDBC is a lightweight Java framework that sits on top of the JDBC layer, offering enhanced services such as transparent read‑write splitting, full compatibility with JDBC and popular ORM frameworks (JPA, Hibernate, MyBatis, Spring JDBC Template), and support for various connection pools (Druid, HikariCP, etc.) and databases (MySQL, Oracle, SQLServer, PostgreSQL, any SQL‑92 compliant DB).

Key read‑write splitting features include a master‑multiple‑slave configuration, automatic routing of non‑read operations to the master, Spring namespace support, and a Hint‑based mechanism that can force all subsequent reads in a thread to use the master.

Using the HintManager, developers can guarantee that a query reads the most recent data:

HintManager hintManager = HintManager.getInstance();
hintManager.setMasterRouteOnly();
ArticleEntity article = baseMapper.queryObject(id, wid);

However, forcing every read to the master can overload the primary server and affect replication performance, so it should be applied judiciously based on business needs.

An alternative is to tolerate a short delay and perform the read asynchronously after a brief pause, for example using Java Future:

Callable
c1 = new Callable
() {
@Override
public ArticleEntity call() throws Exception {
Thread.sleep(2000); // wait for replication
return articleService.get(id);
}
};
FutureTask
f = new FutureTask<>(c1);
new Thread(f).start();
ArticleEntity article = f.get();

The article also provides the Maven dependency for Sharding‑JDBC and a sample YAML configuration for data sources and the master‑slave rule, as well as a Spring‑boot configuration class that creates the master‑slave DataSource and transaction manager.

Overall, the piece offers a practical guide to handling read‑write consistency in large‑scale MySQL deployments using Sharding‑JDBC, while highlighting trade‑offs and alternative strategies.