一篇文章彻底理解数据库的各种超时参数
一篇文章彻底理解数据库的各种超时参数
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 可能需要耗费较长时间）,所以一般需要配置套接字超时 > 登录超时，套接字超时 > 连接超时；
7 查询超时和套接字超时有何关系？
我们经常遇到开发同学抱怨，明明对某个 SQL 语句配置了查询超时，但看起来查询超时就是不生效，其实这种情况是因为底层的网络出了问题，而查询超时机制在网络异常的状况下是不生效的，其原因如下：
高层次的异常依赖于低层次的异常，只有在低层次的异常机制正常工作的前提下，高层次的异常机制才能正常工作，所以事务超时和查询超时的正常工作，都依赖于套接字超时的正常运转；
查询超时不能用来解决网络异常状况下的超时问题，查询超时仅仅只能用来限制某个语句的执行时间；
为应对网络连接的异常或数据库管理系统的异常宕机，需要使用数据库驱动的套接字超时;
一般不推荐通过套接字超时限制 sql 语句的执行时间，套接字超时一般需要配置为比查询超时大一些 (如果套接字超时小于查询超时，此时由于先触发套接字超时的处理，查询超时的处理逻辑也就不会被执行了，查询超时也就失去了意义)；
8 如何配置常见数据库的套接字超时？
如上文所说，为应对网络连接的异常以及数据库管理系统的崩溃宕机，需要使用数据库驱动的套接字超时；
套接字超时在底层又分为创建连接的超时和读写数据的超时两种，究其原因，是由 TCP 的工作机制决定的，包括连接建立的机制以及连接建立完毕后数据传输的机制；
套接字的连接超时和读写超时，在 JAVA 源码层面，分别对应方法 Socket.connect(SocketAddress endpoint, int timeout) 和方法 Socket.setSoTimeout(int timeout)：
绝大多数数据库驱动都支持对上述两种超时的配置，虽然不同数据库驱动具体的配置方式略有不同，但在驱动代码的最底层都是调用的方法 Socket.connect(SocketAddress endpoint, int timeout) 和方法 Socket.setSoTimeout(int timeout)；
下面总结下常见数据库中，套接字连接超时和读写超时的配置方式：
mysql 可以通过 url 参数指定套接字的连接超时和读写超时，超时单位是毫秒，如：jdbc:mysql://localhost:3306/ag_admin?useUnicode=true&characterEncoding=UTF8&connectTimeout=60000&socketTimeout=60000
pg 也可以通过 url 参数指定套接字的连接超时和读写超时，不过超时单位是秒:jdbc:postgresql://localhost/test?user=fred&password=secret&&connectTimeout=60&socketTimeout=60
oracle 的 thin jdbc driver 不支持通过 URL 参数指定套接字的连接超时和读写超时，而是需要通过系统参数 oracle.net.CONNECT_TIMEOUT 和 oracle.jdbc.ReadTimeout 来分别指定，这两个参数的单位都是毫秒，默认值都是 0，(读写超时参数，在 10.1.0.5 以下版本的驱动中是 oracle.net.READ_TIMEOUT，在 10.1.0.5 以上的版本中才是 oracle.jdbc.ReadTimeout)，比如可以通过 OracleDatasource.setConnectionProperties(java.util.Properties prop) 指定，使用 DBCP 时可以通过 BasicDatasource.setConnectionProperties(java.util.Properties prop)或 BasicDatasource.addConnectionProperties(java.util.Properties prop)指定；
9 操作系统级别的套接字超时检测机制
除了以上几个数据库的超时参数，还需要注意，应用程序和数据库所在的服务器也可以配置操作系统级别的套接字超时检测。
当应用系统没有指定数据库的连接超时和套接字超时时，应用系统大部分情况下都不能有效检测到网络故障。因此，当网络错误发生后，在应用系统成功重新建立连接前或成功读取到数据前，应用系统都会无限制地一直处于等待状态；
为避免上述状况，运维人员可以在服务器上配置套接字超时检测，从而在服务器的操作系统层面主动对网络连接进行校验；
比如在 linux 服务器上将 KeepAlive 的检测间隔配置为 30 分钟后，在遇到网络问题时，即使应用系统没有在 JDBC 数据库驱动中指定套接字超时（或指定为 0），因为网络问题造成的数据库连接问题的持续时间也不会超过 30 分钟；
linux 下，操作系统级别的套接字超时检测机制，主要跟以下几个内核参数相关：
/proc/sys/net/ipv4/tcp_keepalive_intvl: 默认 75 秒，The number of seconds between TCP keep-alive probes；
/proc/sys/net/ipv4/tcp_keepalive_probes: 默认 9 次，The maximum number of TCP keep-alive probes to send before giving up and killing the connection if no response is obtained from the other end；
/proc/sys/net/ipv4/tcp_keepalive_time: 默认 7200 秒即 2 小时，The number of seconds a connection needs to be idle before TCP begins sending out keep-alive probes. Keep-alives are sent only when the SO_KEEPALIVE socket option is enabled. An idle connection is terminated after approximately an additional 11 minutes (9 probes an interval of 75 seconds apart) when keep-alive is enabled；
linux 下，可以通过 sysctl 命令查看和更改上述内核参数；
10 相关源码与参考连接
版权声明: 本文为 InfoQ 作者【明哥的IT随笔】的原创文章。
原文链接:【http://xie.infoq.cn/article/baf4a34552414090c63555d8c】。
本文遵守【CC-BY 4.0】协议，转载请保留原文出处及本版权声明。
Keep Striving! 2018-04-25 加入
明哥，十四年 IT经验，六年大数据经验； 做过大数据集群的搭建运维，大数据应用系统的开发优化，也做过大数据平台的技术选型以及架构咨询； 目前聚焦于泛大数据生态，包括数据仓库/数据湖，云计算和人工智能。
评论