MySQL Master/Slave Connection

Using Master/Slave Replication with ReplicationConnection

http://dev.mysql.com/doc/refman/5.0/en/connector-j-reference-replication-connection.html

springframework의 declarative txn. mgt.에서 readOnly=true를 주면 setReadOnly 메소드를 사용하지 않을까 ???

2008/03/13 이렇게 시도해 보니 진짜 생각한 대로 동작했다. 그런데 테스트할 때 문제가 있었다. AbstractTransactionalXXXTest를 상속 받은 경우에는 항상 rollback 되기 때문에 transaction aspect이 동작하지 않는 것 같다. 그래서 readOnly transactional method에서 CUD를 해도 exception이 발생하지 않았다. 그래서 AbstractJUnit4SpringContextTests를 사용했다.

이제 해 볼 것은 readOnly 메소드에서 transactional method를 호출할 때 제대로 master/slave로 호출이 분배되는지 확인하는 것이다.

이건 안된다.

readOnly 메소드가 transactional 메소드를 호출하면 read only connection에서 데이터를 변경하려고 시도했다고 exception을 발생시킨다.

Load Balancing JDBC Pool (lbpool)

http://code.tailrank.com/lbpool

예제)
1. replication.properties

replication.driverClassName=com.mysql.jdbc.ReplicationDriver
replication.url=jdbc:mysql://master-ip,slave-ip-1, slave-ip-2/dbname?autoReconnect=true
jdbc.username=xxx
jdbc.password=yyy

2. applicationContext.xml

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:p="http://www.springframework.org/schema/p"
xmlns:context="http://www.springframework.org/schema/context"
xmlns:tx="http://www.springframework.org/schema/tx"
xmlns:aop="http://www.springframework.org/schema/aop"
xsi:schemaLocation="
http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-2.5.xsd
http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-2.5.xsd
http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-2.5.xsd
http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-2.5.xsd">

<context:property-placeholder
location="classpath:replication.properties" />

<tx:annotation-driven transaction-manager="transactionManager" />

<context:component-scan base-package="net" />

<bean id="dataSource"
class="org.springframework.jdbc.datasource.DriverManagerDataSource"
p:driverClassName="${replication.driverClassName}"
p:url="${replication.url}" p:username="${jdbc.username}"
p:password="${jdbc.password}" />

<bean id="sqlMapClient"
class="org.springframework.orm.ibatis.SqlMapClientFactoryBean"
p:configLocation="classpath:sqlMap-config.xml"
p:dataSource-ref="dataSource" />

<bean id="sqlMapClientTemplate"
class="org.springframework.orm.ibatis.SqlMapClientTemplate"
p:sqlMapClient-ref="sqlMapClient" />

<bean id="transactionManager"
class="org.springframework.jdbc.datasource.DataSourceTransactionManager"
p:dataSource-ref="dataSource" />

<bean id="someDao"
class="net.SomeDaoImpl"
p:sqlMapClientTemplate-ref="sqlMapClientTemplate" />
</beans>

3. ManageArticleServiceImpl

@Service
@Transactional(readOnly=true)
public class ManageArticleServiceImpl implements ManageArticleService {
@Autowired
private SomeDao SomeDao;

@Autowired
private CountService countService;

@Transactional(readOnly=false, propagation = Propagation.REQUIRED)
public Article getArticle(Long articleId) {
Article article = someDao.find(articleId);

countService.read(article);

return article;
}
}

4. CountServiceImpl

@Service
@Transactional(readOnly=true)
public class CountServiceImpl implements CountService {
@Autowired
private SomeDao someDao;

@Transactional(readOnly=false, propagation = Propagation.REQUIRED)
public void read(Article article) {
article.read();
someDao.update(article);
}
}