Some personal insights from C # connection pool (Connection pool)

Original title: Some personal insights on the ado.net connection pool (Connection pool)
Here is the original:
Establishing a pool connection can significantly improve the performance and scalability of your application. The SQL Server. NET Framework Data Provider automatically provides connection pooling (MSDN) for ado.net client applications.
Opening A database connection is a resource intensive and time consuming operation. Connection pooling increases the performance of web/windows applications by reusing active database connections instead of Creating a new connection with every request. Connection Pool Manager maintains a pool of open database connections. When a new connection requests come into, the pool manager checks if the pool contains any unused connections and returns on e if available. If all connections currently into the pool are busy and the maximum pool size has not been reached, the new connection is CR Eated and added to the pool. When the pool reaches it maximum size all new connection requests are being queued up until a connection in the pool Beco MES available or the connection attempt times out.
Connection pooling behavior is controlled by the Connection string parameters. Please look into MSDN documents in the reference link if your want to know further information.

There are some basic introductions to the connection pool knowledge, the following is the focus and personal opinion. Since there is no reference or corroboration of this document, please think and discuss it carefully (I do not want to mislead you).
The following are discussed in 2 different situations.
The 1,client-side Windows Form application accesses the background database directly through Ado.net.

View larger image
SQL Server Directconnection

I think in this case, there is a connection pool between each client and the database. Verified by setting the max pool size and min pool Size property of the connectiongstring.
If max pool size = 5, Min pool size = 3, the following results can be detected via SQL Server Sp_who2:
There are 3 connection between Windows Form application:application and SQL Server that starts 1 client side.
There are 6 connection between Windows Form application:application and SQL Server that starts 2 client side.
There are 9 connection between Windows Form application:application and SQL Server that starts 3 client side.

Thus, there is a connection pool between each client and SQL Server, otherwise 9 connection has exceeded the Max Pool size setting.

The 2,client end of the application does not directly access the background database through ado.net, but rather through IIS server to deal with the background database server.
There are at least 2 types of situations:
(1) Client accesses the background database Server by accessing the data access class in the Web Application,web application deployed in IIS through IE.
(2) client-side Windows Form application accesses Remote Object,remote Object deployed in IIS to further access the background database Server.

View larger image
SQL Server Indirectconnection
The same tests are done below: Verify by setting the max pool size and min pool size attribute for connectiongstring.
If max pool size = 5, Min pool size = 3, the following results can be detected via SQL Server Sp_who2:
There are 3 connection between the 1 client-side Web Form application:application and SQL Server.
There are 3 connection between the 2 client-side Web Form application:application and SQL Server.
There are 3 connection between the 3 client-side Web Form application:application and SQL Server.
Calls remote Object hosted by IIS with the result similar. Just before the client side was started, it was found that there was already a connection between the remote object and the database server.
Thus, for the same application, there is only one connection pool between IIS server and the database server, which is not related to the client side.
3, Connection Pool section
Based on the test results above, it is clear that the second scenario is more conducive to reducing the number of unwanted connections and improving the performance of the database server. For the. Net Remoting technology and its performance issues, you can refer to the following reference connections, which are not discussed here.
In addition, this article is personal about the connection pool of some insights, if the point of view is not correct, I hope not to mislead you. You are welcome to express your views here. Agree to say yes, please give your opinion. Thank you.
Reference Links:
(1) MSDN, Ms-help://ms. Vscc.2003/ms. Msdnqtr.2003feb.2052/cpguide/html/cpconconnectionpoolingforsqlservernetdataprovider.htm
(2) Microsoft. NET Remoting: Technology Overview, Http://www.microsoft.com/china/MSDN/library/NetFramework/default.mspx
(3) Performance comparison:. NET Remoting and ASP.net Web services, http://www.microsoft.com/china/msdn/archives/library/dnbda/html/ Bdadotnetarch14.asp
======================== the original text end, open can refer to another article: NET Connection Pool Lifeguard
Some additional points of view of the original text:
Creation and acquisition of connections:
Q: If my maximum connection pool is 5, then in my program, every time I access, I will use the new one, and then open, use later, then close, then at each new time, will not create the connection.
A: In each new, if there are idle connections in the connection pool, no new connections are created, or they are created.
If the maximum number of connections is exceeded, a request for new is issued, but the request is queued until there is an idle connection or a connection request timeout in the connection pool.
Efficiency issues with connections:
Q: If you can control the connection, how to control, very simple, now every time I use database access, will be new and close, I am worried that this efficiency is not very low ah, want to improve efficiency in connection, how to operate it.
A: When using the connection pool, new does not necessarily create a connection, and if there is an idle connection in the connection pool, take it directly.
Close also simply puts the connection into the connection pool for subsequent requests. So there is no efficiency problem.
Time to live the connection:
When a connection is returned to the pool, its creation time and current time are compared, and if the interval exceeds the value specified by Connection Lifetime (in seconds), the connection is destroyed. It can be used in a clustered configuration to force load balancing between the running server and the server that is just online.
A value of 0 (0) will cause the pool connection to have the maximum time-out period. The default value is 0
Comparison of Sqlconnection.close and. Dispose:
The same places as Sqlconnection.close and. Dispose are:
Dispose must have called close, so close inside has something to do, and Dispose includes
Dispose also made other resources released, so that when the GC first collection, save the process of Dispose, speed up the recovery of memory resources.
If no call to DISPOSE,GC will be done in the first recycle first.
If the program frequently does the new SqlConnection () and then quickly close and no longer use this connection (for example, some Com+/asp.net/web service), and do not Dispose, So as the program is invoked many times, there will be a lot of system resources (usually memory) that are allocated but not released as soon as possible, and the number of GC forced recoveries is correspondingly increased. The overall efficiency of the system will be reduced (GC recovery is all. NET programs are paused, such as GC recovery can not continue after running, resulting in other programs are also affected). So for this situation, do dispose well.
But if other circumstances, such as WinForms's client, then are not called.
Recently found that this site sometimes can not connect to the server phenomenon, through the sp_who view of the database connection, found that the Web site applications occupy a relatively large number of connections, when the number of connections reached a certain number, the site will not be connected to the database error. So be sure that the reason for the site error is that the database connection was not released in time--or it opened up too many connections. The database access logic of the Web site is not centralized in a database access class, but is built when used and closed. So is this the case with the first case mentioned above? The first of these should be for WinForm applications. But this site's program does not want to say the second case, the data access is concentrated in a data access class. This question needs to be validated by practice.
Source: http://blogs.msdn.com/angelsb/archive/2004/08/25/220333.aspx

Author: angelsb


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

Timeout expired anomaly is a tricky anomaly that almost everyone must have met. Sometimes it's a real grind to it, and it can't be taken. ANGELSB This article is very good, hope to be useful to everybody. I also saw him speak very well, just translated, the level is limited, please advise.


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

Hey! In another process, it appears again the connection pool is full of problems, this is one of the most headache but also the most common connection pool problem. The reason is that in the development process rarely encountered this headache, but in the deployment of the app to the client, but always inadvertently ran out. I think, I should take some time to make a complete summary of the problem.

What is the nature of what happens?

Let's take a serious look at two situations where this anomaly might occur

1 You have used more than the maximum number of connection pool connections (the default maximum number of connections is)

In most applications, this is a rare occurrence. After all, when you use connection pooling, 100 parallel connections are a very large number. According to my experience, the most likely cause of this anomaly should be the opening of 100 connections under a thoroughbred. Program code

sqlconnection[] Connectionarray = new SQLCONNECTION[101];
for (int i = 0; I <= i++)
{
Connectionarray[i] = new SqlConnection ("server=.//sqlexpress; integrated security=sspi;connection timeout=5");
Connectionarray[i]. Open ();
}

Solution: If you are sure you will be using more than 100 parallel connections (on the same connection string), you can increase the maximum number of connections.

2) connection leakage

I personally think that the connection leak definition is that you have opened a connection but you have not executed close () or Dispose () in your code. This range is not just that you forgot to use Dispose () or closing () to turn off the period after the connection is connected, It also includes situations where you have written close () after the relevant connection and have not worked at all. Let's take a look at the following code: program code

Using System;
Using System.Data;
Using System.Data.SqlClient;

public class Repro
{
public static int Main (string[] args)
{
Repro repro = new repro ();
for (int i = 0; I <= 5000; i++)
{
try{Console.Write (i+ ""); Repro. Leakconnections (); }
catch (SqlException) {}
}

return 1;
}
public void Leakconnections ()
{
SqlConnection sqlconnection1 = new SqlConnection ("server=.//sqlexpress; integrated security=sspi;connection timeout=5 ");
SqlConnection1. Open ();
SqlCommand sqlcommand1 = sqlconnection1. CreateCommand ();
Sqlcommand1.commandtext = "RAISERROR (' This is a fake exception ', 17,1)";
SqlCommand1.  ExecuteNonQuery (); This throws a SqlException every time it is called.
SqlConnection1. Close (); We are calling connection close, and we are still leaking-connections (do you have above comment for explanation)
}
}

