还搞不定 SQL 阻塞与超时?早晚得出事!

关于SQL阻塞和超时，多年前就在前司时就遇到过，多年后发现现司无论是开发者还是DBA对此并不是很了解，在数据库使用规范里对相关参数也缺乏对应的指引和建议，可能此问题在大多数SQL频率不是很高的开发场景中并不常出现，偶尔出现也可通过重启进行恢复，因此不没引起足够重视。

在实际生产环境，如果出现网络中断、数据库异常，或者在做数据库的切换演练时，对于一些业务量较大、高频SQL场景，这却是经常会出现比较头疼的一个问题，具体表现为连接池中某些连接长时间无响应，也无报错，处于长时间阻塞状态，无论是连接池的失败检测还是应用中的超时设置都无法处正常生效。当我们在测试环境有时则不一定能够很快出现，而且连接池在探活到网络异常时也会进行连接重建。

在讲具体阻塞原因前，先讲讲Java里关于使用数据库时常见的超时机制。

一、JDBC超时

JDBC的java.sql.Statement提供了setQueryTimeout接口，此接口可指定SQL执行的等待时间，当超过指定时间后中止对应SQL操作，同时抛出对应的SQLException。

JDBC超时设置方法

以MySQL为例，其实现机制为启动一个异步线程，如果SQL很快执行完，则取消此超时任务，否则到了超时时间，此超时任务则会建立新的数据库连接，然后发送KILL QUERY，中止MySQL服务端对应此SQL的执行。

com.mysql.cj.jdbc.StatementImpl#executeInternal

MySQL驱动对JDBC超时实现代码

1.启动一个SQL超时检测的线程任务T；

2.执行SQL，同步阻塞等待数据库返回执行结果；

3.取消第1步中SQL超时检测的线程任务T。

查看startQueryTimer方法源码，com.mysql.cj.AbstractQuery#startQueryTimer：

MySQL驱动startQueryTimer

可以看到启动了一个线程CancelQueryTaskImpl，其核心代码为com.mysql.cj.CancelQueryTaskImpl#run

MySQL驱动CancelQueryTask

• MySQL驱动会先判断连接参数queryTimeoutKillsConnection的值，当此参数为true时，当超时后则会关闭此连接。

• queryTimeoutKillsConnection参数如果不设置，默认为false，则会走到另一个分支，创建一个新的连接，然后发送然后发送“KILL QUERY 原连接ID，MySQL服务端收到此SQL后会中断对应此SQL的执行（https://dev.mysql.com/doc/refman/8.4/en/kill.html，然后给返回MySQL驱动SQLState: 70100，应用端则会收到MySQLQueryInterruptedException。

看起来是一个完整的SQL超时机制，但这个方法是否可以保证SQL在任何情况下超过指定时间后就报错呢？其实并不能。

这种JDBC的超时机制在网络、数据库等正常时生效，但在出现网络故障、数据库端异常时，则会出现长时间阻塞的问题，进而影响数据库连接的可用性。

二、事务超时

除了JDBC超时，还有一个超时比较常见，就是事务超时。JDBC本身并不支持事务超时的设置，而通常是在通过框架（Spring, EJB）或应用级实现，以Spring为例，可以通过 @Transactional(timeout = 5) ，设置事务超时时间为5秒。

Spring提供的事务管理器它会记录每个事务的开始时间和消耗时间，当在每条SQL执行前会对消耗时间做校验，当超出timeout值时将抛出异常。Spring中，数据库连接被保存在ThreadLocal里，事务的开始时间和消耗时间会被同时保存下来，当使用此连接创建statement时，执行SQL前就会校验事务的消耗时间。在具体实现时Spring也是通过JDBC的sestQuerytimeout实现每条SQL超时保证。具体代码可见

org.springframework.jdbc.datasource.DataSourceUtils#applyTimeout

Spring事务超时关键代码

同样，Spring这种通过JDBC的超时机制实现的事务超时也无法应对在网络和数据库异常场景下的SQL长时间阻塞问题。

三、SQL阻塞的根本原因

当发生阻塞时通过jstack，可以看到MySQL驱动程序阻塞在socket的read接口：

SQL阻塞方法栈

如果没有设置socket的超时的话，应用在数据库返回结果前会无期限地等下去，这时使用的数据库连接已经变成了dead connection。

