数据库相关异常分析

摘要：起因最近一段时间，生产系统持续碰到一些数据库异常，导致执行失败。综上，若发生异常，为数据库连接失效，但是失效的原因可能会有多种，大致都与各种参数相关。当时数据量大概多条，然后在批量插入时抛出该异常。

最近一段时间，生产系统持续碰到一些数据库异常，导致 sql 执行失败。

Java 1.7 + Mysql 5.6 + spring + ibatis

将各种失败的异常记录了一下，碰到最多下面几种异常。

java.net.SocketTimeoutException: Read timed out

java.sql.BatchUpdateException: No operations allowed after statement closed。

com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

java.io.EOFException: Can not read response from server. Expected to read 8 bytes, read 7 bytes before connection was unexpectedly lost.

java.net.SocketException: Software caused connection abort: recv failed

针对上面第一种情况，很容易从字面意义就得出是读取超时。然而查询资料 JDBC 存在多种 timeout,仔细研究了一下，梳理一下。

JBDC 可以设置超时时间分别是 Transaction Timeout，Statement Timeout，Socket TimeOut，ConnectionTimeout。上述超时时间层次从上至下。

以下我们从上之下分别了解这几种种超时时间。

Transaction Timeout ：事务超时时间，由多个 Statement 组成。事务的超时时间=N*Statement.timeout+其他代码执行时间。所以我们不应该在一个事务中执行一些 RPC 或 HTTP 等这些长耗时的调用。如果时间卡在这些调用上，会导致事务超时发生回滚。

Statement Timeout：一次语句的执行的时间，可以用来限制一个查询语句的执行时间。但是如果出现网络故障，这个超时间将不起作用。最终需要 Socket TimeOut 解决。

Socket TimeOut ：目前 JDBC 类型存在四种，而我们通常使用的是数据库协议驱动（Database-Protocol driver (Pure Java driver) or thin driver）。这种驱动采用 Socket 用来与数据库通信。若没有设置，一但发生网络故障，SCOKET 读取就会直接阻塞。而设置以后，时间超时后将会抛出 java.net.SocketTimeoutException: Read timed out，防止长时间阻塞，系统不可用。

ConnectionTimeout ：这个超时参数也是与 Socket 建立连接有关。若没有设置，一旦如果数据库相关地址参数错误错误，将会长时间阻塞在建立数据库连接上。

使用网上一张图可以清晰的解析前三者关系。

实际上还存在操作系统层面上 Socket 超时。各个操作系统可以设置相应 Socket 超时时间，然后若 JDBC 没有设置，到了操作系统的超时时间也将会断开。但是我们不能依赖该超时间，因为该时间完全不可控，我们应该显式设置。

综上，针对相关 JDBC 参数我们至少需要设置 ConnectionTimeout 以及 Socket TimeOut.针对 sql 语句，可以设置 Statement Timeout。若存在事务，还可以设置相应 Transaction Timeout。

这个 CommunicationsException 异常会因为其他底层异常导致如以下这两种异常。

java.io.EOFException: Can not read response from server. Expected to read 8 bytes, read 7 bytes before connection was unexpectedly lost.

java.net.SocketException: Software caused connection abort: recv failed

刚开始碰到该异常，根据 CommunicationsException 查询一下了，大致都是说 Mysql server 端会检测空闲连接，超时后主动断开连接，导致客户端的连接失效。

那么什么是 mysql 的空闲连接那？简单来说，mysql 连接进程 Command 为 sleep 状态。我们可以使用 show processlist ; 查看正在运行的进程。空闲的进程示例如图：

jdbc 连接会根据 mysql wait_timeout 检测空闲连接。若在 wait_timeout 时间内，连接还是空闲状态，mysql server 将会断开这个链接。针对这种情况，采用编码模拟。
采用如下代码：

        try {
            Connection connection = dataSource.getConnection();
            TimeUnit.SECONDS.sleep(11L);
            run.query(connection,"select "X"", h);
            //Thread.sleep(60000);
        } catch (Exception e) {
            log.error("查询异常", e);
        }

然后设置 mysql wait_timeout=10 。
以下模拟代码获取连接后，休眠11s，这个过程中，mysql 主动断开连接,等真正执行时，程序抛出异常。

以下为报错的情况：

但是底层异常却为 java.net.SocketException: Software caused connection abort: recv failed，而不是 java.io.EOFException。

这个报错却是很疑惑。然后仔细查看 EOFException 后面描述 Expected to read 8 bytes, read 7 bytes before connection was unexpectedly lost，可以看出这个连接其实有一段时间其实还是可用，有读取数据，但是在读取数据过程中，未读到符合数量的相应数据，导致报错。而上面代码模拟的却是连接使用时连接已生效的情况。

执行 show variables like "%timeout%"; 查看 mysql 其他超时时间，

从上图可以注意到 net_read_timeout 与 net_write_timeout 这两个参数。

查看 mysql 官方文档

net_read_timeout 默认30s
The number of seconds to wait for more data from a connection before aborting the read. When the server is reading from the client, net_read_timeout is the timeout value controlling when to abort. When the server is writing to the client,
net_write_timeout 默认60s
The number of seconds to wait for a block to be written to a connection before aborting the write

net_write_timeout 控制 mysql 服务端向客户端写数据超时时间。针对这种情况，在 MysqlIO read 处打上短点，

程序启动时，先放开断点，查看 mysql processlist，看到 mysql 进程 state send to client 时，这个时候使断点生效。这个时候，等待60s 以后，成功复现出如下错误。

net_read_timeout 该超时不知道如何模拟:(。

综上，若发生 com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure 异常，为数据库连接失效，但是失效的原因可能会有多种，大致都与 mysql 各种 timeout 参数相关。

这个错误是发生在数据批量导入时。当时数据量大概 20 多W条，然后在批量插入时抛出该异常。以下为批量插入代码。

getSqlMapClientTemplate().execute(new SqlMapClientCallback