The timeout expired timeout time has reached. The maximum pool size error and the Max pool size setting have been reached.

Reference Database Link string:

<Add key = "data" value = "Server = 192.168.1.123; Port = 3306; uid = root; Pwd = root; database = data; pooling = true; min pool size = 5; max pool size = 512; Connect timeout = 20; "/>

 

View the application pool usage:

Select * From sysprocesses where dbid = db_id ('database name ')

 

 

Max pool size: If not set, the default value is 100, and the theoretical maximum value is 32767. The maximum number of connections is the maximum number of connections that can be applied by the connection pool. If the number of database connection requests exceeds this limit, the subsequent database connection requests will be added to the waiting queue, which will affect subsequent database operations. In the waiting queue, the default waiting time to connect to the server is 15 seconds.

 

Chinese error:

The timeout has reached. The timeout time has reached, but the connection has not been obtained from the pool. This may occur because all pool connections are in use and the maximum pool size is reached.

English error:

Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and Max pool size was reached.

Problem description: The above exception occurs when the connection pool exceeds the maximum value. Generally, the maximum connection pool value is 100. When we get a connection that exceeds the maximum value, ADO. net waits for the connection pool to return a connection and times out. This will throw the above exception.
Solution: The first thing we need to do is to close the connection immediately after we use the connection. If the connection is not closed, the connection will be saved to the connection pool and GC will be known for destruction. In this case, you think the connection pool has not reached the maximum value, but the connection pool has actually reached the maximum value. Secondly, we can dynamically expand the maximum number of connections in the connection pool by using the Max pool size = N in the connection string.

Note:

That is, if not specified in connectionstringMax pool size, ThenMax pool size= 100. When the number of people who access the database at the same time is 101, wait for sqlconnection. after the connectiontimeout time is set (the default value is 15 seconds), the above error occurs if there is no available connection.

When we set it:

"Server = (local); Integrated Security = sspi; database = northwind; Max pool size = 512; min pool size = 5. When the number of concurrent database connections is 513, the user waits for sqlconnection. after the connectiontimeout time is set (the default value is 15 seconds), the above error occurs if there is no available connection.

 

 

- Connection Pool What is it?Every time the program needs to read and write the database. Connection. open () will connect to the database using connectionstring, the database will establish a connection for the program and stay open, then the program can use T-SQL statements to query/update the database. When connection. Close () is executed, the database closes the connection before the execution. Good. Everything looks so methodical. However, if my program needs to open and close the connection from time to time, such as ASP. net or Web Service). For example, when an HTTP request is sent to the server, we need to open the connection and use select * from table to return a datatable/dataset to the client/browser, then close the current connection. So frequent open/close connection operations will undoubtedly become a waste for the entire system. Ado. Net team provides a better solution. Save the previous connection. The previous connection is handed over to the next connection when you need to open the connection. This is the connection pool.


- Connection Pool How does it work?First, when a program executes connection. when open () is enabled, ado.net needs to determine whether the connection pool is supported (pooling is set to true by default). If it is set to false, ado.net creates a connection with the database (to avoid confusion, all connections in the database use the "connection" Description) and then returns it to the program.

If it is set to true, ado.net will create a connection pool based on connectstring, and then fill in the connection pool with the connection (all connections in. net programs use the "connection" description ). The number of connections filled is determined by the min pool size (0 by default) attribute. For example, if it is set to 5, ado.net opens five connections with the SQL database at a time, then stores four connections in the connection pool, and one connection is returned to the program.

When the program runs connection. Close. If pooling is true, ado.net places the current connection in the connection pool and maintains the connection with the database.

At the same time, the connection lifetime (default value: 0) attribute will be determined. 0 represents an infinite number. If the connection exists for a longer time than the connection lifetime, ado.net will close the connection and disconnect the database, instead of saving it to the connection pool again.

(This setting is mainly used in the Cluster's SQL database to achieve load balancing ). If pooling is set to false, the connection to the database is directly disconnected.

Then, when connection. open () is executed, ADO. NET will judge whether the new connectionstring is consistent with the connectionstring of the connection stored in the connection pool.

(ADO. net converts connectionstring to a binary stream. That is to say, the new connectionstring must be exactly the same as the connectionstring of the connection stored in the connection pool, even if a space is added, or the order of some properties in the connection string will make ADO. net considers this as a new connection and creates a new connection. Therefore, if you use the userid and password authentication method, modifying the password will also lead to a connection. If you use SQL Integration authentication, you need to save the two connections using the same one ).

Then, ado.net needs to determine whether there is a usable connection in the current connection pool (not occupied by other programs). If not, ado.net needs to judge the Max pool size (100 by default) set by connectionstring. If all connections in the connection pool do not reach the Max pool size, ado.net will connect to the database again and create a connection, then return the connection to the program.

If maxpoolsize is reached, ado.net will not create any new connection again, but will wait for the connection occupied by other programs in the connection pool to be released. The waiting time is affected by sqlconnection. connectiontimeout (the default value is 15 seconds). That is to say, if the time exceeds 15 seconds, sqlconnection will throw a timeout error (sometimes if sqlconnection is used. the open () method throws a timeout error. One possible cause is that the previous connnection is not closed in time, and the number of connection pools reaches maxpoolsize .)

If there is an available connection, the connection retrieved from the connection pool is not directly returned to the program. ado.net also needs to check the connectionreset attribute of connectionstring (true by default) whether to reset the connection at most. This is because the connection returned from the program may have been modified, for example, sqlconnection. changedatabase method modifies the current connection. The returned connection may not be connected to the initial Catalog Database specified by the current connection string. Therefore, you need to reset the current connection once. However, all additional checks increase the overhead of the ado.net connection pool on the system.