连接池和 "Timeout expired"异常_.NET_编程开发_程序员俱乐部

http://blog.csdn.net/yanwu616/article/details/4301198

错误：

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.

最可能的原因：链接泄露

打开了一个连接但你没有在你的代码中执行close()或dispose().这范围不仅仅是你忘记了在connection后连接后使用dispose()或close()对期进行关闭；还包括一些你已经在相关connection后写好了close()却根本没有起作用的情況.我们来看看下面的代码:

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 above comment for explanation)
     }

}

这就是一个典型的例子,将这段代码复制到visual Studio中,在 sqlconnection1.close()中设置一个断点,编译时可以看到他永远没有执行,因为ExecuteNonQurery抛出了一个异常.之后你应该可以看到恐怖的超时异常了. 在我的机子上,大约有170个连接被打开. 我曾想让其在每次调用的时候将异常抛出来达到降低连接超时出现的机率,但当你考虑到将其部署到一个ASP.NET的应用程序的时候,任何一个泄漏都将让你处于麻烦之中.

解决方案:

你需要保证你每次调用连接的同时都在使用过后通过close()或dispose()对其执行了关闭.最简单的办法就是使用using,将你的连接泄漏方法修改成如下面的代码样式:

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_
     }