一篇文章彻底理解数据库的各种超时参数

一篇文章彻底理解数据库的各种超时参数

1 前言

在日常数据库的使用过程中，我们经常会遇到各种超时，特别是在网络不稳定和业务高并发的情况下。
理解这些超时的背后原理和工作机制，以及不同数据库下的超时参数和设置方式，无疑会对异常状况下的问题排查大有裨益；通过合理配置这些超时参数，也可以减少各种异常情况下应用宕机恢复的时间，从而提高 RTO 和 RPO，满足 SLA 的要求。

本片文章，我们就来一起学习下这些超时的相关知识。

2 数据库的超时参数有哪些？

数据库的超时参数有以下几种：

• 事务超时：transaction timeout;
• 查询超时 query timeout，有时也被称为语句超时 statement timeout;
• 套接字超时（socket timeout）：具体又包括登录超时 loginTimeout，连接超时 connectTimeout，和常规的套接字超时 socket timeout，其中连接超时有时也被称为网络超时 NetworkTimeout；

除了以上几个数据库的超时参数，还需要注意，应用程序和数据库所在的服务器也可以配置操作系统级别的套接字超时检测机制。

3 事务超时的含义是什么？

事务超时，即 transaction timeout, 可以用来限制某个事务中所有 statement 语句的处理时间之和的最大值，简单来说，事务超时时间 statement timeout = 语句超时时间 statement/query timeout * 事务中语句个数 + 其他耗时（如业务代码处理时间，gc 垃圾回收时间等）

事务超时一般在应用框架中进行配置, 如 spring 中，可以使用注解 @Transactional 指定。

4 查询超时的含义是什么？

查询超时，即 query timeout，有时也被称为语句超时 Statement timeout，可以用来限制某个 statement 语句（可以是增删改查）的最大执行时间，若该 sql语句在该超时时间内还没有返回执行结果，应用端的数据库驱动程序就会抛出超时异常，并发送取消执行的信号给远程的数据库管理系统，由数据库管理系统取消该语句的执行。

• JDBC 提供了标准 API 来指定语句超时： java.sql.Statement.setQueryTimeout(int timeout);
• 不过在实际应用中，大多数开发者都不会通过代码直接指定语句超时，而是使用框架提供的配置机制来指定语句超时；
• 比如 mybatis中，可以通过注解 @defaultStatementTimeout 指定默认的语句超时时间，并在具体的 SQL语句中通过注解 @timeout 覆盖全局的默认值；
• 语句超时的默认值是0，即没有超时时间,具体超时时间的配置，需要根据业务特征进行配置，并没有统一的推荐值；
• 在使用 batch 机制时，该超时时间是适用于某个单独的sql还是该批次所有sql之和，JDBC并没有统一要求，由具体的数据库驱动自行实现；
  

5 查询超时的工作机制是什么?

查询超时在不同数据库管理系统和不同驱动下，其工作机制略有不同，但其工作原理是相似的，即大都是通过一个独立的线程来跟踪语句的执行时间，在执行时间超过指定的超时时间时，应用端抛出超时的错误，并通过底层的数据库连接发送取消执行的信号给远程的数据库管理系统，由数据库管理系统取消该语句的执行。

比如 Oracle数据库中，其查询超时的工作机制大体如下：

• 创建待执行 statement：Creates a statement by calling Connection.createStatement()；

• 触发执行 statement：Calls Statement.executeQuery()；

• 通过 statement 底层的连接将 statement 远程传输给数据库管理系统：The statement transmits the Query to Oracle DBMS by using its own connection.

• 注册该 statement 到超时处理线程 OracleTimeoutPollingThread：The statement registers a statement to OracleTimeoutPollingThread (1 for each classloader) for timeout process.
  

• 执行时发生了超时：Timeout occurs.

• 超时处理线程调用方法取消语句的执行：OracleTimeoutPollingThread calls OracleStatement.cancel().
  

• 通过 statement 底层的连接，发送取消执行的信号给远程的数据库管理系统，以取消语句的执行：Sends a cancel message through the connection and cancels the query being executed.
  

再比如Mysql中，其查询超时的工作机制大体如下：

