Segregating the read-only and read-write in Spring/J2EE Apps

Question

We using Spring, Spring-Data and JPA in our project.

For production servers, we would like to setup database cluster such that all read queries are directed to one server and all write queries are directed to another server.

This obviously will require some changes in the way the DAOs are built.

Does anyone know how to achieve this if one has, so far, been following cook-book style DAO creations using Spring-Data/JPA where a DAO implementation is responsible for both reads and writes? What kind of changes in architecture will be needed to segregate the two types of calls?

Answer 1

When using MySQL, it is common for Java developers to use Connector/J as the JDBC driver. Developers typically use the Connector/J com.mysql.jdbc.Driver class, with a URL such as jdbc:mysql://host[:port]/database to connect to MySQL databases.

Connector/J offers another driver called ReplicationDriver that allows an application to load-balance between multiple MySQL hosts. When using ReplicationDriver, the JDBC URL changes to jdbc:mysql:replication://master-host[:master-port][,slave-1-host[:slave-1-port]][,slave-2-host[:slave-2-port]]/database. This allows the application to connect to one of multiple servers depending on which one is available at any given point in time.

When using the ReplicationDriver, if a JDBC connection is set to read-only, the driver treats the first host declared in the URL as a read-write host and all others as read-only hosts. Developers can take advantage of this in a Spring application by structuring their code as follows:

@Service
@Transactional(readOnly = true)
public class SomeServiceImpl implements SomeService {
   public SomeDataType readSomething(...) { ... }

   @Transactional(readOnly = false)
   public void writeSomething(...) { ... }
}

With code like this, whenever the method readSomething is called, the Spring transaction management code will obtain a JDBC Connection and call setReadOnly(true) on it because the service methods are annotated with @Transactional(readOnly = true) by default. This will make all database queries from the readSomething method to go to one of the non-master MySQL hosts, load-balanced in a round-robin fashion. Similarly, whenever writeSomething is called, Spring will call setReadOnly(false) on the underlying JDBC Connection, forcing the database queries to go to the master server.

This strategy allows the application to direct all read-only traffic to one set of MySQL servers and all read-write traffic to a different server, without changing the application's logical architecture or the developers having to worry about different database hosts and roles.