JDBC Timeout setting "Go"

The appropriate JDBC timeout setting can effectively reduce the time to service failure. This article describes the various time-out settings for the database and how to set it up.

Real case: Application Server cannot respond after a DDoS attack

After a DDoS attack, the entire service collapsed. As the fourth layer of switches is overwhelmed, the network becomes unreachable, leading to a business system that is not functioning properly. The security group quickly blocked all DDoS attacks and restored the network, but the business system still did not work. By analyzing the thread dump of the system, the business system stops at the call of the JDBC API. After 20 minutes, the system is still in a waiting state and cannot be responded to. After 30 minutes, the system throws an exception and the service returns to normal.

Why did we set the query timeout to 3 seconds and the system lasted 30 minutes of waiting state? Why did the system return to normal after 30 minutes? When you understand the timeout setting for JDBC, you can find the answer to the question.

Why do we need to understand JDBC?

Business systems and databases are usually the two parts we care about most when performance problems or system errors occur. In the company, these two parts are assigned to two different departments to be responsible, so each department will focus on the problem in their own domain, so that the business system and the part of the database will become a blind spot. For Java applications, this blind spot is the DBCP database connection pool and JDBC, and this article focuses on JDBC.

What is JDBC?

JDBC is the standard API used in Java applications to connect relational databases. Sun has a total of 4 types of JDBC defined, and we primarily use the 4th type of driver, which is fully implemented by Java code and communicates with the database by using a socket.

Figure 1 JDBC Type 4.

The 4th type of JDBC handles the byte stream through the socket, so there will be some basic network operations similar to httpclient, a code base for network operations. When a problem is encountered in a network operation, it consumes a lot of CPU resources and loses the response time-out. If you've used httpclient before, you've certainly encountered an error caused by a timeout not set. Similarly, the 4th type of JDBC will have the same error if the socket timeout is not properly set-the connection is blocked.

Next, let's learn how to properly set up the socket timeout and the issues to consider.

The timeout level between the application and the database

Figure 2 Timeout Class.

