DBCP连接池TestOnBorrow的用法

浅浅的花香味﹌ 2022-04-06 11:37 335阅读 0赞

生产环境连接池TestOnBorrow设置为false，导致有时获取的连接不可用。分析如下：  
  
\[list\]  
\[\*\]TestOnBorrow=false时，由于不检测池里连接的可用性，于是假如连接池中的连接被数据库关闭了，应用通过连接池getConnection时，都可能获取到这些不可用的连接，且这些连接如果不被其他线程回收的话，它们不会被连接池被废除，也不会重新被创建，占用了连接池的名额。  
\[/list\]

The last packet successfully received from the server was 19,956 milliseconds ago.  The last packet sent successfully to the server was 32 milliseconds ago.
        at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
        at com.mysql.jdbc.StatementImpl.executeQuery(StatementImpl.java:1571)
    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:3143)
        at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3597)
        ... 8 more

\[list\]  
\[\*\]当TestOnBorrow=true时，有两种情况：  
\[/list\]  
① 集群某实例宕掉时，如果连接刚好不处于通信阶段，tcp连接正处于CLOSE\_WAIT状态或已关闭，当应用通过连接池getConnection时，在borrow时会检测连接，由于连接已关闭，于是报了如下报错，并重新建立新连接，此时的新连接到集群的其他实例上了。后面能正常通信。

com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure
    
    The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.
        at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    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:3143)
        at com.mysql.jdbc.MysqlIO.readPacket(MysqlIO.java:597)
        ... 21 more

② 集群某实例宕掉时，如果连接刚好处于通信阶段，由于客户端无法立即感知服务端已断连接，它可能会报如下错误，等待服务端的响应超时报错。当应用通过连接池getConnection时，在borrow时会检测连接，由于连接已关闭，于是报了如下报错，并重新建立新连接，此时的新连接到集群的其他实例上了。后面能正常通信。

com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure
    
    The last packet successfully received from the server was 10,538 milliseconds ago.  The last packet sent successfully to the server was 10,306 milliseconds ago.
        at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure