Example of how to configure the MySQL database timeout setting

Preface

Recently, I was preparing for JD.com's Double 11 shopping festival. When configuring MySQL's timeout configuration, I found that there are many places where I can set it. What are the effects of so many timeout configurations, and what effects will the configurations have? Let me analyze it in today's article.

1. JDBC timeout settings

connectTimeout: indicates the timeout for waiting to establish a socket connection with the MySQL database. The default value is 0, which means no timeout is set. The unit is milliseconds. 30000 is recommended.

socketTimeout: indicates the waiting timeout for reading and writing sockets after the client and MySQL database establish a socket. The default socketTimeout of the Linux system is 30 minutes, which can be left unchanged.

2. Connection pool timeout settings

maxWait: indicates the waiting time when there is no available connection in the database connection pool. The default value is 0, which means infinite waiting. The unit is milliseconds. 60000 is recommended.

3. MyBatis query timeout

defaultStatementTimeout: indicates the default query timeout in the MyBatis configuration file, in seconds. If not set, there will be no waiting time.

If some SQL statements need to be executed for more than the defaultStatementTimeout, the timeout of a separate SQL statement can be configured in the Mapper file.

4. Transaction timeout

Transaction timeout is used to control the timeout of transaction execution. The execution time is the sum of all code executions within the transaction, in seconds.

Summarize

The higher-level timeout depends on the lower-level timeout. Only when the lower-level timeout is correct can the higher-level timeout be guaranteed to be normal. For example, when the socket timeout fails, both the high-level statement timeout and transaction timeout will become invalid.

1. Transaction Timeout

The transaction timeout configuration provided by Spring is very simple. It records the start time and consumption time of each transaction. When a specific event occurs, the consumption time will be checked. When the timeout value is exceeded, an exception will be thrown.

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

2. Statement Timeout

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

In iBatis, the default value of statement timeout can be set through the defaultStatementTimeout attribute in sql-map-config.xml. At the same time, you can also set the timeout attribute of the select, insert, and update tags in sqlmap to independently configure the timeout of different SQL statements.

3. Socket timeout

JDBC socket timeout is very important when the database is suddenly stopped or a network error occurs (due to device failure, etc.). Due to the structure of TCP/IP, the socket has no way to detect network errors, so the application cannot actively discover that the database connection is disconnected. If the socket timeout is not set, the application will wait indefinitely before the database returns the result. This connection is called a dead connection.

To avoid dead connections, the socket must have a timeout configuration. The socket timeout can be set through JDBC. The socket timeout can prevent the application from waiting endlessly when a network error occurs, thus shortening the service failure time.

It is not recommended to use socket timeout to limit the execution time of a statement. Therefore, the socket timeout value must be higher than the statement timeout value. Otherwise, the socket timeout value will take effect first, making the statement timeout value meaningless and ineffective.

This is the end of this article about MySQL database timeout settings and configuration. For more relevant MySQL database timeout settings and configuration content, please search for previous articles on 123WORDPRESS.COM or continue to browse the related articles below. I hope everyone will support 123WORDPRESS.COM in the future!