Database connection pooling issues Max pool Size

Excerpt from: http://blog.csdn.net/chensirbbk/article/details/6225268

Timeout expired time-out has arrived. Max Pool Size error reached

Reference database link string: [Code=sql]<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; "/> [/code] View application pool consumption: [Code=sql]select * from sysprocesses where dbid= db_id (' database name ') [/code]max pool] Size: If not set, the default is 100, 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 unspecified Max Pool Size's Value, the Max Pool Size=100, when the number of simultaneous connections to a database is 101, the Sqlconnection.connectiontimeout is waiting for the time set (default is 15 seconds) and 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* The 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.

A better solution is given by the ADO team. 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 it work?
First when a program executes Connection.Open (), ADO needs to determine whether this connection supports connection Pool (Pooling defaults to True), and if False is specified, 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 property of ConnectionString ( The default is True) if a reset is required for connection. This is because the previously returned connection from the program may have been modified, for example, by using Sqlconnection.changedatabase method to modify the current connection, the con returned at this timeNection 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.

Database connection pooling issues Max pool Size