[Translation] connection pool and "Timeout expired" exceptions

Original article title: Connection Pooling and the "Timeout expired" exception FAQ

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 may have occurred because all pooled connections were in use and max pool size was reached.

Timeout expired exception is a very tricky exception, and almost everyone must have encountered it. Sometimes it is really a bit of a tooth, it can not be used. Angelsb is a good article and I hope it will be useful to you. I also saw that he spoke very well before he translated it. The level is limited. Please give me more advice.

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

Ah! In another process, the connection pool is full again, which is one of the most annoying but common connection pool problems. the reason is that this headache is rarely encountered during the development process. However, when deploying an APP to the client, it always runs out inadvertently. I think I should spend some time summing up this question.

What is the nature of occurrence?

Let's take a closer look at the two possible exceptions.

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

This is rare in most applications. after all, when you use a connection pool, 100 parallel connections are a very large number. according to my experience, the biggest possible cause of this exception is that 100 connections are opened in a purebred directory.

Program code

SqlConnection [] connectionArray = new SqlConnection [1, 101];
For (int I = 0; I <= 100; I ++)
{
ConnectionArray [I] = new SqlConnection ("Server =. \ SQLEXPRESS; Integrated security = sspi; connection timeout = 5 ");
ConnectionArray [I]. Open ();
}

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

2) connection Leakage

In my opinion, connection Leakage is defined as that you opened a connection but you did not execute close () or dispose () in your code (). this range is not only because you forget to use dispose () or close () to close the connection period after connection, but also because you have written close () after connection () it does not work 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 (see abve comment for explanation)
}
}

This is a typical example. Copy this code to visual Studio and set a breakpoint in sqlconnection1.close (). During compilation, we can see that it is never executed because ExecuteNonQurery throws an exception. then you can see that the terrorism timeout is abnormal. on my host, about 170 connections are opened. I once wanted to throw an exception during each call to reduce the chance of connection timeout, but when you consider deploying it to an ASP. NET Applications, any leakage will put you in trouble.

3) You use SQL debugging in visual Studio to open or close the connection.

This is a well-known Bug. You can refer to the link below the issue.
Http://support.microsoft.com/default.aspx? Scid = kb; en-us; 830118

How to determine whether connection leakage exists in ADO. NET2.0

In 1.0 or 1.1, it is difficult for us to determine whether a connection is leaked. At most, it can be achieved through some performance indicators or such work. but in ADO. in NET2.0, if you notice NumberOfReclaimedConnections, you can know whether your application is a connection leak.

Always pay attention to fixing related connection strings

Modifying the relevant connection string allows you to temporarily translate "escape" some exceptions, which is very attractive, especially when a high performance consumption occurs, it is more necessary to modify it.

Here are some abnormal behaviors that enable your application to "run well" (throwing a rock at your feet)

Do not set Poooling to False

Frankly speaking, if you set pooling to disabled, you certainly won't encounter a timeout exception any more. What's terrible is that your application performance will be greatly reduced, and your connection is still in the leak state.

Do not set Connection LifeTime = 1

This is not a way to clear exceptions, but it may be the closest solution. what you want to tell us is to discard all connections that have been connected for more than one second (the normal end of the lifecycle should be in connetcio. close ). I personally think that closing the connection pool in this way is no different. unless you are using a database cluster, you should not set a connection cycle for the purpose.

Do not set Connection TimeOut = 40000

This is a very stupid choice. Before you tell us to throw a timeout exception, you are infinitely waiting for a connection to change to available. thanks to ASP. NET will cancel a process in three minutes.

Do not set Max PoolSize to 4000;
If you set the maximum number of connection pools to a large enough value, you will eventually stop the exception. however, on the other hand, you will occupy the huge connection resources that your applications really need. This method can only be used to quench your thirst.

Solution:

You need to ensure that you close the connection through close () or dispose () after each call. the simplest way is to use using to change your connection leakage 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?

A: The using structure is equivalent to Try /... /Finally {<using object>. Dispose () Even if ExecuteNonQuery throws an execution error, we can ensure that the finally module will execute

Q: Can I use close () or dispose () If no exception is thrown in the above Code?

A: We use either one or both of them with no scruples. Using close () or dispose () in A closed or dipose () connection will not be affected.

Q: What is the difference between Close () and Dispose ()? Which one should I use?
A: They do the same thing. You can call either of them or use them at the same time.

Q: What do you mean by "practically the same thing?
A: Dispose () will clean up related connections through sqlConnection, and then execute close (). There is no essential difference between them. You can prove this through reflector.

Q: Will connection. dispose () remove connections compared to close?
A: No

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

My shares:

For the exception "Timeout expired", I also read a lot of information. In China, many projects adopt the sqlhelper encapsulation class provided by MS. Because this class has its own defects, the probability of a "Timeout expiered" exception is high. The solution I saw in an article abroad is:

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

If (trans! = Null)
Cmd. Transaction = trans;

Cmd. CommandType = primitive type;
Cmd. CommandTimeout = 240;

After searching, I found a related code.
This code is taken from a fellow Chinese. Thank you!
Http://blog.csdn.net/long2006sky/archive/2007/07/09/1683459.aspx

Eg:

** // <Summary>
/// Execute the query statement and return the DataTable
/// </Summary>
/// <Param name = "SQLString"> query statement </param>
/// <Param name = "commTime"> set the query Timeout </param>
/// <Returns> used for complex queries </returns>
Public static DataTable GetDataTable (string SQLString, int commTime)
...{
String connectionString = System. Configuration. ConfigurationManager. etettings ["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;
}
}
Http://www.dezai.cn/blog/article.asp? Id = 226