Understanding JDBC Internals & Timeout Configuration
An application with a proper JDBC timeout can cut down the failure time. In this article we would like to talk about different kinds of timeout values and recommended timeout application methods when you import values from DBMS.
Web Application Server became unresponsive after a DDos attack one day
(This is a close reconstitution of an actual event.)
The entire service did not work normally after a DDos attack. The network was disconnected because L4 was not working, which caused WAS to be inoperable as well. Shortly afterwards, the security team blocked all DDos attacks, and restored the network back to normal. Yet, WAS was still not working.
Through the ThreadDump of WAS, the service team was able to confirm that WAS had stopped during API call from JDBC. After 20 minutes, WAS was still in WAITING status and the service was still not working. About 30 minutes had passed when an exception suddenly occurred, and the service was restored.
Why was WAS in WAITING status for 30 minutes when QueryTimeout value was set to 3 seconds, and why did WAS start working again after 30 minutes?
You can find the answer if you understand how the JDBC Timeout works.
Why Do We Need to Know about the JDBC Driver?
When there is a performance issue or an error, WAS and DBMS are the two important tiers we pay attention to. In NHN, WAS and DBMS are generally handled by different departments, so each department tries to figure out this situation by focusing on their own area of expertise. When this happens, you get a blind spot between WAS and DBMS, that does not receive much attention. For Java applications, the blind spot would be between DBCP and JDBC. In this article we will focus on JDBC.
What is a JDBC Driver?
JDBC is a standard API that you use to access the DBMS in Java applications. There are 4 types of JDBC drivers(Wikipedia) defined by Sun. NHN mainly uses the type 4. JDBC type 4 driver is written entirely in Java (pure Java) and communicates with a DBMS using sockets in Java applications.
Figure 1: JDBC Type 4.
Type 4 drivers process byte stream via sockets, and have the same basic operations as a network library likeHttpClient. This uses up a lot of CPU resources and loses response timeout, while sharing the same error points with other network libraries. If you have used HttpClient before, then you must have encountered errors from not setting the timeout value. Type 4 driver may have the same error (a hang occurs) if the socket timeout value is not set properly.
Let's learn about how to configure the socket timeout value for JDBC driver, and what needs to be considered.
Timeout Class at WAS - DBMS Communication
Figure 2: Timeout Class.
Figure 2 above shows a simplified version of the timeout class when WAS and DBMS are communicating.
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.
We have received a lot of comments that said:
Even after the statement timeout was configured, the application still did not recover from the error because the statement timeout did not work at the time of network failure.
The statement timeout does not handle the timeouts at the time of network failure. Statement timeout does only one thing: restricts the operation time of 1 statement. Handling timeout to prevent network failure must be done by JDBC Driver.
The JDBC driver's socket timeout is affected by the OS's socket timeout configuration. This would explain why JDBC connection hang recovers 30 minutes after the network connection failure, even when the JDBC driver's socket timeout is not configured.
DBCP Connection Pool is located on the left side of Figure 2. You can see that the timeout classes and DBCP are separated. DBCP is in charge of creating and managing connections, and is not involved in processing timeouts. When a connection is created within DBCP or a validation query is sent to check the validity of the connection, the socket timeout does affect these processes but does not affect the application directly.
However, when getConnection() is called to DBCP from the application logic, then you can specify the timeout until the application acquires the connection. However, this has nothing to do with the JDBC's connect timeout.
Figure 3: Timeout for Each Levels.
What is Transaction Timeout?
Transaction timeout is a timeout valid in frameworks (Spring, EJB container) or at the application level.
Transaction timeout can be an unfamiliar concept. Simply put, transaction timeout is "Statement Timeout * N (number of statements being processed) + @ (garbage collection, etc.)." Transaction timeout is used to limit the total statement processing time to the maximum amount allowed.
For example, if it takes 0.1 second to process 1 statement, processing a few statements would not be a problem, but processing 100,000 statements would take 10,000 seconds (approx. 7 hours). Statement timeout can be used here.
EJB CMT (Container Managed Transaction) would be a typical example of actual implementations. EJB CMT varies in its implementation methods and operating process depending on developers. NHN does not use EJB Container, so transaction timeout of Spring Framework would be the most common example. In Spring, you may use XML as shown below or use @Transactional from Java source codes, for configuration.
<tx:attributes> <tx:method name="…" timeout="3"/> </tx:attributes>
Statement timeout provided by Spring is very simple. It records the starting time and the elapsed time for each transaction, and checks the elapsed time when an event occurs. If the timeout is abnormal, it generates an exception.
In Spring, the connection is stored in, and used from ThreadLocal. This is called Transaction Synchronization. When a connection is saved in ThreadLocal, the starting time and the timeout time of the transaction is also recorded. When a statement is being created by using the proxy connection, the elapsed time is checked to generate an exception.
The EJB CMT implementation is done in a similar way. The structure itself is very simple. If the transaction timeout is very important but the container or the framework you are using does not provide this feature, you could implement it yourself without major problems. There is no standard API for transaction timeout.
Lucy 1.5 and 1.6 Framework does not have a transaction timeout feature, but you can get the same result by using Transaction Manager from Spring.
If the processing time of the statement (5 or less) is 200 ms and the processing time of other business logics or framework operation is 100 ms, the transaction timeout time should be set to 1,100 ms ((200 * 5) + 100) or more.
What is Statement Timeout?
It is a limitation on how long a statement should run. It sets the timeout value for the statement, which is a JDBC API. The JDBC driver processes the statement timeout based on this value. Statement timeout is configured via java.sql.Statement.setQueryTimeout(int timeout), which is a JDBC API. In recent developing environments, the developers rarely configure the statement timeout value directly through Java source codes, but often configure it by using the framework.
To use iBatis as an example, the default value can be configured by using @defaultStatementTimeoutvalue in sqlMapConfig/settings of sql-map-config.xml. By using @timeout value, you can configure statement, select, insert and update syntax of sql-map.xml separately.
When MangedDatasource of Lucy 1.5 and 1.6 is used, the queryTimeout option can be used to get a statement of which timeout is configured at the datasource level.
The statement timeout time is configured based on the features of each application, so there is no recommended configuration value.
Statement Timeout Execution Process for JDBC Driver
Statement timeout works differently per DBMS and driver. The way it works is similar between Oracle and MS SQLServer. It is also similar between MySQL and CUBRID.
QueryTimeout for Oracle JDBC Statement
- Creates a statement by calling Connection.createStatement().
- Calls Statement.executeQuery().
- The statement transmits the Query to Oracle DBMS by using its own connection.
- The statement registers a statement to OracleTimeoutPollingThread (1 for each classloader) for timeout process.
- Timeout occurs.
- OracleTimeoutPollingThread calls OracleStatement.cancel().
- Sends a cancel message through the connection and cancels the query being executed.
Figure 4: Query Timeout Execution Process for Oracle JDBC Statement.
QueryTimeout for JTDS (MS SQLServer) Statement
- Creates a statement by calling Connection.createStatement().
- Calls Statement.executeQuery().
- The statement transmits the Query to MS SqlServer by using the internal connection.
- The statement registers a statement in TimerThread for timeout process.
- Timeout occurs.
- TimerThread calls up TsdCore.cancel() inside the JtdsStatement object.
- Sends a cancel message through the ConnectionJDBC and cancels the query being executed.
Figure 5: QueryTimeout Execution Process for JTDS (MS SQLServer) Statement.
QueryTimeout for MySQL JDBC Statement (5.0.8)
- Creates a statement by calling Connection.createStatement().
- Calls Statement.executeQuery().
- The statement transmits the Query to MySqlServer by using the internal connection.
- 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.
Figure 6: QueryTimeout Execution Process for MySQL JDBC Statement (5.0.8).
QueryTimeout for CUBRID JDBC Statement
- Creates a statement by calling Connection.createStatement().
- Calls Statement.executeQuery().
- The statement transmits the Query to CUBRID DBMS by using the internal connection.
- The statement creates a new timeout-execution thread for timeout process.
- 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 message using the connection.
Figure 7: QueryTimeout Execution Process for CUBRID JDBC Statement.
What is Socket Timeout for JDBC Driver?
JDBC driver type 4 uses the socket to connect to the DBMS, and the connection timeout process between the application and the DBMS is not carried out by the DBMS.
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.
Socket timeout has 2 options listed below, and their configurations vary by driver.
- Timeout at socket connection: Time limit for Socket.connect(SocketAddress endpoint, int timeout)
- Timeout at socket reading/writing: Time limit for Socket.setSoTimeout(int timeout)
By checking the source for CUBRID, MySQL, MS SQL Server (JTDS) and Oracle JDBC, we confirmed that all the drivers we checked use the 2 APIs above.
How to configure SocketTimeout is as explained below.
JDBC Driver | connectTimeout | Default | Unit | Application Method |
socketTimeout | Default | Unit | ||
MySQL Driver | connectTimeout | 0 | ms |
Specify the option in the DriverURL. Example: jdbc:mysql://xxx.xx.xxx.xxx:3306/database?connectTimeout=60000&socketTimeout=60000 |
socketTimeout | 0 | ms | ||
MS-SQL Driver jTDS Driver |
loginTimeout | 0 | sec |
Specify the option in the DriverURL. Format: Example: jdbc:jtds:sqlserver://server:port/database;loginTimeout=60;socketTimeout=60
|
socketTimeout | 0 | sec | ||
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:
|
oracle.jdbc.ReadTimeout | 0 | ms | ||
CUBRID Thin Driver | No separate configuration | 5,000 | ms | Not possible with the driverURL. Timeout occurs in 5 seconds.
|
- Note 1: The default value for connectTimeout and socketTimeout is "0," which means that the timeout does not occur.
- Note 2: You can also configure through properties without directly using the separate API of DBCP.
When you configure properties, pass on the character string where the key value is “connectionProperties”, and the format value is “[propertyName=property;]*”. The following example shows configuring properties through xml in iBatis.
<transactionManager type="JDBC"> <dataSource type="com.nhncorp.lucy.db.DbcpDSFactory"> .... <property name="connectionProperties" value="oracle.net.CONNECT_TIMEOUT=6000;oracle.jdbc.ReadTimeout=6000"/> </dataSource> </transactionManager>
OS Level SocketTimeout Configuration
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. However, if you look at the actual issues NHN services encountered, the problems were often resolved after the applications (WAS) tried to reconnect to the network 30 minutes after. This is because the OS can also configure socket timeout time. Linux servers used by NHN have set the socket timeout to 30 minutes. This checks the network connection at the OS level. Because the KeepAlive checking cycle for NHN's Linux servers is 30 minutes, even when socket timeout is set to 0, the DBMS network connection problems caused by network issues do not surpass 30 minutes.
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 this case, the OS tries to resend the packet for a certain amount of time, and generates an error when it reaches the limit. In NHN's Linux server environment, the timeout for this situation is set to 15 minutes.
I have explained the internal operations of JDBC so far. I hope that this will help you with the correct timeout configuration and reducing errors.
If you have more questions or any good information related to JDBC, please leave your comments below.
Lastly, I have listed some of the frequently asked questions below.
FAQ
Q1. I configured the query timeout by using Statement.setQueryTimeout(), but it does not work as expected when there is a network error.
➔ Query Timeout only works when it is connected to the socket correctly. Therefore, it cannot be used to solve an exceptional situation with a network error. To be prepared for network errors, socket timeout in JDBC driver must be configured.
Q2. How are transaction timeout, statement timeout and JDBC driver socket timeout related to the DBCP configuration values?
➔ When the connection is acquired from DBCP to JDBC, nothing but waitTimeout is affected.
Q3. If JDBC SocketTimeout is configured, wouldn't the connections that stayed in idle status for a long time in DBCP be closed?
➔ No. The socket option is applied when the actual data is being written or read, so it does not affect the connections in idle status in DBCP. The socket option can have certain effect when new connections that lack in inside of DBCP are created, old idle connections are removed, or the validation is checked, but this does not cause any significant issues unless the network has an error.
Q4. How long should SocketTimeout be set to?
➔As I have mentioned in the main article above, it must be much bigger than the statement timeout, and there is no recommended value. Socket timeout value for the JDBC driver becomes effective after a network error occurs. A careful configuration for the value cannot prevent such the errors from happening, but sometimes shortens the time that the network is disabled (if the network is restored right away).
By Woon Duk Kang, Software Engineer at Web Platform Development Lab, NHN Corporation.
相关推荐
JESD79-2F DDR2 JESD79-3F DDR3 JESD79-4D DDR4 JESD79-5C DDR5 JESD209-2F LPDDR2 JESD209-3C LPDDR3 JESD209-4E LPDDR4 JESD209-4-1A LPDDR4X JESD209-5C LPDDR5(X)
COMSOL二维光子晶体角态研究:单胞与超胞能带计算及边界态与角态特性分析,COMSOL二维光子晶体角态研究:单胞与超胞能带计算及边界态与角态特性分析,comsol二维光子晶体角态。 单胞能带,超胞能带,边界态以及角态计算。 ,comsol;二维光子晶体;角态;单胞能带;超胞能带;边界态计算,基于Comsol的二维光子晶体角态及能带边界计算研究
六自由度机械臂抓取动作仿真与代码解析:抓取动画、关节参数变化及轨迹图解详解,六自由度机械臂抓取动作仿真指南:掌握两套代码实现动画与轨迹图模拟学习攻略,六自由度机械臂抓取动作仿真-8 两套关于抓取动作的代码,包括抓取动画、关节角、角速度、角加速度的变化仿真、以及抓取轨迹图 简单易懂好上手~ ,六自由度机械臂;抓取动作仿真;抓取动画;关节角变化;角速度角加速度;抓取轨迹图;两套代码;简单易懂好上手,六自由度机械臂抓取动作仿真演示:代码与轨迹图解
ITC网络广播工具软件
Multisim四位密码锁电路仿真设计:设定、开锁与声光报警功能演示资料包,Multisim四位密码锁电路仿真设计:设定、输入、开锁与报警功能详解,附源文件、原理说明书与演示视频,multisim四位密码锁电路仿真设计 功能: 1.通过拨码开关1进行初始密码设定。 2.通过拨码开关2输入密码,实现开锁判断。 3.如果密码正确,LED绿灯亮,表示开锁。 4.如果密码不正确,LED红灯亮,蜂鸣器鸣叫,声光报警。 资料包含:仿真源文件+原理说明书+演示视频 ,四位密码锁电路、Multisim仿真设计、初始密码设定;拨码开关输入;开锁判断;LED灯显示;声光报警;仿真源文件;原理说明书;演示视频,Multisim四位密码锁电路仿真设计:初始密码设置与智能解锁功能的声光报警展示
俗话说,摸鱼摸的好,上班没烦恼,毕竟谁能拒绝带薪拉屎呢(手动狗头) 这是一个云开发职场打工人专属上班摸鱼划水微信小程序源码,没有后台 直接导入微信开发者工具即可运行,UI简约大气漂亮,只需登录微信公众平台配置完合法域名即可轻松上线。 用户进入摸鱼小程序,可以自由设置薪资,上班时间、下班时间、发薪日、 月工作天数以提醒自己摸鱼,全民打酱油,让自己成为摸鱼冠军,《商鞅摸鱼哲学》 摸鱼不是自我放纵,而是个人实力的积蓄,我们的小目标是晚睡晚起 小程序中的今日待办会提醒用户带薪拉屎和闲逛,下方展示的是距离休息日的天数,距离下一次发工资的天数和节日的天数。
【毕业设计】基于Java的开发的一个集合校园二手交易、拼车、失物招领等功能的app_pgj
个人记录:PICkit3离线烧录流程 使用软件:MPLAB X IDE v5.30 记录时间:20250215
基于Matlab代码的电力系统状态估计与实验仿真研究:扩展卡尔曼滤波和无迹卡尔曼滤波在电力系统动态状态估计中的应用及效果分析,Matlab仿真实验研究:基于扩展卡尔曼滤波器与无迹卡尔曼滤波器对电力系统状态估计的影响及验证,状态估计 电力系统状态估计 Matlab代码 实验仿真研究 电力系统由于测量值和传输误差,还有测量噪声的影响,会对状态估计产生影响。 因此,需要对嘈杂的测量进行滤波,以获得准确的电力系统运行动态。 本文使用扩展卡尔曼滤波器(EKF)和无迹卡尔曼滤波器(UKF)来估计电力系统的动态状态。 扩展卡尔曼滤波EKF、无迹卡尔曼滤波UKF 利用扩展的无迹卡尔曼滤波器估计了动力系统的动态状态。 对WECC 3机9总线系统和新英格兰10机39总线系统进行了案例研究。 结果表明EKF和UKF都能准确地估计电力系统的动态状态。 ,核心关键词:状态估计; 电力系统状态估计; Matlab代码; 实验仿真; 测量值误差; 测量噪声; 扩展卡尔曼滤波器(EKF); 无迹卡尔曼滤波器(UKF); 动力系统; 动态状态估计; WECC 3机9总线系统; 新英格兰10机39总线系统。,Matlab
springboot在线考试--
台达DVP EH3与MS300 PLC&变频器通讯程序的全面解决方案,台达DVP EH3与MS300通讯程序:稳定可靠的频率控制与启停管理系统,台达DVP EH3与台达MS300通讯程序(TDEH-9) 可直接用于实际的程序,程序带注释,并附送触摸屏程序,有接线方式和设置,通讯地址说明等。 程序采用轮询,可靠稳定 器件:台达DVP EH3系列PLC,台达MS300系列变频器,昆仑通态7022Ni 功能:实现频率设定,启停控制,实际频率读取,加减速时间设定。 资料:带注释程序,触摸屏程序,接线和设置说明,后续有技术咨询。 ,核心关键词:台达DVP EH3; 台达MS300; 通讯程序(TDEH-9); 轮询; 稳定; 频率设定; 启停控制; 实际频率读取; 加减速时间设定; 触摸屏程序; 接线方式; 设置说明; 技术咨询。,台达PLC与变频器通讯程序(带注释、触摸屏控制)
项目资源包含:可运行源码+sql文件 适用人群:学习不同技术领域的小白或进阶学习者;可作为毕设项目、课程设计、大作业、工程实训或初期项目立项。项目具有较高的学习借鉴价值,也可拿来修改、二次开发。 个人账户管理:支持用户注册、登录与个人信息编辑;提供密码找回及账号安全保护措施。 声纹采集:利用麦克风设备录制用户的声纹样本;支持多种录音格式和质量调整,确保采集到清晰、准确的声纹数据。 声纹模板库管理:建立和维护一个安全的声纹模板库;支持声纹模板的添加、删除、更新和查询操作。 声纹比对与识别:运用深度学习算法对输入的声纹数据进行特征提取和匹配;实现快速、准确的声纹身份验证。 多场景应用支持:适用于多种场景,如门禁系统、移动支付、远程登录等;可根据实际需求定制开发相应的应用场景。 实时监控与报警:实时监控系统运行状态,包括声纹识别成功率、处理速度等指标;当出现异常情况时,及时发出报警信息。 数据分析与报告生成:收集并分析声纹识别过程中的数据,如识别准确率、处理时间等;根据用户需求输出包含详细图表说明的专业级文档供下载打印保存。 社区互动交流:设立论坛版块鼓励用户分享心得体会讨论热点话题;定期邀请行业专家举办线上讲座传授实用技巧知识。 音乐筛选与推荐:集成音乐平台API,根据用户的浏览习惯和情绪状态推荐背景音乐,增强用户体验。 数据可视化:提供交互式的数据可视化面板,使非技术用户也能轻松理解复杂的数据集,从而做出更明智的决策。
三相与多相开绕组永磁同步电机仿真模型的先进控制策略探讨与实现,三相与多相开绕组永磁同步电机的Simulink仿真模型与先进控制策略研究,开绕组电机,开绕组永磁同步电机仿真模型、simulink仿真 共直流母线、独立直流母线,两相容错,三相容错控制,零序电流抑制,控制策略很多 三相开绕组永磁同步电机,六相开绕组永磁同步电机 五相开绕组永磁同步电机,五相开绕组电机 ,开绕组电机; 永磁同步电机仿真模型; simulink仿真; 共直流母线; 独立直流母线; 两相容错; 三相容错控制; 零序电流抑制; 控制策略; 六相开绕组永磁同步电机; 五相开绕组永磁同步电机,开绕组电机仿真研究:共直流母线与独立直流母线的容错控制策略
【毕业设计】基于Java的开发的网上汽车租赁管理系统_pgj
csv 模块是 Python 的标准库,无需额外安装。 运行结果如下图: ['姓名', '年龄', '城市'] ['张三', '25', '北京'] ['李四', '30', '上海'] ['王五', '22', '广州']
【毕业设计】基于Java+Springboot+Vue的宠物领养系统_pgj
让前端开发者学习“机器学习”!
【毕业设计】基于Java的实现的以宠物为主体的论坛式的APP
大模型应用工具实战2-有好玩的数字人
【毕业设计】基于ssm的选课管理系统