sql 连接超时问题

原文标题：Connection Pooling and the "Timeout expired" exception FAQ

来源：http://blogs.msdn.com/angelsb/archive/2004/08/25/220333.aspx

作者：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 异常是个很棘手的异常，想必几乎每个人都碰到过。有时可真是对它咬牙切齿，拿它没办法。 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.

哎!在另一个进程中,又出现了连接池已满的问题,这是个最让人头痛却又是最常出现的连接池问题之一.原因是在开发过程中很少碰到这个头痛的问题,但在部署APP到客户端时,却总是不经意地跑出来了.我想,我应该花些许时间对这个问题进行一次完整的总结吧.

发生的本质是什么?

我们来认真看一下可能会发生这种异常的两种情况

1) 你使用了超过最大的连接池连接数(默认的最大连接数是100)

在大部分应用程序中,这种情况是很少出现的. 毕竟当你使用连接池时,100个并行连接是一个非常大的数字.根据我的经验,会造成这种异常的原因的最大可能,应该是在一个纯种下打开了100个连接.

程序代码

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

 

解决方案:如果你确定你将会使用超过100个并行连接(在同一连接字符串上),你可以增加最大连接数.

2) 连接泄漏

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

程序代码

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

 

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

3)你是通过visual Studio中的sql debugging 来打开或关闭连接的

这是一个众所周知的Bug,可以看一下下面这个链接
http://support.microsoft.com/default.aspx?scid=kb;en-us;830118



如何在ADO.NET2.0中判断是否是连接泄漏

在1.0或1.1中,我们很难去判断是否是连接泄漏,至多可以通过一些性能指标或诸如此类的工作去实现.但在ADO.NET2.0中,如果你注意到NumberOfReclaimedConnections这个玩艺儿,就可以知道你的应用程序是否是连接泄漏了.


时刻注意修复相关的连接字符串

修改相关的连接字符串可以让你暂时翻译”逃过”一些异常,这是非常诱人的.特别是在一个高性能消耗时,修改它就显示更为必要了.


这里是一些让你的应用程序能”运行良好”的非正常行为(搬起石头砸自己的脚)

不要把Poooling=False

坦白的说,如果你将pooling设为关闭状态,你当然不会再碰到超时异常,可怕的是你的应用程序性能将大大降低,而你的连接仍然处于泄漏状态.

不要把Connection LifeTime=1

这不是一个能清除异常的方法,但它可能是最接近的一个解决方法.你想告诉我们的是将所有的连接超过一秒钟的连接都通通抛弃(正常的生命周期结束应该是在connetcio.close()后).我个人认为这种方法上关闭连接池没什么两样.除非你是在使用数据库的集群,否则你不应设置连接周期来达到目的.


不要将 Connection TimeOut=40000


非常愚蠢的选择,你这是在告诉我们在抛出一个超时异常之前,你在无限地等待一个连接转变为可用的.幸亏在ASP.NET中将会在三分钟之后取消一个进程.


不要将Max PoolSize=4000;
如果你将连接池的最大数设置到足够大的时候,你最终会将这异常停止.但在另一方面,你将占用了你的应用程序中才是真正需要的巨大的连接资源,这种做法只能饮鸠止渴.



解决方案:

你需要保证你每次调用连接的同时都在使用过后通过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_
}

FAQ：

Q:为什么要这样做

A:使用using结构等同于Try/…/Finally{ <using object>.Dispose() ) 即使当ExecuteNonQuery会抛出一个执行错误时,我们都可以保证finally模块将会执行



Q:我上面的代码中如果没有异常抛出的话,我可以使用close()或dispose()吗

A:我们毫无顾忌地用他们中的任意一个,或两个同时使用.在一个已经close或dipose()的连接中使用close()或dispose()是不会影响的

Q:Close()和Dispose()有什么不同,我应该用哪一个好?
A:它们做的是同一件事,你可以调用他们中的任意一个,或两个同时使用.

Q:你所说的"practically the same thing”是什么意思?
A:Dispose()将会通过sqlConnection来清理相关的连接,之后执行close().它们没有什么本质的区别,你可以通过reflector来证明这点

Q:与close()相比,connection.dispose()会将连接些移除吗?
A:不会

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

我的分享：

针对"Timeout expired"这个异常，我也查阅了很多资料。在国内我们很多project都会采用MS提供的sqlhelper这个封装类。因为这个类中有本身的缺陷所致，所以出现的"Timeout expiered"异常机率大。我在国外的一篇文章中看到的解决方案是：

将SqlHelper中的cmd.CommandTimeout="你要设置的秒数"加上去，重新编译.

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

cmd.CommandType = cmdType;
cmd.CommandTimeout = 240;

通过搜索，我找到了相关的一个代码.
这个代码是摘自国人的某位同行的,感谢
http://blog.csdn.net/long2006sky/archive/2007/07/09/1683459.aspx

eg:

//// <summary>
/// 执行查询语句，返回DataTable
///</summary>
///<param name="SQLString">查询语句</param>
///<param name="commTime">设置查询Timeout</param>
///<returns>用于复杂查询</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;
}
}