This is a classic example of copying this code into Visual Studio, setting a breakpoint in Sqlconnection1.close (), and you can see that he never executes at compile time, Because Executenonqurery throws an exception. Then you should be able to see the horrible timeout exception. On my machine, about 170 connections are turned on. I wanted to throw an exception at every call to reduce the chance of a connection timeout, but when you consider deploying it to a asp.net application, any leak will put you in trouble.

3 You are using the SQL debugging in Visual Studio to open or close the connected

This is a well-known bug, you can look at the following link
http://support.microsoft.com/default.aspx?scid=kb;en-us;830118



how to determine whether a connection leak is in the ado.net2.0

In 1.0 or 1.1, it is difficult to determine whether a connection leak can be achieved at most by performance metrics or something like that. But in ado.net2.0, if you notice numberofreclaimedconnections, You will know if your application is connected to a leak.


always pay attention to fixing related connection strings

Modifying the associated connection string allows you to temporarily translate "escape" some exceptions, which is very tempting. Especially in a high-performance consumption, it is more necessary to modify it.


Here are some unusual behaviors that allow your application to "run Well" (in the foot of the foot)

Don't put Poooling=false

Frankly, if you set the pooling to a closed state, you will of course not run out of timeout exceptions, and the terrible thing is that your application performance will be significantly reduced and your connection is still leaking.

Don't put connection lifetime=1

This is not a way to clear the exception, but it may be the closest solution. What you want to tell us is to discard all connections for more than one second (the normal life cycle should end at Connetcio.close ()). I personally think it's no different to turn off the connection pool in this way. Unless you are using a database cluster, you should not set the connection cycle to achieve the goal.


do not Connection the timeout=40000


Very stupid choice, you're telling us that before you throw out a timeout exception, you are waiting indefinitely for a connection to be turned into usable. Luckily, ASP.net will cancel a process in three minutes.


don't poolsize=4000 Max;
If you set the maximum number of connection pools to large enough, you will end up stopping this abnormally. But on the other hand, you're going to take up the huge connection resources that you really need in your application, and that's the only way to quench your thirst.



Solution:

You need to make sure that every time you call a connection, it is closed with close () or Dispose (). The easiest way to do this is to modify your connection leak method to the following code style:

public void Doesnotleakconnections ()
{
Using (SqlConnection sqlconnection1 = new SqlConnection ("server=.//sqlexpress; integrated security=sspi;connection Timeout=5 ")) {
SqlConnection1. Open ();
SqlCommand sqlcommand1 = sqlconnection1. CreateCommand ();
Sqlcommand1.commandtext = "RAISERROR (' This is a fake exception ', 17,1)";
SqlCommand1.  ExecuteNonQuery (); This throws a SqlException every time it is called.
SqlConnection1. Close (); Still never gets called.
}//Here sqlconnection1. Dispose is _guaranteed_
}



FAQ:

Q: Why do you do this?

A: Using A use structure is equivalent to try/.../finally{<using object>. Dispose ()) Even when ExecuteNonQuery throws an execution error, we can guarantee that the finally module will execute



Q: Can I use close () or Dispose () if there is no exception in the above code?

A: We have no qualms about using either of them, or two of them at the same time. Using close () or Dispose () in a connection that is already close or dipose () does not affect

What's the difference between q:close () and Dispose (), which one should I use?
A: They do the same thing, you can call either of them, or two of them to use at the same time.

Q: What do you mean by "practically the same thing"?
A:dispose () will clean the associated connection through SqlConnection, followed by close (). There's no essential difference, you can prove it by reflector.

Q: Does Connection.dispose () remove the connection as compared to close ()?
A: No

---------------------------------------------------------------

My share:

I have also consulted a lot of information on the anomaly of "Timeout expired". In China, many of our project will use MS-provided sqlhelper this encapsulation class. Because this class has its own defects, so the occurrence of "Timeout expiered" abnormal probability is large. The solution I see in an article abroad is:

Add the cmd.commandtimeout= "you want to set the number of seconds" in SqlHelper and recompile.


if (trans!= null)
Cmd. Transaction = trans;

Cmd.commandtype = Cmdtype;
Cmd.commandtimeout = 240;


