Mysql master/slave replication .Connect to master even for read queries?

[Ashika Umagiliya]

I use mysql master/slave replication (write to master and reads to slaves) with ReplicationDriver.My connection URL is as follows :


"jdbc:mysql:replication://master:3306,slave1:3307,slave2:3308/sampledb?allowMasterDownConnections=true"


I use Spring + Spring MyBatis modules.

I have marked my transaction as readOnly as follows :

@Override
@Transactional(rollbackFor=Exception.class,readOnly=true)
public Sample getSample(SampleKey sampleKey) throws SampleException {
//Call MyBastis based DAO with "select" queries.
}


But when I see the transaction/db logs it shows that even for the "readOnly" transactions ReplicationDriver first hits master.
Notice the lines "Acquired Connection" and "Releasing JDBC connection" lines.

Why is this happening ?

1) If the readOnly=true , doesn't Spring set the readOnly(true) of the underlying Connection object ?

2) Even though it acquires connection to the "master" actual "select" query is executed on "slave1" ?



2014-03-19 12:32:28,280 DEBUG [http-8080-2] [AbstractPlatformTransactionManager.java:365] - Creating new transaction with name [com.rakuten.gep.foo.businesslogic.impl.SampleBusinessLogicImpl.getSample]: PROPAGATION_REQUIRED,ISOLATION_DEFAULT,readOnly; '',-java.lang.Exception
2014-03-19 12:32:28,390 DEBUG [http-8080-2] [DataSourceTransactionManager.java:204] - Acquired Connection [jdbc:mysql://master:3306/, UserName=root@10.174.10.72, MySQL Connector Java] for JDBC transaction
CACHED DAO
Trying to retrive from the Cache
2014-03-19 12:32:31,334 DEBUG [http-8080-2] [Slf4jImpl.java:47] - ooo Using Connection [jdbc:mysql://slave1:3307/, UserName=root@10.174.10.72, MySQL Connector Java]
2014-03-19 12:32:31,334 DEBUG [http-8080-2] [Slf4jImpl.java:47] - ==> Preparing: select tbl.item_id, tbl.item_name, tbl.create_time, tbl.update_time from sample_tbl tbl where tbl.item_id=?
2014-03-19 12:32:31,335 DEBUG [http-8080-2] [Slf4jImpl.java:47] - ==> Parameters: 79bc3c80-af0a-11e3-a8e4-b8e8560f9d02(String)
Adding SampleTbl id to cache : 79bc3c80-af0a-11e3-a8e4-b8e8560f9d02
2014-03-19 12:32:31,340 DEBUG [http-8080-2] [AbstractPlatformTransactionManager.java:752] - Initiating transaction commit
2014-03-19 12:32:31,342 DEBUG [http-8080-2] [DataSourceTransactionManager.java:264] - Committing JDBC transaction on Connection [jdbc:mysql://slave1:3307/, UserName=root@10.174.10.72, MySQL Connector Java]
2014-03-19 12:32:31,382 DEBUG [http-8080-2] [DataSourceTransactionManager.java:322] - Releasing JDBC Connection [jdbc:mysql://master:3306/, UserName=root@10.174.10.72, MySQL Connector Java] after transaction





My connection settings are :

<Context>
<WatchedResource>WEB-INF/web.xml</WatchedResource>
<Resource name="jdbc/sample"
auth="Container"
type="javax.sql.DataSource"
factory="org.apache.commons.dbcp.BasicDataSourceFactory"
username="root"
password="root"
driverClassName="com.mysql.jdbc.ReplicationDriver"
url="jdbc:mysql:replication://master:3306,slave1:3307,slave2:3308/sampledb?allowMasterDownConnections=true"
connectionCachingEnabled="true"
connectionCacheProperties="{MaxStatementsLimit=10}"
removeAbandoned="true"
removeAbandonedTimeout="600"
logAbandoned="true"
timeBetweenEvictionRunsMillis="1000"
minEvictableIdleTimeMillis="1000"
testOnBorrow="false"
testOnReturn="false"
validationQuery="select null"
testWhileIdle="true"
maxActive="10"
maxIdle="3"
maxWait="1000"
defaultAutoCommit="false"/>

</Context>