This is recently read about JDBC timeout problems the most thorough, the original is http://www.cubrid.org/blog/understanding-jdbc-internals-and-timeout-configuration, The existing translations on the Internet are stumbling and a lot of contextual information is lost. Here is a new translation with my understanding.

Properly configured JDBC timeouts in your application can reduce the time to service failure, and in this article we will discuss different types of timeouts and recommended configurations.

The Web application server becomes unresponsive after a DDoS attack

(This is a retelling of a real case)

After the DDoS attack, the entire service did not work properly because the layer 4 switch did not work and the network connection WAS disconnected, which also caused WAS (can be understood as the application of the author’s company) to not work properly. Shortly after the attack, the security team intercepted all DDoS attacks and the network WAS back up, but WAS still not working.

Analysis of dump logs shows that the service system stops calling the JDBC API. After 20 minutes, the system is still in the waiting state and cannot respond. After about 30 minutes, an exception occurs and services are restored.

Why did WAS wait 30 minutes after the query timeout WAS set to 3 seconds? Why WAS working again 30 minutes later?

You can find the answer if you understand the TIMEOUT mechanism of JDBC.

Why do we need to know the JDBC driver

WAS and the database are two important layers to focus on when there are performance issues or system-level errors. In my company WAS and database are usually handled by different departments, so each department focuses on its own area to try to figure out what is going on. At this point, the part between WAS and the database will have a blind spot because of insufficient attention. For Java applications, this dead zone is between database connection pooling and JDBC, which we’ll focus on in this article.

What is a JDBC driver

JDBC is a set of standard apis for Java applications to access databases. Sun defines four types of JDBC drivers. My company mainly uses the fourth type, which is driven by pure Java language, and communicates with the database through socket in Java applications.

The type 4 driver handles byte streams through sockets and has the same basic operations as HttpClient, a network operation library. As with other network libraries, they can take up a lot of CPU resources and become unresponsive when timeouts occur. If you’ve used HttpClient before, you’ve probably encountered an error caused by not setting a timeout. Type 4 drivers may have the same error (the connection is blocked) if the socket timeout setting is not appropriate.

Let’s learn how to configure socket timeout for JDBC drivers and what to consider when setting it.

The level of set timeouts between WAS and the database

Figure 2 shows the simplified timeout hierarchy for communication between WAS and the database.

The higher level timeouts depend on the lower level timeouts, and the upper level timeouts work only when the lower level timeouts work properly. If the SOCKET timeout of the JDBC driver does not work properly, then higher-level timeouts such as Statement and transaction timeouts will not work properly.

We get a lot of comments saying,

Even if the Statement timeout is configured, the application cannot recover from the failure because the Statement timeout does not take effect in the event of a network failure.

The Statement timeout does not take effect in the event of a network failure. It can only do this by limiting the time a Statement is executed, and handling timeouts in case of a network failure must be done by a JDBC driver.

The SOCKET timeout of the JDBC driver is also affected by the socket timeout configuration of the operating system. This explains why the JDBC connection in the case was blocked for 30 minutes after the network failure before being restored, even though the SOCKET timeout for the JDBC driver was not configured.

The DBCP connection pool is on the left side of Figure 2. You’ll notice that timeouts at various levels are separate from DBCP. DBCP is responsible for the creation and management of database connections, referred to in this article, and does not involve handling timeouts. When a database connection is created in DBCP or an SQL statement is sent to check the validity of the connection, socket timeout affects the processing of these processes, but does not directly affect the application.

However, when you call DBCP’s getConnection() method in an application, you can specify how long the application must time out to obtain a database connection, but this is independent of JDBC connection timeouts.

What is transaction timeout

Transaction timeouts are timeouts that are valid only at the framework (Spring, EJB container) or application level.

Transaction timeouts may be an uncommon concept. In simple terms, the transaction timeout is equal to ** Statement timeout * N (the number of statements that need to be executed) + other (garbage collection, etc.) **. Transaction timeouts are used to limit the total time for all Statement executions within a transaction.

For example, if executing a Statement takes 0.1 seconds to execute, then executing a Statement several times is not a problem, but executing a Statement 100,000 times takes 10,000 seconds (approximately 7 hours), which is a good time to use a transaction timeout.