• 创建待执行 statement: Creates a statement by calling Connection.createStatement().
• 触发执行 statement：Calls Statement.executeQuery().
• 通过 statement 底层的连接远程传输 statement 给数据库管理系统：The statement transmits the Query to MySqlServer by using the internal connection.
• 为每个 statement 创建一个超时处理线程（在 5.1 版本中，更改为为每个连接创建一个超时处理线程）：The statement creates a new timeout-execution thread for timeout process；（For version 5.1.x, it changes to assign 1 thread for each connection.）
• 向超时处理线程注册超时处理逻辑：Registers the timeout execution to the thread.
• 执行时发生了超时：Timeout occurs.
• 超时处理线程创建到数据库管理系统的连接：The timeout-execution thread creates a connection that has the same configurations as the statement.
• 超时处理线程通过底层的连接，发送取消执行的信号给远程数据库管理系统以取消语句的执行：Transmits the cancel Query (KILL QUERY "connectionId“) by using the connection.

6 套接字超时的含义是什么？

数据库连接的套接字超时，即 socket timeout, 具体又包括登录超时 (loginTimeout)，网络超时/连接超时 (connectTimeout/NetworkTimeout)，和常规的套接字超时 socket timeout，各自的含义如下。

6.1 登录超时 loginTimeout

登录超时，即 loginTimeout，是数据库用户成功登录到数据库服务器的超时时间。

• JDBC 标准 API 中定义了登录超时的含义，如java.sql.DriverManager#setLoginTimeout，javax.sql.CommonDataSource#setLoginTimeout；
  

6.2 连接超时

连接超时，即 connectTimeout,有时也被称为 网络超时 NetworkTimeout,是驱动程序建立 JDBC 底层的 TCP 连接的超时时间。

• JDBC 标准 API 中定义了该超时时间的含义，如 java.sql.Connection#setNetworkTimeout：
  

6.3 常规的套接字超时

常规的套接字超时，即 socket timeout, 其含义如下：

• 套接字连接建立后，对 socket 中数据的读写操作都是阻塞的（涉及到 CPU 用户态和内核态的切换以及系统调用），套接字超时即是读写 socket 底层数据时的阻塞超时时间；
• 调用 Socket.write()对 socket 进行写操作时，应用通过系统调用将数据传给本地操作系统内核的缓冲区之后就可以立即返回（控制权立即回到应用上），通过网络对底层数据进行远程传输的操作是由操作系统进行的，所以一般应用代码的 socket 写操作很快就会返回，一般不会发生长时间的阻塞（当然如果系统内核缓冲区由于网络故障满了的话，Socket.write()也会进入waiting阻塞状态，此时操作系统会尝试重新发包，当达到重试的次数时就会产生系统异常错误）；
• 调用 Socket.read() 对 socket 进行读操作时，由于首先需要通过网络将 socket 底层的远程数据传输到本地，然后才能经由操作系统将底层数据返回给用户态的应用程序，所以一般应用代码的 socket 读操作会消耗一段时间，可能会因为尝长时间的阻塞而发生超时异常；
• 在网络连接发生异常或服务器异常崩溃宕机时，因为 TCP/IP 的工作机制, socket 无法检测到底层网络的异常，因此应用系统也就无法检测到跟 DBMS 之间的 TCP 连接是否处于断开状态，所以应用端如果没有配置套接字超时，应用就会无期限地等待 DMBS的返回结果(这种连接也被称为死亡连接或僵尸连接 “dead connection.”) ，为了避免这种僵尸连接，同样需要配置套接字超时；

6.4 登录超时，连接超时，常规套接字超时的区别与联系

登录超时，连接超时，常规的套接字超时，三者的区别与联系如下：

• 登录超时是高级别的数据库服务层面的超时，而连接超时和套接字超时是低级别的socket层面的超时；
• 由于用户登录数据库服务器时，底层包含了 和数据库服务器之间的 tcp 连接的建立，也包含了数据库服务器对用户的认证，所以一般需要配置登录超时 > 连接超时;
• 登录超时和连接超时 只影响客户端和数据库服务器之间的连接的初始建立，而套接字超时 会影响客户端和服务器之间的连接的整个生命周期，包括初始连接的建立，也包括连接建立完毕后所有SQL语句的执行（有些SQL可能需要耗费较长时间）,所以一般需要配置套接字超时 > 登录超时，套接字超时 > 连接超时；

-