Setup Spring transactions for MySQL Replication

This post describes how to setup Spring transaction to connect to MySQL database with Replication to direct all write operations to the master and read operations to both master and slaves.

Database setup

The easiest way to setup MySQL database with replication for testing is via Amazon AWS. Create a RDS instance with MySQL as a master and then create a Read Replica from the master. Note AWS uses native MySQL replication to propagate database changes from  the master to the slaves.

JDBC connection

To connect to MySQL Replication using JDBC, made the following 2 changes to your spring config:

  1. Replace the jdbc driver class, e.g. com.mysql.jdbc.Driver, with com.mysql.jdbc.ReplicationDriver.
  2. Modify jdbc connection string to format

             jdbc:mysql:replication://<master db url>,<slave 1 db url>/<dbname>

See MySQL documentation here for list of configuration properties that can be appended to the jdbc URL.

Note the ReplicationDriver wraps one read and one write jdbc connection and can be used transparently with jdbc connection pools such as cp30.

Spring transaction

Use the readOnly attribute of the @Transaction annotation of Spring to direct a transaction to either the master or slave.

For write operations, use @Transactional(readOnly = false) and the database operations will go to the master only

For read only operations, use @Transactional(readOnly = true) and the database operations can go to the slave.

Note:

  1. There are a few old articles on the web indicating the readOnly attribute is ignored. This seems to be outdated and the attribute is working as expected in Spring 3. I am using Spring 3.2.

To verify the setup, I have the following Spring test class:

@RunWith(SpringJUnit4ClassRunner.class)
@TransactionConfiguration(transactionManager="transactionManager", defaultRollback=true)
@ContextConfiguration("classpath:spring/app-config-test.xml")
@Transactional(readOnly = true)
@ActiveProfiles(profiles={"aws"})
public class ProductRepositoryImplTest {
@Autowired
 private SessionFactory sessionFactory;
@Autowired
 @Qualifier("productRepository")
 private IProductRepository repository;
// ... setup details omitted here
@Transactional(readOnly = false)
 @Test
 @Repeat(value = 100)
 public void testReplicationWrite() {
      repository.create(createEntity());
 }
 @Transactional(readOnly = true)
 @Test(expected = GenericJDBCException.class)
 public void testReplicationWriteFail() {
      repository.create(createEntity());
 }
 @Transactional(readOnly = true)
 @Test
 @Repeat(value = 100)
 public void testReplicationRead() {
      repository.findProductByName(RandomStringUtils.randomAlphabetic(10));
 }
}

Note:

  1. The test class above tests the productRepository bean which implements standard CRUD operations (details omitted here) for the entity Product.
  2. The first test method testReplicationWrite() will pass as the readOnly attribute of the @Transactional annotation is set to false.
  3. The second test method testReplicationWriteFail() will throw a GenericJDBCException, as expected by the test method. This confirms that the readOnly attribute works by sending the database operation to the slave and hence the exception. If you remove the “expected = GenericJDBCException.class”, the test will fail with the following error: “org.hibernate.exception.GenericJDBCException: Connection is read-only. Queries leading to data modification are not allowed”
  4. You can also verify on the MySQL master and slave by using the “show processlist” command while the tests are running. The @Repeat annotation runs the tests multiple (100) times to keep the connection process running. Of course, you can also enable the query log for this.
Advertisements

About Raymond Lee
Professional Java/EE Developer, software development technology enthusiast.

Comments are closed.