EJB declarative transaction management (container-managed transaction) is a typical usage scenario, but declarative transaction management only defines the corresponding specification, and the processing and implementation of transactions within the container is the responsibility of the container developer. Our company does not use EJBs and uses the most common Spring framework, so the configuration of transaction timeouts is also managed by Spring. In Spring, transaction timeouts can be configured explicitly in XML files or with the Transactional annotation in Java code.

<tx:attributes>
        <tx:method name="..." timeout="3"/>
</tx:attributes>
Copy the code

Spring provides very simple configuration for transaction timeouts. It records the start time and elapsed time of each transaction, verifies the elapsed time when a specific event occurs, and throws an exception if it exceeds the configuration.

In Spring, database connections are stored in ThreadLocal variables, which is called Transaction Synchronization. When a database connection is saved to ThreadLocal, both the start time and the timeout time of the transaction are recorded. So statements created through agents connected to the database will be checked against this time when they are executed.

EJB’s implementation of declarative transaction management is similarly simple. If transaction timeouts are important and your container or framework doesn’t provide them, you can implement them yourself. There is no standard API for transaction timeouts.

Versions 1.5 and 1.6 of the Lucy framework do not support transaction timeouts, but you can achieve the same effect with Spring’s transaction management.

If a transaction contains five statements, each of which takes 200 milliseconds to execute, and other business logic or framework operations take 100 milliseconds to execute, the transaction should allow a timeout of at least 1100 milliseconds (200 x 5 + 100).

What is Statement timeout

The Statement timeout is used to limit the execution time of the Statement, and its specific value is set through the JDBC API. The JDBC driver performs timeout processing on Statement execution based on this value. Statement timeout is configured using the setQueryTimeout(int Timeout) method of the java.sql.Statement class in the JDBC API. However, developers today rarely configure it directly in code, rather through frameworks.

For iBatis, you can use the setting attribute defaultStatementTimeout in sqlmapconfig. XML to set the global statement timeout default value. You can also override this by using the Statement attribute of the SELECT INSERT UPDATE tag in the specific SQL mapping file.

When you use Lucy 1.5 or 1.6, you can set the Statement timeout at the data source level by setting the queryTimeout property.

The value of the Statement timeout depends on each application and there is no recommended value.

Statement timeout process in the JDBC driver

Statement timeouts are handled differently for each database and driver. Oracle and SQLServer work in a similar way, MySQL and CUBRID work in a similar way.

Statement timed out in Oracle

  1. Call the createStatement() method in Connection to create a Statement object
  2. Call the executeQuery() method in Statement
  3. Statement sends query commands to the Oracle database through an internally bound Connection object
  4. The Statement to the Oracle timeout processing threads OracleTimeoutPollingThread (one per class loader this thread) to register a Statement is used to deal with the timeout
  5. Timeout occurred
  6. The Oracle OracleTimeoutPollingThread call OracleStatement cancel () method
  7. Send a message through the Statement’s Connection to cancel a query that is still running

Statement timeout processing in JTDS (MS SQLServer)

1. Call the Connection createStatement() method to create a Statement object. 2. Call Statement’s executeQuery() method. 3. Statement sends query commands to the MS SqlServer database through an internal Connection TimerThread The thread registers a Statement to handle timeouts 5. TimerThread calls the tsdcore.cancel () method inside JtdsStatement 7. Send a message via ConnectionJDBC to cancel the query that is still executing

Statement timed out in MySQL (5.0.8)

  1. Call the createStatement() method in Connection to create a Statement object
  2. Call the executeQuery() method in Statement
  3. Statement transfers query commands to the MySqlServer database through an internal Connection
  4. Statement creates a new timeout-execution thread to handle the timeout
  5. Version 5.1 above changed to allocate one thread per connection
  6. Register the current Statement with the timeout-execution thread
  7. Timeout occurred
  8. The timeout-execution thread creates a Connection with the same configuration
  9. Send the command to cancel the query with the newly created Connection

Statement timeout in CUBRID

  1. Call the createStatement() method in Connection to create a Statement object
  2. Call the executeQuery() method in Statement
  3. Statement sends query commands to the CUBRID database through an internal Connection
  4. Statement creates a new timeout-execution thread to handle the timeout
  5. Register the current Statement with the timeout-execution thread
  6. Timeout occurred
  7. The timeout-execution thread creates a Connection with the same configuration
  8. Send the command to cancel the query with the newly created Connection

What is a Socket timeout

