Issues When Using Transactions with Database Read/Write Splitting

The article discusses the problems that may arise when using transactions in a read/write‑separated database architecture, focusing on two main questions: whether a transaction can mistakenly operate on a slave database and whether transaction isolation levels and propagation can cause connection‑pool deadlocks.

For the first question, it explains that a thread may select a slave for a SELECT operation, but when the transaction starts, Spring’s AOP clears the ThreadLocal holding the slave identifier, ensuring the transaction switches to the master and avoiding master‑slave switching errors.

For the second question, it shows that if both the main transaction and a secondary operation (such as GetSeq) share the same DataSource/connection pool, exhausting the pool can lead to deadlock; the recommended fix is to use a separate pool or set PROPAGATION_REQUIRES_NEW.

The implementation of dynamic data source routing is described using Spring’s AbstractRoutingDataSource, a singleton DynamicDataSource, and a ThreadLocal‑based DynamicDataSourceHolder to store the current lookup key (master or slave).

Transaction routing is achieved via Spring AOP, where proxies intercept method calls, clear the ThreadLocal before transaction interception, and ensure the correct master is used during transactional operations.

Transaction management details include the use of @Transactional, the handling of multiple transaction managers, and XML configuration examples that bind the appropriate DataSource and transaction manager beans.

The article also briefly reviews MySQL’s transaction isolation levels, noting that InnoDB (the default engine) uses REPEATABLE READ, and explains how isolation and locking provide atomicity, consistency, and durability.