The issue of SQL database connection timeout expired

catalogueSQL database connection timeout has expiredproblemresolventSummary of database access timeout configuration1. Background2

SQL database connection timeout has expired

problem

1: System. InvalidOperationException: The timeout has expired. The timeout has expired, but the connection has not yet been obtained from the pool. This situation may occur because all pool connections are in use and the maximum pool size has been reached.

2: A transport level error occurred while sending a request to the server. (provider: TCPProvider, error: 0- Unable to perform operations on sockets due to insufficient system buffer space or full queue.)

3: A connection was successfully established with the server, but an error occurred during the handshake before login. (provider: SSLProvider, error: 0- The waiting operation is outdated.) --> System. ComponentModel. Win32Exception (0x80004005): The waiting operation is outdated.

4: A transport level error occurred while receiving results from the server. (provider: TCPProvider, error: 0- The specified network name is no longer available.) --> System. ComponentModel. Win32Exception (0x80004005): The specified network name is no longer available.

5: The connection timeout has expired. The timeout period has been exceeded during the post login phase. The connection may timeout while waiting for the server to complete the login process and respond; Or timeout when attempting to create multiple active connections. The duration spent attempting to connect to this server is - [Pre Login] initialization=2; handshake=5 [Login]initialization=0 authentication=0 [Post-Login]complete=14025---& gt; System. ComponentModel. Win32Exception (0x80004005): The waiting operation is outdated.

resolvent

Set maximum timeout time

server=192.168.0.1; UserID=sa; Password=123; database=; MinPoolSize=0; MaxPoolSize=30000; Pooling=true;

In general, in cases of high concurrency requests, the following code can check the number of SQL connections, which can be used as a means of monitoring.

SELECT[program_name],[spid],*FROM[sys]. [sysprocesses]WHERE[spid]> 50and[program_name]='.NetSqlClientDataProvider'anddbid=db_ id('')

Summary of database access timeout configuration

1. Background

Accessing the database for too long, accessing a large amount of data, or scanning a large amount of data, resulting in the database being unresponsive for a long time. If the link is occupied and cannot be released, the thread pool will be full.

Therefore, in order to timely release the occupied links and ensure that other businesses are not affected by database access, it is necessary to set the database access timeout time reasonably.

2. Configuration Description

2.1 ConnectionTimeout Configuration Size

The connectTimeout configuration of Jdbc refers to the timeout period for waiting to establish a socket link with the database. If not set, the default is 0, which means never timeout.

The configuration of checkoutTimeout in the code is set to 1000s, which means the connection acquisition link timeout in the Connection pool. If it is set too high, the interface response will be slow.

Referring to the experience value on the acquiring side, the connectTimeout is set to 1000ms.

2.2 SocketTimeout Configuration Size

The configuration of socketTimeout refers to the timeout period during which the client waits to read and write sockets after establishing a socket with the database. If not set, the default is 0, which means never timeout. Zebra's default socketTimeout is set to 60 seconds.

According to statistics on accessing the service database, it was found that the maximum length is 1-2s, and socketTimeout can be set to 3s

3. Plan

3.1 Database SDK Configuration Modification

The connection timeout settings of the Connection pool need to be set to the extraJdbcUrlParams parameter as follows:

< propertyname="extraJdbcUrlParams"value="connectTimeout=1000&amp;socketTimeout=3000&amp;useSSL=false"/>

Jdbcdriver5.1.36 and above require" to be set in jdbcurl; useSSL=false" Parameters can be set in this way

3.2 Default SDK Configuration Modification

Modifying the jdbcRef configuration

3.3 Final plan

summary

The above is my personal experience and I hope to provide you with a reference. I also hope that you can support Script Home more.