Ado. NET Data connection Pool __.net

21st century is the most expensive. Database connection. Database connection is one of the most precious resources in the whole system for the application system with database as the cornerstone of data storage. Database connection pooling is the most important measure to make more efficient use of database connections. It is critical for the performance of a large application system, especially Web applications. Ado.net Data Provider (hereinafter referred to as data Provider) will help us manage the connection pool, so it is said that using a connection pool is as easy as a child's pool. But it's not that there's a data provider programmer that doesn't worry about everything, and incorrectly using a connection pool can cause your application to drown in the pool. The author hopes that through this paper readers can understand the importance of the connection pool and can correctly configure the parameters of the connection pool according to actual situation, understand the actual application of the connection leakage, "dead connection" and other anomalies and coping methods, so that the application easy to swim connection pool. This article mainly introduces the connection pool of Ado.net 1.1.

1, what is the connection pool

Connection pooling is a mechanism provided by data provider that allows applications to use a connection that is kept in a connection pool and avoids the complete process of establishing/closing connections each time. To understand the connection pool, first understand the Sqlconnection.open (), Sqlconnection.close () in the program and turn on/off a "physical connection" relationship.

The complete process by which Data provider establishes a connection when it receives a connection request is to first connect the pool to establish a new connection (that is, "logical connection"), and then establish the "physical connection" for that logical connection. Establishing a "logical connection" must be accompanied by a "physical connection". The complete process for Data provider to close a connection is to turn off the physical connection corresponding to the logical connection and then destroy the logical connection. Destroying a logical connection must be accompanied by closing the physical connection. Sqlconnection.open () is a request to the data provider a connection, the data provider does not necessarily need to complete the completion of the connection process, may only need to remove from the connection Pool A usable connection can Sqlconnection.close () is a request to close a connection, the Data provider does not necessarily need to complete the complete process of shutting down the connection, may only need to release the connection back to the connection pool.

Here is an example to illustrate. This example uses the console application. We use the operating system Performance Monitor to compare the use of connection pooling or not, the number of "physical connections" in the database is different. Because the Performance Monitor collects data at least once per second, the open and close connections in the code sleep one second for ease of observation.

SqlConnection con = new SqlConnection ("server =.; Database = Northwind;pooling = False;trusted_connection = true "); for (int i = 0;i < 10;i++) {try {con. Open (); System.Threading.Thread.Sleep (1000); catch (Exception e) {Console.WriteLine (e.message);} finally {con. Close (); System.Threading.Thread.Sleep (1000); } }

First, do not use the connection pool for testing. Pooing = False in the above program indicates that no connection pool is used, the program uses the same connection string open & Close for 10 connections, and uses performance counters to observe the number of "physical connections" for SQL Server. From the jagged figure below you can see that every time con is executed. Open (), the number of "physical connections" for SQL Server increases by one, and every time con is executed. Close (), the number of "physical connections" for SQL Server is reduced by one. Because the connection pool is not used, data provider needs to destroy both the "logical connection" and the "physical connection" every time the close connection is made, and the data provider need to establish a "logical connection" and a "physical connection" each time the open connection is made.

Figure 1

The following enables connection pooling to be tested again. Change the pooling parameter of the connection string to True, plus console.read () after the For loop.

As you can see from the figure below, the number of physical connections for SQL Server remains at 1 from the first open to the end of Console.read (), and the number of "physical connections" to SQL Server becomes 0 after the process of shutting down the console application. Because the connection pool is used, data provider simply releases the logical connection back to the connection pool each time close is connected, and the corresponding physical connection remains open. Each time the open connection, the Data provider only need to remove a "logical connection" from the connection pool, so that it can use its corresponding "physical connection" without the need to establish a new "physical connection", the line diagram thus.

Figure 2

Use performance counters under Ado.net 1.1 to observe the connection pool related counters need to be aware of two bugs.

(1) When the application process is closed, the counter "Sqlclient:current # Pooled Connections" and "Sqlclient:current # Connection Pools" will not be reduced to 0, So the value of each rerun of the application performance counter is cumulative on the basis of the last value. This is the error display of the counter, and in fact the connection pool and pooled connection are reduced to 0 after the application is closed. Because the performance monitor is turned off after the application is closed, restarting the application and then reopening Performance Monitor will show "Sqlclient:current # Pooled Connections" and "Sqlclient:current # Connection Pools "is starting to rise again from 0.

(2) With breakpoint debugging, the connection string is "server =.; Database = Northwind;pooling = True;trusted_connection = True "connnection The first time Open" Sqlclient:current # pooled Connect Ions "goes from 0 to 2. However, depending on the meaning of the connection string parameters, only open a connection, "Sqlclient:current # Pooled Connections" should change from 0 to 1 (Figure 2 is the curve without breakpoint debugging). This is not a counter display error, but a bug in the Ado.ent 1.1 itself, because "User connections" also changes from 0 to 2 with "Sqlclient:current # pooled Connections".

Why you need a connection pool.

Completing the complete process of establishing/closing a connection is a process that consumes a lot of resources and time. Imagine a asp.net system, which contains a large number of access to the database of the code, the system has a large number of users at the same time using the system, if the program every time open/close a connection to the data provider complete the establishment/shutdown of a connection complete process, Such system performance is certainly unacceptable.

The Data provider provides connection pooling and enables "physical connections" to be reused through connection pooling to avoid the frequent establishment and shutdown of physical connections, thus greatly improving the performance of the application system. Figure 1 depicts a different client app using a connection pool to access the database, and the data provider is responsible for establishing and managing one or more connection pools, one or more connections in each connection pool, and the connection in the pool is "logical connection". There are n connections in the connection pool that indicates that there are N "physical connections" between the connection pool and the database. Add a connection, the connection pool and the database "physical connection" to add one, reduce a connection, the connection pool and the database "physical connection" is reduced by one.

Figure 3

The Data provider manages the connection pool of the process for each process, and one process can have one or more connection pools. The Data provider is based on what determines whether to create a new connection pool or to use an existing connection pool. Depending on the database connection string. How to be the same connection string. The character of the connection string is exactly the same. The answer is yes but it is not.

I have seen some articles that do not necessarily two strings of characters are identical to be the same connection pool, such as "Server =.; Database = Northwind;user = Sa;password = SQL Server "and" server =.; Database = Northwind; Password = SQL Server; user = sa "is the same connection string. However, the author has tested that Data provider two connection pools for the above two connection strings, proving that they are not the same connection strings. In fact, the author thinks, for "two connection string parameters are the same but the order is different", "two connection string only one space" is the same connection string and other issues do not need to pay attention, because it is not difficult to ensure that the characters of the two connection pool are the same.

If you need the same connection string, first you guarantee that each of the two connection strings will be the same, but this does not guarantee that the data provider only set up a connection pool for you. Because if you use Windows authentication, then even use the same connection string "Server =.; Database = Northwind;trusted_connection = True "It is also possible to establish multiple connection pools. Windows authentication means that the database user who connects to the database is the current user running the Open database Connection open (), and if the user running the code is not fixed, then even a connection string with the same character each time produces multiple connection pools.

After the connection pool is established, it will not be destroyed until the process it belongs to is finished.

3. How many connections are there in a connection pool?

Having understood how to differentiate between different connection pools, let's look at how many connections there are in a connection pool. The number of connections in a connection pool is not a static number, and it changes with different states of the connection pool. This relates to how many connections are connected when the connection pool is established, when the connection will be reduced, when it will increase, and how many connections the upper limit is.

First take a look at the connection string parameters that can affect the number of connections in the connection pool, as shown in the following table.

Parameters	Default value	Describe
Min Pool Size	0	Once the connection pool is established, the minimum number of connections in the pool.
Max Pool Size	100	Maximum number of connections in the connection pool.
Connection Lifetime	0	The connection is destroyed whenever a connection is exhausted and then released back to the connection pool, and if the current time subtracts the value of the time established by the connection is greater than the value set by this parameter (seconds). 0 means lifetime has no upper limit.
Connection Timeout	15	The time that the connection request waits before it stops the request and outputs an error. When the number of connections to the pool reaches max pool size and is all occupied, the connection request waits for the "occupied" connection to be released back into the connection pool, throwing InvalidOperationException if the wait exceeds the specified time and the connection is released.

3. 1 Increase Connection

    Once the connection pool is established, a connection of the specified number of min pool size is established immediately. If only one connection is occupied, then the other connection (if min Pool size is greater than 1) is the "available" connection in the pool. If a process has a connection request and the connection string for the requested connection is the same as the connection string for one of the process's connection pools (if all connection pools in the process do not match the requested connection, a new connection pool needs to be established). Then, if there is a "available" connection in the connection pool, remove a "usable" from the connection pool. Connection is used, and a new connection is established if there is no "available" connection. Once the program runs the connected close or Dispose method, the "occupied" connection is freed back to the connection pool to become a "available" connection. You need to differentiate between the number of connections in the connection pool and the ' available ' number of connections. "Number of connections" refers to the number of connections in the connection pool that include the "occupied" Connection and the "available" connection.

    If Max Pool size is reached and all connections are occupied, new connection requests need to wait. If an occupied connection is released back to the connection pool, the request is received, and if the request waits longer than the connection timeout, the program throws the InvalidOperationException.

3. 2 Decrease Connection

There will be fewer connections in the connection pool in both cases.

    (1) The connection is destroyed whenever a connection is exhausted and then released back to the connection pool, if the current time subtracts the value of the time established by the connection greater than the value set by connection lifetime (seconds). Connection lifetime is used in a clustered database environment. The middle tier of an application system, for example, accesses a clustered database of 3 servers that, after running for a while, discovers that the database is overloaded and needs to add a 4th database server. If you do not set connection Lifetime, you will find that the new server has not been connected for a long time and the load of the original 3 servers has not decreased at all. This is because the connection between the middle tier is never destroyed and the likelihood of establishing a new connection is small (unless the concurrent maximum number of concurrent accesses to the database is exceeded before the increase occurs).

Note:Connection lifetime is easy to misunderstand. Do not assume that connection lifetime determines the lifetime of a connection. Because only the moment the connection is released back to the connection pool (after the close connection), the connection Lifetime value is checked to determine whether the connection is destroyed and the connection is not checked for connection Lifetime when it is idle or in use. This means that, in most cases, the connection is larger than the connection lifetime from the time it was established to the destruction. In addition, if the min Pool size is n (n > 0), then there are n connections in the connection pool that are not affected by connection lifetime. The N connections will remain in the pool until the connection pool is destroyed.

    (2) When a connection is found that corresponds to a "physical connection" disconnect (this connection is known as "Dead Connection"), such as the database has been shutdown, network outages, SQL Server connection process was killed, Oracle's connection session was killed, the connection was destroyed. A "dead connection" occurs when it is not immediately discovered until the connection is consumed to access the database.

Note:If the open () method is executed when data provider only need to remove an existing connection from the connection pool, then open () does not have access to the database, so the "dead connection" is not yet discovered.

    The following is an example to explain in detail the change in a connection pool from the set up to the process end connection number.

String connectionString = "Server =.; Database = Northwind;user = sa; Password = sqlserver;min Pool size = 2;max Pool size = 5; Connection lifetime = 20;connection Timeout = 10 "; sqlconnection[] connections = new SQLCONNECTION[7]; for (int i = 0;i < connections. length;i++) Connections[i] = new SqlConnection (connectionString); ... Open connection[0],8 seconds after open connection[1] ... Close connection[0],10 seconds after 8 seconds open connection[0] ... 5 seconds after Open connection[2], [3], [4], opens a Console.WriteLine every two seconds ("Now the Max Pool Size is reached and we try to open connection[5 ]./r/n "); for (int i = 0;i < 2;i++) {try {connections[5]. Open ();} catch (InvalidOperationException e) {if (i = = 1) return; Console.WriteLine ("Can ' t open connection[5]./r/n" + e.message); CONNECTIONS[4]. Close (); Console.WriteLine ("/r/ntry to open connection[5] again."); Continue } Console.WriteLine ("Connection[5] is open."); foreach (SqlConnection con in connections) {if (con. state = = ConnectionState.Open) {con. Close (); Console.WriteLine ("A connectiOn are released back to the pool. "); System.Threading.Thread.Sleep (5000); } }

Using Performance Monitor observations, you get the results shown in Figure 4. We observe the "Sqlclient:current # Connection Pools", "Sqlclient:current # Pooled Connections" of. NET CLR data, and SQL Server:general S The Tatistic user connections counter.

Figure 4

Because the min pool Size = 2, the Open connection[0] has two connections in the connection pool. After the open connection[1], close connection[0, open connection[0], the number of connection pool connections remains at 2 because the number of concurrent open connections is not more than 2. Then, successively open connection[2], [3], [4], because there is no "available" connection in the connection pool each time the connection is requested, the number of connection connections per request increases by 1, and climbs to Max Pool Size (5). At this time connection[0], [1] lifetimes have exceeded connection Lifetime, but as they have not been close, they will continue to survive. Then try to request the connection again, and the first attempt failed because Max Pool size was reached and all the connections in the pool were occupied. Close Connection[4] Before a second attempt, so that a connection is released back to the connection pool and the second attempt succeeds. The last close all open connection, every 5 seconds close one, all connection are close when their lifetime is greater than connection Lifetime, but due to the min Pool Size = 2, so only 3 connection were destroyed.

Two additional points are highlighted:

(1) can be seen to increase/decrease the connection of a connection pool, the User connections (ie "Physical connection") with the increase/decrease of one. (for easy observation, open a user connection with SQL Query Analyzer first)

(2) Because of the use of the same connection string, there is only one connection pool from beginning to end.

4. Connection leakage

As mentioned earlier, when a connection is opened, the close or Dispose method is executed before it is released back to the connection pool. If a connection has left its code valid range, but has not been close or Dispose, the connection is compromised. A compromised connection is that a connection is no longer in use in the code, but the connection has not been released back to the connection pool. In the following code, each execution of method () leaks a connection and throws a InvalidOperationException on the 11th execution because the maximum number of connections is reached and all connections are already occupied.

private void Method () {String constring = "Server =.; Database = Northwind;user = sa; Password = Sqlserver;max Pool size = 10 "; SqlConnection con = new SqlConnection (constring); Con. Open (); }

If there is code in an application system that leaks the connection, the system runs for a period of time and the connection leaks out. Even setting max Pool size is not going to solve the problem, because there are too many database connections that are intolerable, and these are "physical connections" that cannot be used.

To avoid a connection leak, be aware of the following points:

(1) Close DataReader will not close the associated connection unless the commandbehavior.closeconnection is used as a ExecuteReader parameter. In a multi-tiered system, if the middle tier returns DataReader to the presentation layer, You must use CommandBehavior.CloseConnection as the ExecuteReader parameter so that when the presentation layer executes the DataReader Close method, the close connection is made, or the presentation layer is powerless to help you.

(2) When the DataAdapter fill and update method is executed, if the connection is not turned on, then DataAdapter automatically turns on the connection, closes the connection automatically after the operation is done, but if the connection is turned on, the DataAdapter does not close the connection after the operation has been done , you need to be responsible for closing the connection yourself.

5, processing "Dead connection"

A "available" connection must be able to access the database. Not necessarily.

In the previous section "Reducing Connections", the connection pool produces a "dead connection" when the database is shutdown, the network is interrupted, the database connection process/session is killed. "Dead connection" refers to a connection in the pool of the corresponding "physical connection" has been disconnected, but clientapp execution of the Open method can obtain the connection from the connection pool until the database operation data provider found that the connection is "dead connection." Note the distinction between "dead connection" and a leaked connection.

"Dead Connect" is a "logical connection", a "available" connection, however, the "physical connection" for the "logical connection" does not already exist; The compromised connection means that the "physical connection" exists and the corresponding "logical connection" is not actually occupied but is identified as "occupied" and that the "logical connection" cannot be used.

Data provider destroys the connection and throws SqlException but does not automatically attempt to use a different connection, even in Ado.net 2.0, when a "dead connection" is found. Catch the exception and then prompt the user to do it again is not the best way to handle it. No matter why Microsoft does not help us try other connections, we can only accept the reality of our own solution.

In the following example, the helper ExecuteReader puts the connection to "invalid" after the SqlException catch thrown by the data provider, and then tries to use a different connection. If the number of attempts to reach a predetermined value is not successful before throwing SqlException.

public class Helper {private static int timestry = 0,maxtry = 5; public static SqlDataReader ExecuteReader (String constr , CommandType EType, string commandtext) {SqlConnection cn = null; SqlDataReader dr = null; SqlCommand cmd = null; try {cn = new SqlConnection (constr); cmd = new SqlCommand (COMMANDTEXT,CN); cmd.commandtype = EType; cn. Open (); Dr = cmd. ExecuteReader (commandbehavior.closeconnection); The catch (SqlException e) {if (Dr!= null) Dr. Close (); cn. Close (); System.Threading.Thread.Sleep (2000); if (Timestry < maxtry) {Dr = ExecuteReader (Constr,etype,commandtext); timestry++; else throw e; Return Dr; } string constring = "Server =.; Database = Northwind; user = Sa;max Pool size = 1;password = SQL Server; Application Name = Deadconnectionexample "; SqlDataReader reader = Helper.executereader (Constring,commandtype.text, "select * from Orders"); Reader. Close (); System.Threading.Thread.Sleep (15000); SqlConnection con = new SqlConnection ("server =.; Database = Master; user = SA;password = Sqlserver;pooling = false "); Con. Open (); SqlCommand cmd = new SqlCommand ("Select SPID from master.dbo.sysprocesses WHERE program_name = ' Deadconnectionexample '", C ON); string spid = cmd. ExecuteScalar (). ToString (); cmd = new SqlCommand ("Kill" + Spid,con); Cmd. ExecuteNonQuery (); Con. Close (); System.Threading.Thread.Sleep (5000); Reader = Helper.executereader (Constring,commandtype.text, "select * from Orders"); Reader. Close ();

In the main method, the connection pool is established and a connection is established after the first call to Helper.executereader, and then we simulate the connection process and then call Helper.executereader. To kill for the analog connection process, first in the master.dbo.sysprocesses query program_name the SPID for deadconnectionexample (the application Name of the connection string), Then kill the connection process. A "dead connection" is encountered when calling Helper.executereader again (must be encountered because there is only one connection in the connection pool). Use Performance Monitor to observe the situation in the connection pool (first open SQL Quary Analyzer to get a user connection for easy observation) get Figure 5.

The number of connection pools in Figure 5 remains at 1 because connection pooling is not used for the connection string used by the kill connection process. The user connections (blue line) dropped 1 immediately after the connection process was killed, while the number of connections (yellow Line) of the connection pool did not decrease as 1, resulting in a "dead connection". Then, from the connection pool out of the connection to access the database to throw SqlException, the number of connections dropped 1, because this time the data provider destroy the "dead connection." Next, try to use a different connection, because the connection in the pool is 0, so a new connection needs to be established, and the number of connections and user connections increases by 1 at the same time. In order to facilitate observation, the other connection on the front line sleep for two seconds.

Of course, if a "dead connection" is caused by a network outage and the database is shutdown, then the helper can only throw sqlexception at the end.

Note: There is no need to use connection pooling for the connection string used by the query master.dbo.sysprocesses.

Figure 5

6, ado.net 2.0 Performance Counters
The two bugs mentioned earlier when using performance counters do not appear in Ado.net 2.0. In Ado.net 2.0, the performance object of ". NET CLR data" was discarded in 1.1, with the new performance object ". NET data Provider for Oracle" and ". NET data Provider for SQL Server". These two performance objects have 14 counters, which can see more and deeper connection pool information than Ado.net 1.1. The "occupied" connections, "available" connections, "logical connections" and "physical connections" in the Ado.net 2.0 performance counters are called active Connection, free Connection, Soft Connection, Hard, respectively. Connection.

numberoffreeconnections, Numberofactiveconnections, Softdisconnectspersecond and Softconnectspersecond defaults are not turned on by the Performance Monitor, and to observe the values of these counters you need to add the following configuration to the program's configuration file:

<system.diagnostics> <switches> <add name= "Connectionpoolperformancecounterdetail" value= "4"/> </switches> </system.diagnostics>

Numberofactiveconnectionpoolgroups counter. As mentioned earlier, if the connection string uses Windows authentication, then different Windows users have different connection pools, ado.net 2.0 uses Numberofactiveconnectionpoolgroups to group the different connection pools that are produced by using the same connection string (the same character) used in Windows authentication.

Numberofactiveconnections, Numberoffreeconnections counter. The counters in Ado.net 1.1 do not provide how many of the connections in a connection pool are "occupied" and how many are "available." Numberofactiveconnections and numberoffreeconnections fill this void. These two counters more "vividly" describe the connection changes in the connection pool. Figure 6 is a more "vivid" curve than Ado.net 1.1, which is a connection that has been open/close 4 times.

Figure 6

    7, summarizing  

     Understand that the connection pooling does not work as a connection pool, To fully tap the connection pool to improve the performance of the application system, in addition to avoid leakage connection need to pay attention to two points, please refer to the recommendations:  

     (1) To ensure that each access to the database using the same connection string, the connection string do not use Windows authentication. &NBSP

     (2) Opens the connection when it is not open, and closes the connection immediately after the connection is used. Because premature occupancy and late release of the connection means increasing the unnecessary load on the connection pool (more connections need to be established and the connection request waiting longer). &NBSP

     (3) sets the appropriate min pool size and max pool size according to the actual load of the application system. To prevent a connection request from timing out, it is necessary to set the max pool size larger If the application system has a maximum concurrent access to the database than the default 100 of Max pool size; If the database for the application system is not the maximum number of accesses is N, then the min Pool size is not greater than N. &NBSP

     (4) If the application system is not using the cluster database, set the connection lifetime to 0. It is not necessary to destroy the connection in the context of a single database server, because it needs to be established after the destruction. &NBSP

     connection pooling plays a vital role in improving the performance of the application system, but requires that the connection pool has its scope and is suitable for application systems that require frequent access to the database. A database access application that is low frequency (for example, only a few times a day) is not necessary because a "physical connection" that retains a low frequency is not as good as using it once.