SQL connection timeout

Original article title: ConnectionPoolingandtheTimeoutexpiredexceptionFAQ Source: Author: angelsbSystem. InvalidOperationException: Timeoutexpired. Lost. Thismayhaveoccurredbe

Original article title: Connection Pooling and the "Timeout expired" exception FAQ Source: angelsb System. invalidOperationException: Timeout expired. the timeout period elapsed prior to obtaining a connection from the pool. this may have occurred be

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

Source:

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's really tough to get your teeth from the Hong Kong server. 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] = );
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
{
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 = );
Sqlconnection1.Open ();
SqlCommand sqlcommand1 = sqlconnection1.CreateCommand ();
Sqlcommand1.CommandText =;
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 above 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.
? 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: