文章详情

短信预约-IT技能 免费直播动态提醒

请输入下面的图形验证码

提交验证

短信预约提醒成功

深入源码和内核,一篇文章彻底理解数据库的各种超时参数

2024-11-30 01:37

关注

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

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

总体而言,数据库的超时参数有以下几种:

除了以上几个常见的数据库超时参数,还需要注意,客户端的JDBC应用程序和服务端的数据库管理系统,在服务器上都有操作系统级别的基于 tcp keep-alive 的超时检测和保活机制;如果操作系统支持,客户端的JDBC应用程序和服务端的数据库管理系统,甚至也可能配置套接字级别的基于 tcp keep-alive的超时检测和保活机制。

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

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

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

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

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

图片

5 连接超时

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

图片

6 登录超时 loginTimeout 的含义是什么?

登录超时,即 loginTimeout,是数据库用户成功登录到数据库服务器的超时时间,由于用户登录数据库服务器时,底层包含了和数据库服务器之间的 tcp 连接的建立,也包含了数据库服务器对用户的认证,所以一般而言,需要配置登录超时 > 连接超时;

图片

图片

7 TCP 套接字超时

由于应用程序通过 TCP 协议读写网络数据包,都是通过 TCP/IP 协议栈的 socket api 进行的,所以常规的套接字超时 socket timeout 同样适用于 JDBC 应用程序。虽然 TCP是面向连接的协议,但这里的连接是虚拟的,是动态的,也是不对等的,所以应用程序需要通过 socket timeout 来检测和感知网络层面 TCP 连接的异常,从而避免僵死连接造成的无限等待;(对这块感兴趣的朋友,可以关注笔者对 tcp/ip 协议栈,对 tcpdump/wireshark/packetdrill 工具的相关分享文章);

8 登录超时,连接超时,TCP 套接字超时的区别与联系

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

- The loginTimeout specifies how long the whole process of logging into the database is allowed to take. It governs the operation of connecting and authenticating to the dbms server, this involves establishing a TCP connection followed by one or more exchanges of packets for the handshake and authentication to the dbms server;
- The connectTimeout specifies how long to wait for a TCP network connection to get established, it governs the time needed to establish a TCP socket connection, and as establishing a TCP connection is part of establishing a database connection and doesn't guarantee a login, so loginTimeout >= connectTimeout;
- A connection timeout occurs only upon starting the TCP connection. This usually happens if the remote machine does not answer. If you get an ConnectException, possible reasons are: the server has been shut down, you used the wrong IP/DNS name, wrong port or the network connection to the server is down.
- A connection timeout is the maximum amount of time that the program is willing to wait to setup a connection to another process. You aren't getting or posting any application data at this point, just establishing the connection, itself.
- The socketTimeout specifies how long the client will wait for a response to a command from the server before throwing an error, it governs the time a socket can be blocked waiting to read from a socket, this involves all reads from the server, not just during connect, but also during subsequent interaction with the server (eg executing queries),so you may want to set it higher (eg for other operations that take a long time to get a response back) than you are willing to wait for the login to complete;
- A socket timeout is dedicated to monitor the continuous incoming data flow. If the data flow is interrupted for the specified time the connection is considered as stalled/broken. Of course this only works with connections where data is received all the time and there are no delays longer than the configured socket timeout.
- A socket timeout is the timeout when waiting for individual packets. It's a common misconception that a socket timeout is the timeout to receive the full response. So if you have a socket timeout of 1 second, and a response comprised of 3 IP packets, where each response packet takes 0.9 seconds to arrive, for a total response time of 2.7 seconds, then there will be no timeout.
- By setting socket timeout to 1000 (ms) this would require that every second new data is received (assuming that you read the data block wise and the block is large enough).If only the incoming stream stalls for more than a second you are running into a socket timeout.This is especially important when HTTP servers process a complex request that requires some time on server side before the HTTP response data is available. If you configure socket timeout to 10000 (10 seconds) but the HTTP server requires 15 seconds after receiving the HTTP request, then you will never get the response as after 10 seconds you will get an SocketTimeoutException (no data is transmitted between reception of the HTTP request until the HTTP response is ready).
- A socketTimeout can be used as both a brute force global query timeout and a method of detecting network problems;
- the loginTimeout and connectTimeout are related to establishing a connection, while socketTimeout is relevant for the whole database session;
- connectTimeout and socketTimeout are timeouts on low-level socket operations, while loginTimeout is on a high level - the database level;
- Generally, the application hangs from network issues when the application is calling Socket.read(). However, depending on the network composition or the error type, it can rarely be in waiting status while running Socket.write(). When the application calls Socket.write(), the data is recorded to the OS kernel buffer and then the right to control is returned to the application immediately. Thus, as long as a valid value is recorded to the kernel buffer, Socket.write() is always successful. However, if the OS kernel buffer is full due to a special network error, even Socket.write() can be put into waiting status;

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

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

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