Type 4 JDBC drivers connect to the database using sockets, and connection timeouts between the application and the database are not handled by the database.

The Socket timeout value for the JDBC driver is required when the database suddenly goes down or a network error occurs (device failure, etc.). Because of the TCP/IP structure, the Socket has no way of detecting network errors, so the application cannot detect a disconnection from the database. If the Socket timeout is not set, the application waits for the database to return the result. (This connection is also called a dead connection.) To avoid dead connections, the Socket must set a timeout. Socket timeout can be configured using the JDBC driver. By setting Socket timeout, you can prevent waiting for network errors and shorten the fault time.

You are not advised to use a Socket timeout to limit the execution time of a Statement. Therefore, the value of the Socket timeout must be higher than the value of the Statement timeout. Otherwise, the Socket timeout takes effect first.

The following shows the connection options for Socket timeout Settings, which vary from driver to driver.

  • Timeout during Socket connection: This parameter is configured using the connect(SocketAddress endpoint, int timeout) method of the Socket object
  • Timeout of Socket read/write: Set by setSoTimeout(int timeout) of the Socket object

By reviewing the JDBC driver source code for CUBRID, MySQL, MS SQL Server (JTDS), and Oracle, we confirm that all of the above drivers use the above two apis to set socket timeout.

How to configure Socket timeout

JDBC driver Connection Timeout Configuration Socket Timeout Configuration The JDBC Url format The sample
MySQL ConnectTimeout (Default: 0, in milliseconds) SocketTimeout (Default: 0, ms) jdbc:mysql://[host:port],[host:port]… /[database]

[?propertyName1][=propertyValue1][&propertyName2][=propertyValue2]…		 jdbc:mysql://xxx.xx.xxx.xxx:3306/database? connectTimeout=60000&socketTimeout=60000
MS-SQL , jTDS LoginTimeout (Default: 0, unit: second) SocketTimeout (Default: 0, unit: s) jdbc:jtds:<server_type>://[:][/][;=[;…]] jdbc:jtds:sqlserver://server:port/database; loginTimeout=60; socketTimeout=60
Oracle Oracle.net.CONNECT_TIMEOUT (default: 0, milliseconds) Oracle.jdbc.readtimeout (default: 0, in milliseconds) Does not support via the url configuration, only through OracleDatasource. SetConnectionProperties () API set, When using DBCP can invoke the BasicDatasource. SetConnectionProperties () or BasicDatasource. AddConnectionProperties () to set –
CUBRID No individual configuration item (Default: 5,000 milliseconds) No individual configuration item (Default: 5,000 milliseconds) – –
  • The default value for connectTimeout and socketTimeout is 0, which means that no timeout will occur.
  • You can also configure through properties without using DBCP’s API directly.

For properties, the key is “connectionProperties” and its value is in the format of “[propertyName=property;]. * “. The following is an example of configuring attributes in iBatis through an XML file.

<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>
Copy the code

Socket timeout configuration at the operating system level

If Socket timeout or connection timeout is not set, the application will not detect network errors in most cases. At this point, the application will wait until it connects to the database or can read data. However, if you look at what happens to the actual service, you will find that the problem is often resolved after the application (WAS) tries to reconnect to the network 30 minutes later. This is because the operating system is also configured with Socket timeout. The Linux server used by my company sets the Socket timeout to 30 minutes. It verifies network connections at the operating system level. Because the company’s Linux servers have a KeepAlive check interval of 30 minutes, even if the Socket timeout is set to 0 in the application, the database connection problems caused by network causes will not last longer than 30 minutes.

Typically, the application will block when calling the Socket’s read() method due to network problems. However, it is rare to call the Socket’s write() method in a wait state, depending on network composition and error type. When an application calls the Socket’s write() method, the data is logged into the operating system’s kernel buffer, and control is immediately returned to the application. Thus, once the data has been written to the kernel buffer, the call to write() is always successful. However, if the operating system kernel buffer is full due to a specific network error, the write() method also goes into wait state. In this case, the operating system tries to resend the packet for a certain amount of time and generates an error when the timeout limit is reached. On the company’s Linux server, the timeout is set to 15 minutes.

So far, I’ve explained JDBC internals, which I hope will help you configure the timeout correctly to reduce errors.

So far, I have covered JDBC internals, hoping to show you how to properly configure the timeout period to reduce errors.