才工作一兩年用Oracle的時(shí)候，一些網(wǎng)絡(luò)問(wèn)題會(huì)造成應(yīng)用線程一直卡在 SocketInputStream.read() 上，或者卡了某個(gè)時(shí)間段后報(bào)出java.sql.SQLRecoverableException: IO 錯(cuò)誤: Socket read timed out。 當(dāng)時(shí)周?chē)鷽](méi)人知道這個(gè)問(wèn)題的原因，我在排查過(guò)程中發(fā)現(xiàn)，大家都百度的時(shí)候，誰(shuí)能Google反而往往更能找到問(wèn)題的真正原因。。。最終在http://www-01.ibm.com/support/docview.wss?uid=swg1PM91941 里找到了答案，設(shè)置超時(shí)參數(shù)oracle.jdbc.ReadTimeout，繼續(xù)搜索又碰到了極品好文 Understanding JDBC Internals & Timeout Configuration。
當(dāng)時(shí)因?yàn)橹灰蠸ocketTimeoutException發(fā)生，再調(diào)用Connection對(duì)象的一些方法就會(huì)報(bào)錯(cuò)，所以下意識(shí)以為類(lèi)似socketTimeout的參數(shù)超時(shí)會(huì)直接導(dǎo)致socket不可用。在我弄分庫(kù)分表中間之后，需要了解mysql通信協(xié)議，測(cè)試的時(shí)候發(fā)現(xiàn)，Socket read timed out后socket仍然可用，并且jdbc驅(qū)動(dòng)會(huì)發(fā)送數(shù)據(jù)庫(kù)協(xié)議層的揮手請(qǐng)求，正常關(guān)閉連接?。ㄆ鋵?shí)人家javadoc就是這么寫(xiě)的?。?/p>
Connection層面關(guān)于socket的兩個(gè)超時(shí)參數(shù)
- connect timeout
連接到server的超時(shí)時(shí)間，對(duì)應(yīng)java.net.Socket#connect(java.net.SocketAddress, int)的第二個(gè)參數(shù)
public void connect(SocketAddress endpoint, int timeout) throws IOException
- socket timeout
等待讀取數(shù)據(jù)的超時(shí)時(shí)間，傳遞給java.net.Socket#setSoTimeout方法
public synchronized void setSoTimeout(int timeout) throws SocketException
java.net.Socket#setSoTimeout的javadoc
Enable/disable SO_TIMEOUT with the specified timeout, in milliseconds. With this option set to a non-zero timeout, a read() call on the InputStream associated with this Socket will block for only this amount of time. If the timeout expires, a java.net.SocketTimeoutException is raised, though the Socket is still valid. The option must be enabled prior to entering the blocking operation to have effect. The timeout must be > 0. A timeout of zero is interpreted as an infinite timeout.
javadoc清楚的寫(xiě)著，超時(shí)以后java.net.SocketTimeoutException會(huì)被拋出，但是Socket還是可用的。
在mysql-jdbc驅(qū)動(dòng)里面，SocketTimeoutException被包裝成CommunicationsException向上拋出，連接關(guān)閉清理資源是會(huì)調(diào)用com.mysql.jdbc.MysqlIO#quit，這里會(huì)用當(dāng)前socket繼續(xù)向mysql發(fā)送QUIT包。如果客戶端連接的是分庫(kù)分表中間件的話，QUIT包確保了中間件那里不會(huì)有類(lèi)似java.io.IOException: 遠(yuǎn)程主機(jī)強(qiáng)迫關(guān)閉了一個(gè)現(xiàn)有的連接這樣的報(bào)錯(cuò)出現(xiàn)。
"main@1" prio=5 tid=0x1 nid=NA runnable
java.lang.Thread.State: RUNNABLE
at com.mysql.jdbc.MysqlIO.quit(MysqlIO.java:2261)
at com.mysql.jdbc.ConnectionImpl.realClose(ConnectionImpl.java:4232)
at com.mysql.jdbc.ConnectionImpl.cleanup(ConnectionImpl.java:1338)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2517)
- locked <0x3cf> (a com.mysql.jdbc.JDBC4Connection)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2449)
at com.mysql.jdbc.StatementImpl.executeQuery(StatementImpl.java:1381)
at com.tankilo.App.main(App.java:26)
java.net.Socket#setSoTimeout可以被多次調(diào)用
查看mysql jdbc驅(qū)動(dòng)源碼可以看出，驅(qū)動(dòng)自己執(zhí)行某些內(nèi)置SQL時(shí)，用戶的socketTimeout參數(shù)很大程度不合適，所以執(zhí)行前會(huì)把用戶設(shè)置的socketTimeout參數(shù)暫存，然后調(diào)用java.net.Socket#setSoTimeout臨時(shí)設(shè)置合理的超時(shí)參數(shù)，在執(zhí)行完內(nèi)置SQL后再通過(guò)java.net.Socket#setSoTimeout將用戶設(shè)置的socketTimeout還原。
用戶如何臨時(shí)調(diào)整socketTimeout參數(shù)
在mysql-jdbc驅(qū)動(dòng)里，上面兩個(gè)超時(shí)參數(shù)可以在獲取連接時(shí)，通過(guò)【jdbc url（例如jdbc:mysql://localhost:3306/db1?connectTimeout=60000&socketTimeout=60000）】或者【java.sql.DriverManager#getConnection里的Properties對(duì)象】去設(shè)置。但是如果用戶想在連接建立后，像驅(qū)動(dòng)源碼里一樣臨時(shí)為某個(gè)sql調(diào)整socketTimeout應(yīng)該怎么辦，侵入到驅(qū)動(dòng)實(shí)現(xiàn)里獲取socket對(duì)象？
不需要，JDBC驅(qū)動(dòng)接口類(lèi)已經(jīng)提供了對(duì)應(yīng)的API.
// java.sql.Connection#setNetworkTimeout
void setNetworkTimeout(Executor executor, int milliseconds) throws SQLException;
這個(gè)方法的javadoc也是非常清楚的，
Sets the maximum period a Connection or objects created from the Connection will wait for the database to reply to any one request. If any request remains unanswered, the waiting method will return with a SQLException, and the Connection or objects created from the Connection will be marked as closed. Any subsequent use of the objects, with the exception of the close, isClosed or Connection.isValid methods, will result in a SQLException.
Note: This method is intended to address a rare but serious condition where network partitions can cause threads issuing JDBC calls to hang uninterruptedly in socket reads, until the OS TCP-TIMEOUT (typically 10 minutes).This method can be invoked more than once, such as to set a limit for an area of JDBC code, and to reset to the default on exit from this area. Invocation of this method has no impact on already outstanding requests.
When the driver determines that the setNetworkTimeout timeout value has expired, the JDBC driver marks the connection closed and releases any resources held by the connection.
這個(gè)方法的問(wèn)題
java.sql.Connection#setNetworkTimeout需要兩個(gè)參數(shù)，一個(gè)java.util.concurrent.Executor對(duì)象，一個(gè)超時(shí)參數(shù)值。mysql-jdbc驅(qū)動(dòng)會(huì)在用java.util.concurrent.Executor運(yùn)行com.mysql.jdbc.ConnectionImpl.NetworkTimeoutSetter類(lèi)，最終會(huì)去調(diào)用java.net.Socket#setSoTimeout。
如果第一個(gè)參數(shù)傳入線程池的話，這個(gè)過(guò)程是個(gè)異步過(guò)程，而java.sql.Connection#setNetworkTimeout本身也沒(méi)返回像java.util.concurrent.Future這樣的操作結(jié)果占位符。如果你調(diào)用完java.sql.Connection#setNetworkTimeout立馬執(zhí)行SQL，新的超時(shí)參數(shù)可能不會(huì)在這條SQL上生效，因?yàn)槟愕腟QL和執(zhí)行NetworkTimeoutSetter的異步線程在并發(fā)執(zhí)行。
保險(xiǎn)起見(jiàn)應(yīng)該這樣設(shè)置? 在當(dāng)前線程直接執(zhí)行。
conn.setNetworkTimeout(new Executor() {
@Override public void execute(Runnable command) {
command.run();
}
}, 4000);
總結(jié)
本文只是一些比較零碎的個(gè)人發(fā)現(xiàn)，系統(tǒng)的介紹jdbc超時(shí)參數(shù)的文章，開(kāi)頭也提到過(guò)。只是在學(xué)習(xí)分庫(kù)分表中間的過(guò)程中，對(duì)這些參數(shù)有了自我實(shí)踐的認(rèn)識(shí)?；叵胍郧安閛racle jdbc驅(qū)動(dòng)的時(shí)候，大家都是百度，憑什么別人百度到，你百度不到？（別人能看Oracle付費(fèi)知識(shí)庫(kù)Orz...）?，F(xiàn)在用mysql，最大的好處是開(kāi)源網(wǎng)上信息很多，自己也可以看源碼，即使老同事口口相傳，你不問(wèn)，他不說(shuō)，你也可以自己去從代碼層級(jí)確定某些問(wèn)題。開(kāi)放，大家一起討論，其樂(lè)無(wú)窮。