图片

图片

图片

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

图片

10 查询超时和 TCP套接字超时有何关系?

我们经常遇到开发同学抱怨,明明对某个SQL语句配置了查询超时,但看起来查询超时就是不生效,其实这种情况是因为底层的网络出了问题,而查询超时机制在网络异常的状况下是不生效的,其原因如下:

- The higher level timeout is dependent on the lower level timeout. The higher level timeout will operate normally only if the lower level timeout operates normally as well. If the JDBC driver socket timeout does not work properly, then higher level timeouts such as statement timeout and transaction timeout will not work properly either.
- The statement timeout does not handle the timeouts at the time of network failure, it does only one thing: restricts the operation time of 1 statement,and handling timeout to prevent network failure must be done by JDBC Driver;
- Socket timeout value for JDBC driver is necessary when the DBMS is terminated abruptly or an network error has occured (equipment malfunction, etc.). 
- Because of the structure of TCP/IP, there are no means for the socket to detect network errors. Therefore, the application cannot detect any disconnection with the DBMS. If the socket timeout is not configured, then the application may wait for the results from the DBMS indefinitely. (This connection is also called a "dead connection."),to prevent dead connections, a timeout must be configured for the socket.
- Socket timeout can be configured via JDBC driver. By setting up the socket timeout, you can prevent the infinite waiting situation when there is a network error and shorten the failure time.
- It is not recommended to use the socket timeout value to limit the statement execution time. So the socket timeout value must be higher than the statement timeout value.
- If the socket timeout value is smaller than the statement timeout value, as the socket timeout will be executed first, and the statement timeout value becomes meaningless and will not be executed.

11 如何配置常见数据库的 TCP 套接字超时?

图片

图片

下面总结下常见数据库中,套接字连接超时和读写超时的配置方式:

图片

图片

# 配置参数
finalstatic String url= "jdbc:oracle:thin:@myhost:1521/myorcldbservicename";
finalstatic String user = "hr";
finalstatic String password = "hr";
finalstatic String CONNECT_TIMEOUT = "20000";
finalstatic String READ_TIMEOUT = "50000";
# 使用 DataSource 获取连接
Properties connectionProperties = new Properties(); 
connectionProperties.put(“oracle.net.CONNECT_TIMEOUT”, CONNECT_TIMEOUT);
connectionProperties.put(“oracle.jdbc.ReadTimeout”, READ_TIMEOUT);
OracleDataSource ods = new OracleDataSource();
ods.setURL(url); 
ods.setUser(user); 
ods.setPassword(password);    
ods.setConnectionProperties(connectionProperties);
# 使用 DriverManager 获取连接
Class oracleDriverClass = Class.forName("oracle.jdbc.driver.OracleDriver");
Properties connectionProperties = new Properties(); 
connectionProperties.put(“oracle.net.CONNECT_TIMEOUT”, CONNECT_TIMEOUT);
connectionProperties.put(“oracle.jdbc.ReadTimeout”, READ_TIMEOUT);  
//也可以通过环境变量/系统参数设置,注意需要在 connection 连接之前设置
//System.setProperty("oracle.net.CONNECT_TIMEOUT", connectTimeout);
//System.setProperty("oracle.jdbc.ReadTimeout", readTimeout); 
connectionProperties.put(“user”, user);
connectionProperties.put(“password”, password);  
Connection cnotallow=DriverManager.getConnection(url, props);

12 操作系统或TCP套接字级别的 TCP 超时检测机制

除了以上几个常见的数据库超时参数,还需要注意,客户端的JDBC应用程序和服务端的数据库管理系统,在服务器上都有操作系统级别的基于 tcp keep-alive 的超时检测和保活机制;如果操作系统支持,客户端的JDBC应用程序和服务端的数据库管理系统,甚至也可能配置 TCP 套接字级别的基于 tcp keep-alive的超时检测和保活机制。

/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;

