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.

Source: Internet
Author: User

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.

Solution
1. Close the unclosed connection in the code.
2. Expand the sharing pool as follows:
The solution is to modify the connection life of the Connection Pool, because the default value is 60 seconds, that is, the time when the connection can be saved in the pool after the application is released.
The procedure is as follows:
For ODBC, You can manually change it in ODBC data source administrator. The program is located in "programs"-> \ "Adminstrative Tools \" in the "Start" menu (Chinese Name: management tools), or open ODBC data source administrator from \ "Control Panel \" (Chinese name: Control Panel ), select the connection pool tab, double-click "SQL Server" in the driver, and select "pool connetions ".
To this drive "(connect to the driver using the pool), change the following time to 60 seconds, for example, 120.
If it is a sqlconnection shared connection, specify a value for the connectionstring attribute connection Lifetime Value Name. This value is 0 by default, indicating that the connection will never be automatically removed from the pool. If a different value is specified, the value will be compared with the connection creation time and current time once the connection is returned to the pool. If the lifetime exceeds the value of connection lifetime, the connection will be removed from the pool.

The cause is that the connection to the database is not closed, and it takes time for the system to automatically recycle the database. the solution is to change the size of the connection pool. add the following code after the connection string: Enlist = true; pooling = true; Max pool size = 300; min pool size = 0; connection lifetime = 300; user id = sa; packet size = 1000
The packet size parameter is an integer between 512 and 32767.

Datareader is exclusively connected, that is, your program may be designed incorrectly. For example, if the maximum connection is set to 100 and 100 users use datareader to read the database content at the same time, the above error occurs when the connection pool is no longer connected when 101st users read the data. Datareader is exclusively connected, and each datareader occupies a connection. Of course, this happens occasionally, so it takes a long time, because only concurrent operations that exceed the maximum number of connections in the connection pool will occur. In addition, you can only temporarily reduce the number of concurrent connections. If you increase the number of concurrent connections to 200, what if there are 201 concurrent operations? You said that you can't use the close () method of the connection object, because the close () method only closes the connection, but the connection is not released and is still occupied by this object, to release a connection, you must use the dispose () method of the connection to explicitly release the connection. Otherwise, the connection occupied by this object can be released only when garbage collection is performed. In this case, the error "time-out has reached" will certainly occur.

Solution:
1. modify several key pages or frequently accessed database access operations. Use dataadapter and dataset to obtain database data. Do not use datareader.
2. Use the data cache on the page for accessing the database. If the data on the page is not updated frequently (updated several minutes), the cache object can be used without accessing the database, this greatly reduces the number of connections.
3. modify the code and call dispose () after close.
4. We recommend that you modify Database Operations to create your own database operation proxy class, inherit from the system. idisposable interface, and forcibly release resources. In this way, there will be no insufficient connections.

Solution (*): Web. in config: Add Max pool size = 512 to the database connection; server = Local; uid =; Pwd =; database = 2004; Max pool size = 512; \ "> once and for all.

 

Certificate -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

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.

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.

 

 

- What is the connection pool?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.

-How does the connection pool 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, if you use sqlconnection. changedatabase method to modify the current connection, the returned connection may not be the current connection.
The initial Catalog Database specified by 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.

 

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.