JDBC的设计是基于阻塞IO的，MySQL驱动与MySQL建立socket连接后，当应用通过JDBC接口执行SQL时，是通过socket的write接口将SQL发送给MySQL，然后调用read接口等待MySQL返回SQL执行返回结果集，这个时候如果发生网络中断、或者MySQL异常没有正常返回，就会出现长时间阻塞。

SQL的socket通讯

需要注意的是，这种阻塞通常只发生在上图（3）read接口，如果是在（1）或者（2）则会很快感知到socket异常返回给应用，通常数据库连接池会触发探活机制，然后与数据库重新建立的可用的连接，这也就是为什么这种阻塞往往发生在一些高频SQL场景，因为此问题只会在成功发送了SQL（write成功），然后在read时发生网络或数据库异常，这种时机概率并不高。

那么为什么socket的write不会阻塞，而read会呢？

1.这是因为通常来说socket的write被调用时，数据被写入到操作系统内核的缓冲区，此时如果发生网络故障或者数据库不可用，write 会立即检测到这个问题，因为它会尝试将数据发送出去并立即检测到传输问题。比如发送的数据无法通过底层网络协议传输，TCP 可能会立即返回一个错误（如 EPIPE 或 ECONNRESET），这时write 操作会抛出一个异常，例如 IOException 或 SocketException，并立即返回。

注：如果系统内核缓冲区由于某种网络错误而满了的话，Socket.write()也会进入waiting状态，这种情况下，操作系统会尝试重新发包，当达到重试的时间限制时，将产生系统错误。

socket的read 操作本质上是在等待对方发送数据到达，当调用 read 时，应用程序会阻塞在此操作上，当网络异常、对方主机无响应时，如果没有收到断开连接的明确信号（如 TCP 的 FIN 或 RST 包），read 操作通常会持续阻塞，直到设置的超时时间到达。

这种阻塞行为是因为 TCP 协议设计为在网络问题恢复时自动重传数据，并保持连接的稳定性。因此在网络故障期间，read 操作可能会长时间等待，如果不设置socket timeout或connect timeout，应用多数情况下是无法发现网络错误的，然后会无限制地等下去，这也就是产生SQL阻塞的底层原因。

四、SQL阻塞的解决

知道了原因后，解决方式其实也很简单，就是一定要设置数据库连接的socket超时参数。17年有篇文章对JDBC超时的原理以及常见数据库socket参数设置有过详细介绍，https://www.cubrid.org/blog/3826470，需要注意的除了SQL的读写socket超时，数据库connect时的socket超时也需要配置，避免在新建数据库连接时也发生长时间阻塞。

• MySQL Driver connectTimeout 0 ms Specify the option in the DriverURL.

Format:

jdbc:mysql://[host:port],[host:port].../[database]

[?propertyName1][=propertyValue1][&propertyName2][=propertyValue2]...

Example:

jdbc:mysql: //xxx.xx.xxx.xxx:3306/database?connectTimeout=60000&socketTimeout=60000

• MS-SQL Driver

jTDS Driver loginTimeout 0 sec Specify the option in the DriverURL. Format:

jdbc:jtds:<server_type>://[:][/][;=[;...]]

Example:

jdbc:jtds:sqlserver: //server:port/database;loginTimeout=60;socketTimeout=60

• Oracle Thin Driver oracle.net.CONNECT_TIMEOUT 0 ms Not possible with the driverURL. Must be delivered to the properties object via OracleDatasource.setConnectionProperties() API. When DBCP is used, use the following APIs:

? BasicDatasource.setConnectionProperties()

? BasicDatasource.addConnectionProperties()

需要注意的几点：

1.不要用socket timeout来限制statement的执行时长（JDBC的超时应该通过setQueryTimeout设置），socket timeout的值必须要高于statement timeout，否则，socket timeout将会先生效，这样JDBC的超时就失效了。

2.socket的超时的设置只会在产生数据读写时生效，而不会对数据库连接池中空闲连接产生影响。**

3.socket的超时应该设置为多少？socket timeout必须高于statement timeout，具体设置值还需要看应用里正常的SQL最大执行时间，之前在项目中这个值我们设置的是5分钟。

好久没写技术文章了，也借这篇提醒自己将日常一些研究思考整理记下来，利己也利他人。

作者丨蚊子squirrel

来源丨网址：https://www.jianshu.com/p/af8ac71f3a80

dbaplus社群欢迎广大技术人员投稿，投稿邮箱：editor@dbaplus.cn