import java.net.Socket;
import jdk.net.ExtendedSocketOptions;
Socket socket = new Socket();
socket.setOption(ExtendedSocketOptions.TCP_KEEPIDLE, 10);
socket.setOption(ExtendedSocketOptions.TCP_KEEPCOUNT, 2);
socket.setOption(ExtendedSocketOptions.TCP_KEEPINTERVAL, 3);
# 查询内核参数
- sysctl -a  //显示当前所有可用的内核参数
- sysctl net.ipv4.tcp_keepalive_time //查询某个内核参数
- cat /proc/sys/net/ipv4/tcp_keepalive_time //查询某个内核参数
#修改内核参数
- sysctl net.ipv4.tcp_keepalive_time=3600//修改某个内核参数
- vim /etc/sysctl.conf//在配置文件中修改内核参数
- sysctl  -p  //从配置文件 sysctl.conf 中重新加载内核参数
- If the socket timeout or the connect timeout is not configured, most of the time, applications cannot detect network errors. So, until the applications are connected or are able to read data, they will wait indefinitely. 
- To prevent this, we can configure a socket timeout time at the OS level, so the Linux servers can check the network connection at the OS level.
- If you set the KeepAlive checking cycle for the Linux servers to 30 minutes, then even if someone set the JDBC driver‘s socket timeout to 0, which means no timeout, the DBMS network connection problems caused by network issues do not surpass 30 minutes/The JDBC connection hang recovers 30 minutes after the network connection failure, that is to say, the JDBC driver's socket timeout is affected by the OS's socket timeout configuration. 
- Generally, the application hangs from network issues when the application is calling Socket.read(). However, depending on the network composition or the error type, it can rarely be in waiting status while running Socket.write(). When the application calls Socket.write(), the data is recorded to the OS kernel buffer and then the right to control is returned to the application immediately. Thus, as long as a valid value is recorded to the kernel buffer, Socket.write() is always successful. However, if the OS kernel buffer is full due to a special network error, even Socket.write() can be put into waiting status. In thiscase, the OS tries to resend the packet for a certain amount of time, and generates an error when it reaches the limit.

13 相关源码与参考连接

# JDBC API 相关类与方法
java.sql.DriverManager#setLoginTimeout
javax.sql.CommonDataSource#setLoginTimeout
java.sql.Connection#getNetworkTimeout
java.sql.Connection#setNetworkTimeout
java.sql.Statement#setQueryTimeout
# oracle JDBC driver 相关类与方法
oracle.jdbc.OracleDriver
oracle.jdbc.pool.OracleDataSource#setLoginTimeout
oracle.jdbc.OracleConnection
oracle.jdbc.OracleConnection#CONNECTION_PROPERTY_THIN_READ_TIMEOUT
oracle.jdbc.OracleConnection#CONNECTION_PROPERTY_THIN_NET_CONNECT_TIMEOUT。
oracle.jdbc.OracleConnectionWrapper#setNetworkTimeout
oracle.jdbc.driver.PhysicalConnection#setNetworkTimeout
oracle.jdbc.driver.OracleStatement#setQueryTimeout
oracle.jdbc.driver.OracleStatement#doExecuteWithTimeout
oracle.jdbc.driver.OraclePreparedStatement#executeForRowsWithTimeout
oracle.jdbc.driver.OracleTimeoutPollingThread
# mysql JDBC driver 相关类与方法
com.mysql.cj.jdbc.Driver
com.mysql.cj.jdbc.MysqlDataSource#setLoginTimeout
com.mysql.cj.jdbc.ConnectionImpl#setNetworkTimeout
com.mysql.cj.jdbc.ConnectionWrapper#setNetworkTimeout
com.mysql.cj.jdbc.StatementImpl#setQueryTimeout
com.mysql.cj.jdbc.StatementWrapper#setQueryTimeout
#参考链接
- https://www.cubrid.org/blog/3826470
- https://prashantatridba.wordpress.com/tag/tcp_keepidle/
- https://bugs.openjdk.org/browse/JDK-8194298


来源:明哥的IT随笔内容投诉

免责声明:

① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的,并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据,供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。

② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

软考中级精品资料免费领

  • 历年真题答案解析
  • 备考技巧名师总结
  • 高频考点精准押题
  • 2024年上半年信息系统项目管理师第二批次真题及答案解析(完整版)

    难度     813人已做
    查看
  • 【考后总结】2024年5月26日信息系统项目管理师第2批次考情分析

    难度     354人已做
    查看
  • 【考后总结】2024年5月25日信息系统项目管理师第1批次考情分析

    难度     318人已做
    查看
  • 2024年上半年软考高项第一、二批次真题考点汇总(完整版)

    难度     435人已做
    查看
  • 2024年上半年系统架构设计师考试综合知识真题

    难度     224人已做
    查看

相关文章

发现更多好内容

猜你喜欢

AI推送时光机
位置:首页-资讯-后端开发
咦!没有更多了?去看看其它编程学习网 内容吧
首页课程
资料下载
问答资讯