Connection pooling and "Timeout expired" exceptions

Turn from: Blog Park quiet. Zhiyuan: http://www.cnblogs.com/zhangzhu/archive/2013/10/10/3361197.html

Exception information:

MySql.Data.MySqlClient.MySqlException (0x80004005): Error connecting: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.
At MySql.Data.MySqlClient.MySqlPool.GetConnection ()
At MySql.Data.MySqlClient.MySqlConnection.Open ()
At ArticleSys.MySqlHelper.PrepareCommand (Mysqlcommand cmd, mysqlconnection conn, mysqltransaction trans, CommandType Cmdtype, String Cmdtext, mysqlparameter[] cmdparms)
At ArticleSys.MySqlHelper.GetDataTable (string mysqlconnstr, CommandType cmdtype, String cmdtext, mysqlparameter[] Commandparameters)
=====================2013-10-10 12:28:33==============

------------The following is the text

System.InvalidOperationException: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.

The Timeout expired exception is a tricky anomaly that almost everyone has ever met. Sometimes it's really gnashing of teeth, and it's no way to take it. ANGELSB This article is very good, hoped to be useful to everybody. I also see him speak very well, only translated, the level is limited, please advise.


System.InvalidOperationException: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.

Hey! In another process, there is a problem with the connection pool full, which is one of the most headaches but also the most common connection pooling problem. The reason is that the headache is rarely encountered during the development process, but when deploying the app to the client, it always comes out inadvertently. I think, I should take some time to make a complete summary of this question.

What is the nature of the occurrence?

Let's take a serious look at two scenarios where this anomaly may occur

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

In most applications, this situation is rarely seen. After all, when you use connection pooling, 100 parallel connections are a very large number. According to my experience, the most probable cause of this anomaly would be to open 100 connections under a thoroughbred.

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 that 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 opened a connection but you did not execute close () or Dispose () in your code. This range is not just about forgetting to use Dispose () or close () to close a period after connection. Also includes some situations where you have written close () after the relevant connection, but have no effect at all. Let's take a look at the following 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 <=; 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 is still leaking connections (see above comment for explanation)
}
}

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

3) You are using 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 if a connection leak is in ado.net2.0

In 1.0 or 1.1, it is difficult to determine whether a connection leak can be achieved at most by some performance indicators or the like. But in ado.net2.0, if you notice the numberofreclaimedconnections, You can tell if your application is a connection leak.


always pay attention to fixing related connection strings

Modifying the associated connection string allows you to temporarily translate "escape" some anomalies, 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" (foot)

Don't put Poooling=false

Frankly, if you set the pooling to off, you certainly will not encounter the timeout exception, which is scary, your application performance will be greatly reduced, and your connection is still in a leaking state.

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 over a second (the normal life cycle should be after connetcio.close ()). I personally think that this method of closing the connection pool is no different. Unless you are using a database cluster, you should not set the connection cycle to achieve the purpose.


don't put Connection timeout=40000


Very foolish choice, you are telling us before throwing a timeout exception, you are indefinitely waiting for a connection to be converted to usable. Fortunately, in ASP. NET, a process is canceled in three minutes.


do not put Max poolsize=4000;
If you set the maximum number of connection pools to be large enough, you will end up stopping this abnormally. But on the other hand, you will take up the huge connection resources that are really needed in your application, which can only be consumed by thirst.

Solution:

You need to make sure that each time you invoke the connection, it is closed using close () or Dispose (). The simplest way to do this is to use the using and 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: Use A using structure equivalent to try/.../finally{. 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 I don't have an exception thrown in the code above?

A: We have no qualms about using any of them, or two simultaneously. Using close () or Dispose () in a connection that has close or dipose () is not affected

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

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

Q: Does Connection.dispose () remove any connections compared to close ()?
A: No

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

My share:

I also looked at a lot of information about the "Timeout expired" exception. In China, many of our project will use the SqlHelper package class provided by Ms. Because of its own flaws in this class, the "Timeout expiered" anomaly is very likely to occur. The solution I see in an article abroad is:

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


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

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


By searching, I found a code related to it.
This code is excerpted from one of the fellow countrymen, thanks
Http://blog.csdn.net/long2006sky/archive/2007/07/09/1683459.aspx

eg

**////
Executes a query statement that returns a DataTable
///
Query statements
Set query timeout
For complex queries
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;
}
}

Turn from: Blog Park quiet. Zhiyuan: http://www.cnblogs.com/zhangzhu/archive/2013/10/10/3361197.html

Connection pooling and "Timeout expired" exceptions