Shows the timeout level between the simplified application and the database. (Translator Note: Was/bloc is the company's specific application name, no need to delve into)

A high-level timeout relies on a low-class timeout, which is guaranteed to be normal only if the lower-level timeout is correct. For example, high-level statement timeout and transaction timeout will expire when there is a problem with the socket timeout.

Many of the comments we received mentioned:

Reference:

Even if statement timeout is set, the application cannot recover from the error when the network error occurs.

Statement timeout cannot handle a time-out when the network connection fails, it can only do is to limit the operation time of statement. Timeout in the case of a network connection failure must be referred to JDBC for processing.

The JDBC socket timeout is affected by the OS socket timeout setting, which explains why in the previous case the JDBC connection was blocked for 30 minutes after a network error, and then miraculously resumed, even though we did not have a socket for JDBC Timeout to set.

The DBCP connection pool is located on the left side of Figure 2 and you will find that the timeout level is independent of the DBCP. DBCP is responsible for the creation and management of database connections and does not interfere with the processing of timeout. When the connection is created in dbcp, or the DBCP sends a validation query to check the validity of the connection, the socket timeout will affect these processes, but it does not directly affect the application.

When calling DBCP's Getconnection () method in your app, you can set the time-out for getting a database connection, but this is irrelevant to JDBC timeout.

Figure 3 Timeout for each levels.

What is transaction Timeout?

Transaction timeout is typically present at the framework (Spring, EJB) or application level. Transaction timeout may be a relatively unfamiliar concept, simply put, transaction timeout is "statement timeout * N (number of statement to be performed) + @ (garbage collection and other time)". Transaction timeout is used to limit the total length of time the statement is executed.

For example, assuming that a statement takes 0.1 seconds to execute, there is no problem with performing a small amount of statement, but it takes 10,000 seconds (about 7 hours) to execute 100,000 statement. At this point, transaction timeout comes in handy. EJB CMT (Container Managed Transaction) is a typical implementation that provides a variety of methods for developers to choose from. But we don't use Ejb,spring's transaction timeout setting to be more common. In spring, you can use the XML shown below or use @transactional annotations in the source code to set it up.

XML code:

Spring provides a very simple transaction timeout configuration that records the start time and consumption time of each transaction, and when a particular event occurs, the time spent is verified and an exception is thrown when the timeout value is exceeded.

In spring, the database connection is saved in Threadlocal, which is called Transaction synchronization (Transaction synchronization), while the start time and consumption time of the transaction are also preserved. When a statement is created with this proxy connection, the transaction's consumption time is verified. EJB CMT is implemented in a similar way, and its structure itself is very simple.

When you choose a container or frame that does not support the Transaction timeout feature, you can consider your own implementation. Transaction timeout does not have a standard API. The 1.5 and 1.6 versions of the Lucy framework do not support transaction timeout, but you can achieve the same effect by using Spring's transaction manager.

Assuming that a transaction contains 5 statement, the execution time for each statement is 200ms, and the execution time of the other business logic is 100ms, then transaction timeout should at least be set to 1,100MS (200 * 5 + 100).

What is statement Timeout?

Statement timeout is used to limit the execution duration of statement, and the value of timeout is set by calling the JDBC java.sql.Statement.setQueryTimeout (int timeout) API. However, developers are now rarely set up directly in the code, but mostly through the framework.

Take Ibatis as an example, the default value of statement timeout can be set by the Defaultstatementtimeout property in Sql-map-config.xml. You can also set the Timeout property of the select,insert,update tag in Sqlmap to configure the time-out for different SQL statements independently.

If you are using Lucy1.5 or version 1.6, you can set the statement timeout at the datasource level by setting the QueryTimeout property.

The exact value of the statement timeout needs to be based on the characteristics of the application itself, and there is no recommended configuration.

JDBC's statement timeout process

Different relational databases, as well as different JDBC drivers, will differ in their statement timeout process. Where Oracle and MS SQL Server are handled similarly, MySQL and Cubrid are similar.

QueryTimeout process for Oracle JDBC statement

1. Create statement by calling connection's Createstatement () method

2. Call Statement's ExecuteQuery () method

3. Statement sends query to Oracle database via its own connection

4. Statement registration on Oracletimeoutpollingthread (one per ClassLoader)

5. Reach the time-out period

6. Oracletimeoutpollingthread Call Oraclestatement's Cancel () method

7. Send the cancel message to the executing query via connection

Figure 4 Query Timeout execution Process for Oracle JDBC Statement.

QueryTimeout process for Jtds (MS SQL Server) statement

1. Create statement by calling connection's Createstatement () method

2. Call Statement's ExecuteQuery () method

3. Statement sends query to MS SQL Server database via its own connection

4. Statement registration on the TimerThread

5. Reach the time-out period

6. TimerThread calls the Tsdcore.cancel () method in the Jtdsstatement instance

7. Send the cancel message to the executing query via CONNECTIONJDBC

Figure 5 QueryTimeout execution Process for JTDS (MS SQL Server) Statement.

QueryTimeout process for MySQL JDBC statement

1. Create statement by calling connection's Createstatement () method

2. Call Statement's ExecuteQuery () method

3. Statement sends query to MySQL database via its own connection

4. Statement create a new timeout-execution thread for time-out processing

5. After version 5.1, each connection is assigned a timeout-execution thread

6. Registering with the timeout-execution thread

7. Reach the time-out period

6. TimerThread calls the Tsdcore.cancel () method in the Jtdsstatement instance

7. Timeout-execution thread creates a connection with the same statement configuration

8. Send the Cancel query (KILL query "ConnectionID") to the timeout query using the newly created connection

Figure 6 QueryTimeout execution Process for MySQL JDBC Statement (5.0.8).

The QueryTimeout process of Cubrid JDBC statement

1. Create statement by calling connection's Createstatement () method

2. Call Statement's ExecuteQuery () method

3. Statement sends query to CUBRID database via its own connection

4. Statement create a new timeout-execution thread for time-out processing

5. After version 5.1, each connection is assigned a timeout-execution thread

6. Registering with the timeout-execution thread

7. Reach the time-out period

6. TimerThread calls the Tsdcore.cancel () method in the Jtdsstatement instance

7. Timeout-execution thread creates a connection with the same statement configuration

8. Send the cancel message to the timeout query using the newly created connection

Figure 7 QueryTimeout execution Process for Cubrid JDBC Statement.

What is the socket timeout for JDBC?

The 4th type of JDBC uses a socket to connect to a database, and the database does not process the connection timeout between the application and the database.

The JDBC socket timeout is important when the database is suddenly stopped or a network error occurs due to a device failure. Because of the structure of TCP/IP, the socket has no way to detect a network error, so the application cannot proactively discover that the database connection is broken. If the socket timeout is not set, the application will wait indefinitely until the database returns results, which is known as dead connection.

In order to avoid dead connections,socket must have a timeout configuration. The socket timeout can be set through JDBC, and the socket timeout avoids the endless wait when a network error occurs, shortening the time of service failure.

Socket timeout is not recommended to limit the execution time of statement, so the value of the socket timeout must be higher than statement timeout, otherwise the socket timeout will take effect first, so statement Timeout becomes meaningless and does not take effect.

The following shows the two settings for the socket timeout, and the different JDBC drivers are configured differently.

Timeout at Socket connection: set by Socket.connect (socketaddress endpoint, int timeout)

Timeout when socket reads and writes: set by socket.setsotimeout (int timeout)

By looking at Cubrid,mysql,ms SQL Server (Jtds) and the JDBC driver source for Oracle, we found that all drivers are internally using the 2 APIs above to set up socket timeout.

Here are the different drivers for how the socket timeout is configured.

The default value for ConnectTimeout and sockettimeout is 0 o'clock, and timeout does not take effect.

In addition to calling the API for DBCP, you can configure it through the Properties property.

When configured with the Properties property, a key value pair with the key "ConnectionProperties" is passed in, and the value is in the format "[Propertyname=property;] *”。 The following is the properties configuration in Ibatis.

XML code:

Socket timeout configuration for the operating system

If you do not set a socket timeout or connect timeout, you will not be able to discover network errors in most cases. Therefore, when a network error occurs, the application waits indefinitely until the connection is reconnected successfully or the data is successfully received. However, through the actual case at the beginning of this article, we found that 30 minutes after the application of the connection problem miraculously solved, this is because the operating system is also able to configure the socket timeout. The company's Linux server sets the socket timeout to 30 minutes, which verifies the network connection at the operating system level, so even if the JDBC socket timeout is set to 0, the duration of the database connection problem caused by the network error will not be longer than 30 minutes.

Typically, the app will be blocked while calling Socket.read () because of network problems and rarely enter the waiting state when calling Socket.write (), depending on the network composition and the type of error. When Socket.write () is called, the data is written to the buffer of the operating system kernel, and control is immediately returned to the application hand. Therefore, once the data is written to the kernel buffer, the Socket.write () call is bound to succeed. However, if the kernel buffer of the system is full due to a network error, Socket.write () will enter the waiting state. In this case, the operating system will attempt to re-contract, resulting in a system error when the retry time limit is reached. In our company, the time-out for the re-contract is set to 15 minutes.

At this point, I have already explained the internal operation of JDBC, and I hope that we can learn how to configure the time-out correctly, so as to reduce the occurrence of errors.

Finally, I'll list some common questions.

FAQs

Q1. I've set the query timeout using the Statement.setquerytimeout () method, but it didn't work when the network went wrong.

? The query timeout is only valid if the socket timeout is in effect, it cannot be used to resolve external network errors, and the JDBC socket timeout must be set to resolve this problem.

Q2. What is the relationship between transaction timeout,statement timeout and the configuration of the socket timeout and dbcp?

? When you get a database connection through DBCP, other configurations have no effect on JDBC other than the Waittimeout configuration when dbcp gets the connection.

Q3. If a JDBC socket timeout is set, will the connection in the DBCP connection pool be closed after the timeout is reached?

? No. The socket setting only takes effect when the data is read and written, without affecting the idle connection in the DBCP. When a new connection is created in the DBCP, the old idle connection is removed, or the connection is validated, the socket setting has a certain effect on it, but the impact is minimal unless a network problem occurs.

Q4. What should the socket timeout be set to?

? As I mentioned in the text, the socket timeout must be higher than statement timeout, but there is no recommended value. In the event of a network error, the socket timeout will take effect, but careful configuration will not prevent the network error, only after the network error occurs, shorten the time of service failure (if the network is back to normal).

Original address: http://jingyan.baidu.com/article/fc07f98922615a12ffe519ce.html

JDBC Timeout setting "Go"