最近使用C3P0時(shí)软驰，穩(wěn)定一段時(shí)間后，會(huì)出現(xiàn)從連接池獲取的connection不可用的錯(cuò)誤峭判，錯(cuò)誤棧如下
java.sql.SQLException: Could not retrieve transaction read-only status from server
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:965)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:898)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:887)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:861)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:878)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:874)
at com.mysql.jdbc.ConnectionImpl.isReadOnly(ConnectionImpl.java:3556)
at com.mysql.jdbc.ConnectionImpl.isReadOnly(ConnectionImpl.java:3524)
at com.mysql.jdbc.StatementImpl.executeBatchInternal(StatementImpl.java:977)
at com.mysql.jdbc.StatementImpl.executeBatch(StatementImpl.java:970)
at com.mchange.v2.c3p0.impl.NewProxyStatement.executeBatch(NewProxyStatement.java:743)
Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure
The last packet successfully received from the server was 6,503,493 milliseconds ago. The last packet sent successfully to the server was 1 milliseconds ago.
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
Caused by: java.io.EOFException: Can not read response from server. Expected to read 4 bytes, read 0 bytes before connection was unexpectedly lost.
at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:3014)
at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3472)
... 16 common frames omitted
棧頂?shù)男畔⑹荂ould not retrieve transaction read-only status from server载绿，實(shí)際上root cause是java.io.EOFException床蜘，數(shù)據(jù)庫(kù)連接已經(jīng)閑置了10分鐘。
進(jìn)一步讀代碼蔑水，發(fā)現(xiàn)getConnection()時(shí)邢锯，C3P0默認(rèn)是不檢查連接狀態(tài)的。除非設(shè)置了testConnectionOnCheckout搀别，每次獲取連接時(shí)丹擎，都檢查一下連接的狀態(tài)。經(jīng)如下修改歇父，上面的問(wèn)題就不會(huì)復(fù)現(xiàn)
ComboPooledDataSource ds = new ComboPooledDataSource();
ds.setTestConnectionOnCheckout(true);
關(guān)于testConnectionOnCheckout源碼的相關(guān)部分在C3P0PooledConnectionPool.refurbishResourceOnCheckout()方法中：
if (testConnectionOnCheckout) {
if (C3P0PooledConnectionPool.logger.isLoggable(MLevel.FINER)) {
this.finerLoggingTestPooledConnection(resc, "CHECKOUT");
} else {
this.testPooledConnection(resc);
}
}
那C3P0是如何檢查連接狀態(tài)的呢蒂培？是在DefaultConnectionTester.activeCheckConnectionNoQuery()中向數(shù)據(jù)庫(kù)發(fā)送一個(gè)getTables請(qǐng)求，部分代碼如下
ResultSet rs = null;
try {
rs = c.getMetaData().getTables( null, null,
"PROBABLYNOT",
new String[] {"TABLE"} );
return CONNECTION_IS_OKAY;
}
但如果每次獲取Connection都要testConnection顯然是低效的榜苫。
C3P0提供了另一種方式护戳，就是在connection check in時(shí)檢查連接是否有效，并且之后定期地檢查鏈接是否有效垂睬。只要通過(guò)設(shè)置testConnectionOnCheckin為true媳荒，設(shè)置idleConnectionTestPeriod為20秒即可。