By searching, I found a related code.
This code is excerpted from one of our countrymen's peers, thanks
Http://blog.csdn.net/long2006sky/archive/2007/07/09/1683459.aspx

eg

**////<summary>
Execute Query statement, return DataTable
</summary>
<param name= "SqlString" > Query Statements </param>
<param name= "commtime" > Setup Query timeout</param>
<returns> for complex queries </returns>
public static DataTable getdatatable (String sqlstring,int commtime)
... {
String connectionString = system.configuration.configurationmanager.appsettings["connectionString"];
using (System.Data.SqlClient.SqlConnection connection = new System.Data.SqlClient.SqlConnection (connectionString))
... {
DataTable dt = new DataTable ();
Try
... {
Connection. Open ();
System.Data.SqlClient.SqlDataAdapter da = new System.Data.SqlClient.SqlDataAdapter ();
System.Data.SqlClient.SqlCommand comm = new System.Data.SqlClient.SqlCommand (sqlstring, connection);
Comm.commandtimeout = Commtime;
Da. SelectCommand = comm;
Da. Fill (DT);
}
catch (System.Data.SqlClient.SqlException ex)
... {
throw new Exception (ex. message);
}
return DT;
}
}

asp.net How do I set the number of database connection pools?Use a set of name-value pairs to configure a link pool as a link string. For example, you can configure whether the pool is valid (default is valid), the maximum and minimum capacity of the pool, used to hit
The time the queued request for the open link was blocked. The following sample string configures the maximum and minimum capacity of the pool.

"Server= (local); Integrated SECURITY=SSPI; Database=northwind;
Max Pool size=75; Min Pool size=5 "

Summary


Connection pooling allows an application to obtain a connection from the connection pool and use the connection without having to re-establish a connection for each connection request. Once a new connection is created

and placed in a connection pool, the application can reuse the connection without having to implement the entire database connection creation process.

When an application requests a connection, the connection pool assigns a connection to the application rather than re-establish a connection, and when the application finishes using the connection, the connection is returned to the connection

Pool rather than being released directly.

How to implement a connection pool

Make sure that you use the same connection string (same as the connection pool) for each connection, and that only the connection string will work together. If the connection string is not the same, the application

Instead of using a connection pool, you create a new connection.

Advantages

The main advantage of using connection pooling is performance. The time taken to create a new database connection depends primarily on the speed of the network and on the application and database server

(network) distance, and this process is usually a time-consuming process. With the database connection pool, the database connection request can be satisfied directly through the connection pool without the need for the requested

Reconnect and authenticate to the database server, which saves time.

Disadvantages

There may be a number of unused connections in the database connection pool that have been linked to the database (which means waste of resources).

Tips and Hints

1. Create a connection pool when you need a database connection instead of building it in advance. Once you have finished using the connection, close it immediately, and don't wait for the garbage collector to handle it.

2. Ensure that all user-defined transactions are closed before you close the database connection.

3. Do not close all connections in the database, at least one of the connections in the connection pool is available. If memory and other resources are issues that you must first consider, you can turn off all the connected

Then create the connection pool when the next request arrives.

Connection Pooling FAQ

1. When to create a connection pool.

Create a connection pool when the first connection request arrives; The connection pool's establishment is determined by the connection character Fu Shilai the database connection. Each connection pool is associated with a different connection string.

When a new connection request arrives, a connection is removed from the connection pool if the connection string is the same as the string used by the connection pool, or if it is not the same, a new connection pool is created.

2. When to close the connection pool.

Closes the connection pool when all connections in the connection pool have been closed.

3. What happens when a connection in a connection pool is exhausted and a new connection request arrives.

When a connection pool has reached its maximum number of connections, a new connection request is placed in the connection queue when a new connection request arrives. When a connection is released to the connection pool, the connection pool

The newly freed connection is assigned to a queued connection request in the queue. You can call close and Dispose to return the connection to the connection pool.

4. How should I allow connection pooling.

For. NET application, the default is to allow connection pooling. (This means you don't have to do anything for this matter) of course, if you can SqlConnection objects in the company

Add Pooling=true to the string; Make sure that your application allows the connection pool to be used.

5. How should I disable connection pooling.
Ado. NET defaults to allow database connection pooling, if you want to prohibit connection pooling, you can use the following methods:
1 when using the SqlConnection object, add the following content to the connection string: Pooling=false;
2 When using the OleDbConnection object, add the following content to the connection string: OLE DB services=-4;