Dragisa Krsmanovic

MySQL Connector/J driver has built-in feature for load balancing.

If you have a cluster of read/write MySQL servers. Putting loadbalance: in the JDBC URL will ensure both read and write operations are distributed across servers.
jdbc:mysql:loadbalance://master1,master2,master3…/database?loadBalanceBlacklistTimeout=5000&loadBalanceConnectionGroup=cgroup&loadBalanceEnableJMX=true&autoReconnect=true&autoReconnectForPools=true

What we needed is all write operations to go to master server and read-only operations to be equally distributed among multiple read-only slaves.

For that you need to:

1. Use special JDBC driver: com.mysql.jdbc.ReplicationDriver
2. Set replication: in the URL:
   jdbc:mysql:replication://master,slave1,slave2…/database?loadBalanceBlacklistTimeout=5000&loadBalanceConnectionGroup=ugc&loadBalanceEnableJMX=true&autoReconnect=true&autoReconnectForPools=true

After setting our connection pool like this, all load still ended up going to our single read/write master server.

The reason is that, for the ReplicationDriver to know that queries can go to read-only slaves, two conditions need to be met:

1. Auto commit needs to be turned off. (*)
2. Connection needs to be set to read-only.

^(*)There is a workaround to allow auto commit: Connector/J load-balancing for auto-commit-enabled deployments

Turns out, even if transaction is set to read-only, neither Spring nor JPA providers like Hibernate or EclipseLink will set JDBC connection to readOnly.

To ensure JDBC Connection is set to read-only, I created an annotation and a simple AOP interceptor.

Here is an example code:

@Aspect
public class ReadOnlyConnectionInterceptor implements Ordered {

    private int order;
    private EntityManager entityManager;

    public void setOrder(int order) {
        this.order = order;
    }

    @Override
    public int getOrder() {
        return order;
    }

    @PersistenceContext
    public void setEntityManager(EntityManager entityManager) {
        this.entityManager = entityManager;
    }

    @Around("@annotation(readOnlyConnection)")
    public Object proceed(ProceedingJoinPoint pjp, ReadOnlyConnection readOnlyConnection) throws Throwable {

        Connection connection = entityManager.unwrap(java.sql.Connection.class);

        boolean autoCommit = connection.getAutoCommit();
        boolean readOnly = connection.isReadOnly();

        try {
            connection.setAutoCommit(false);
            connection.setReadOnly(true);

            return pjp.proceed();

        } finally {
            // restore state
            connection.setReadOnly(readOnly);
            connection.setAutoCommit(autoCommit);
        }
    }
}

One important thing is to set priority of this interceptor to be lower than the Spring’s transaction interceptor. That way we want to make sure transaction is created before we start modifying the connection. In other words, you would set order for @Transactional to be less than order for your new interceptor:
<tx:annotation-driven order="10">
    <bean id="readOnlyInterceptor" class="com.myproject.ReadOnlyConnectionInterceptor">
        <property name="order" value="20"></property>
    </bean>
</tx:annotation-driven>

Now, if you want queries to execute on your pool of slave databases, you can just set an annotation on the service method:
@Transactional(readOnly=true)
@ReadOnlyConnection
public Result serviceMethod(...) {
    ...
}

References:

Driver/Datasource Class Names, URL Syntax and Configuration Properties for Connector/J
Todd Farmer’s blog about Connector/J
Load balancing across MySQL servers using JDBC by Todd Huss
Aspect Oriented Programming with Spring