The timeout period has expired, but the connection has not been obtained from the pool. This can happen because all pooled connections are in use and the maximum pool size is reached.

Timeout expired time-out has arrived. Max Pool Size error 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; "/>

To view the application pool usage amount:

SELECT * from sysprocesses where dbid= db_id (' Database name ')

Max Pool Size: The default is 100 if not set, and the theoretical maximum value is 32767. The maximum number of connections is the maximum number of connections the connection pool can request, and if the database connection request exceeds this number, subsequent database connection requests are added to the wait queue, which affects subsequent database operations. In the wait queue, the default time to wait for a connection to the server is 15 seconds.

Chinese error:

The timeout period has expired. The timeout period has expired, but the connection has not been obtained from the pool. This can happen because all pooled 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 has occurred because all pooled connections were in use and Max pool size is reached.

Problem Description: We get a connection that exceeds the maximum connection pool value when the top exception is generated. The maximum connection pool value is typically 100. When we get a connection that exceeds the maximum value, ADO. NET waits for the connection pool to return a connection and time out, which throws an exception as above
Workaround: The first thing to do is to close the connection immediately after we use the connection. If the connection is not closed then the connection will be saved to the connection pool knowing that the GC is destroyed. In this case you think the connection pool did not reach the maximum but actually the connection pool has reached the maximum second we can dynamically expand the maximum number of connections in the connection pool by using max pool Size = N in the connection string .

Description

That is, in ConnectionString if you do not specify a value for max pool size , max pool size= 100, and when the number of people connecting to the database is 101, The above error occurs when you wait for Sqlconnection.connectiontimeout to set the time (default is 15 seconds), or if there is no connection available.

When we set it to:

"Server= (local); Integrated Security=sspi; Database=northwind; Max Pool size=512; Min Pool size=5 ". When the number of simultaneous connections to a database is 513, the error will occur after waiting for the sqlconnection.connectiontimeout to set the time (15 seconds by default) or if there is no available connection.

- Connection Pool What is it?Whenever the program needs to read and write to the database. Connection.Open () uses ConnectionString to connect to the database, the database establishes a connection to the program and remains open, and the program can then use T-SQL statements to query/update the database. When executed to Connection.close (), the database closes the current connection. Well, everything seems to be so methodical. But if my program needs to open and close the connection in an indefinite time, such as ASP. NET or Web Service, for example, when HTTP request is sent to the server, we need to open connection and then use select* from Table Returns a datatable/dataset to the client/browser and then closes the current connection. That every time open/close Connection so frequent operation for the entire system is undoubtedly a waste. The ADO team gives a better solution. Save the previous connection, and the next time you need to open the connection, the previous connection will be handed over to the other connection. This is connection Pool.


- Connection Pool How does that work? First, when a program executes Connection.Open (), ADO needs to determine whether this connection supports connection Pool (Pooling default is True), and if it is specified as false, ADO creates a connection to the database (in order to avoid confusion, all connections in the database are described using a "connection") and then returned to the program.

If specified as true,ado.net, a connection pool is created based on ConnectString and then populated with connection in the connection Pool (all connections in. NET programs, using " Connection "description). How many connection are populated is determined by the Min Pool Size (default = 0) property. For example, if you specify 5, ADO opens 5 connections to the SQL database at a time, then 4 Connection are saved in Connection pool, and 1 Connection are returned to the program.

When the program executes to Connection.close (). If pooling is true,ado.net, the current connection is placed in the connection pool and the connection to the database is maintained.

The connection Lifetime (default 0) attribute is also judged, and 0 represents infinity if the connection exists longer than connection Lifetime, Connection closes the connection to the database at the same time, instead of re-saving it to the connection pool.

(This setting is primarily used in clustered SQL databases for load balancing purposes.) If pooling is specified as false, the connection to the database is broken directly.

Then the next time Connection.Open () executes, ADO. NET will determine whether the new connectionstring is consistent with the connectionstring of the connection previously saved in the connection pool.

(ADO will turn ConnectionString into a binary stream, so that is, the new ConnectionString is saved in connection The connectionstring of the connection in the pool must be exactly the same, even if you add a space, or modify the connection The order of some properties in a string will let ADO think of this as a new connection and create a new connection from the newly created one. So if you use the Userid,password authentication method, modifying the password will also result in a connection, if you are using SQL Integrated authentication, you need to save two connections using the same one).

Then ADO needs to determine if there are any available connection (not occupied by other programs) in the current connection Pool, and if not, ADO needs to determine the max pool of connectionstring settings Size (default is 100), if all connection in the connection pool do not reach Max Pool size,ado.net, the database is connected again, a connection is created, and connection is returned to the program.

If you have reached maxpoolsize,ado.net, you will not create any new connections again, but instead wait for the connection in the connection pool to be released by other programs. This wait time is limited by sqlconnection.connectiontimeout (the default is 15 seconds), which means that if the time exceeds 15 seconds, SqlConnection throws a timeout error (so sometimes if the Sqlconnection.open () method throws a timeout error, one possible reason is not to close the previous connnection in time, while connection The pool number reached maxpoolsize. ）

If a connection is available, the connection removed from the connection Pool is not returned directly to the program, and ADO needs to check the Connectionreset properties of the ConnectionString ( The default is True) if a reset is required for connection. This is because the connection that were previously returned from the program may have been modified, such as using Sqlconnection.changedatabase method to modify the current connection. The returned connection may not have been connected to the initial catalog database specified by the current connection string. So you need to reset the current connection. However, due to all the additional checks, the overhead of the system is increased by the ADO Connection Pool.

The timeout period has expired, but the connection has not been obtained from the pool. This can happen because all pooled connections are in use and the maximum pool size is reached.