System.Data.SQLClient.SqlConnection在Open之后为什么需要及时Close?
如果System.Data.SQLClient.SqlConnection(下面简称dbconn)不及时Close,那么造成的后果可以在这里看到:http://www.cnblogs.com/juqiang/archive/2005/12/10/294269.html
具体的原因,我们来看代码(用reflector反编译.net framework 2.0的dll)
dbconn有一个方法叫做:Open,部分代码如下:
static SqlConnection()
{
EventInfoMessage = new object();
_connectionFactory = SqlConnectionFactory.SingletonInstance;
ExecutePermission = CreateExecutePermission();
}
在看第一段代码的第4行,这里用InnerConnection来进行OpenConnection,那么InnerConnection的类型,我们可以从下面看到:
public SqlConnection()
{
this.ObjectID = Interlocked.Increment(ref _objectTypeCount);
GC.SuppressFinalize(this);
this._innerConnection = DbConnectionClosedNeverOpened.SingletonInstance;
}
它是一个DbConnectionClosedNeverOpened类型(具体做啥的,我们不管),它的基类是:DbConnectionClose。ok,确定好上述两个类型后,我们就看一下OpenConnection的代码:(class DbConnectionClosed)
protected override DbConnectionPoolGroupOptions CreateConnectionPoolGroupOptions(DbConnectionOptions connectionOptions)
{
SqlConnectionString str = (SqlConnectionString) connectionOptions;
if (str.ContextConnection || !str.Pooling)
{
return null;
}
int connectTimeout = str.ConnectTimeout;
if ((0 < connectTimeout) && (connectTimeout < 0x20c49b))
{
connectTimeout *= 0x3e8;
}
else if (connectTimeout >= 0x20c49b)
{
connectTimeout = 0x7fffffff;
}
return new DbConnectionPoolGroupOptions(str.IntegratedSecurity, str.MinPoolSize, str.MaxPoolSize, connectTimeout, str.LoadBalanceTimeout, str.Enlist, false);
}
看最后一行,调用了一个new操作,注意里面的参数!有MaxPoolSize,MinPoolSize等属性。而这些重要属性是从str,即SqlConnectionString的一个property(Class DbConnectionPool,快到终点了,坚持一下。。。)
那么,如果我们代码中对于dbconn进行了Open,没有进行Close,conn pool就会一直增长,一直涨到100,涨到这个Max Pool Size。原因我们看下面:(Class DbConnectionPool)
private DbConnectionInternal UserCreateRequest(DbConnection owningObject)
{
DbConnectionInternal internal2 = null;
if (this.ErrorOccurred)
{
throw this._resError;
}
if ((this.Count >= this.MaxPoolSize) && (this.MaxPoolSize != 0))
{
return internal2;
}
if (((this.Count & 1) != 1) && this.ReclaimEmancipatedObjects())
{
return internal2;
}
return this.CreateObject(owningObject);
}
上面的代码很清楚的表明,如果count>=MaxPoolsize,会返回一个internal2,而这个field是一个Null!从其他的地方,我们也能找到类似的判断。
上面写了这么多,结论就是,如果dbconn在Open之后,没有Close,那么会造成conn个数上涨,到100之后就会停下来。第101个链接的请求,是无法创建成功的。这样,db和web server很可能都是CPU很低,如0%,但是客户端的响应时间就是很长,造成性能下降。
具体的原因,我们来看代码(用reflector反编译.net framework 2.0的dll)
dbconn有一个方法叫做:Open,部分代码如下:
1
try
2
{
3
statistics = SqlStatistics.StartTimer(this.Statistics);
4
this.InnerConnection.OpenConnection(this, this.ConnectionFactory);
5
SqlInternalConnectionSmi innerConnection = this.InnerConnection as SqlInternalConnectionSmi;
6
if (innerConnection != null)
7
{
8
innerConnection.AutomaticEnlistment();
9
}
10![](/Images/OutliningIndicators/None.gif)
注意上面的第4行,this.InnerConnection.OpenConnection(this, this.ConnectionFactory);这里的this.ConnectionFactory是一个DbConnectionFactory类型,定义在class SqlConnection中![](/Images/OutliningIndicators/None.gif)
2
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
3
![](/Images/OutliningIndicators/InBlock.gif)
4
![](/Images/OutliningIndicators/InBlock.gif)
5
![](/Images/OutliningIndicators/InBlock.gif)
6
![](/Images/OutliningIndicators/InBlock.gif)
7
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
8
![](/Images/OutliningIndicators/InBlock.gif)
9
![](/Images/OutliningIndicators/ExpandedBlockEnd.gif)
10
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/ExpandedBlockEnd.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/ExpandedBlockEnd.gif)
1
try
2
{
3
connectionFactory.PermissionDemand(outerConnection);
4
to = connectionFactory.GetConnection(outerConnection);
5
}
6
catch
7
{
8
connectionFactory.SetInnerConnectionTo(outerConnection, this);
9
throw;
10
}
看一下第4行,哈,这里绕到了我们上面的conn factory,我们看它里面的GetConnection怎么写的?(class DbConnectionFactory)![](/Images/OutliningIndicators/None.gif)
2
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
3
![](/Images/OutliningIndicators/InBlock.gif)
4
![](/Images/OutliningIndicators/InBlock.gif)
5
![](/Images/OutliningIndicators/ExpandedBlockEnd.gif)
6
![](/Images/OutliningIndicators/None.gif)
7
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
8
![](/Images/OutliningIndicators/InBlock.gif)
9
![](/Images/OutliningIndicators/InBlock.gif)
10
![](/Images/OutliningIndicators/ExpandedBlockEnd.gif)
1
internal DbConnectionInternal GetConnection(DbConnection owningConnection)
2
{
3
DbConnectionInternal connection;
4
DbConnectionPoolGroup connectionPoolGroup = this.GetConnectionPoolGroup(owningConnection);
5
DbConnectionPool connectionPool = this.GetConnectionPool(owningConnection, connectionPoolGroup);
6
if (connectionPool == null)
7
{
8
connectionPoolGroup = this.GetConnectionPoolGroup(owningConnection);
9
connection = this.CreateNonPooledConnection(owningConnection, connectionPoolGroup);
10
this.PerformanceCounters.NumberOfNonPooledConnections.Increment();
11
return connection;
12
}
13
connection = connectionPool.GetConnection(owningConnection);
14
if (connection == null)
15
{
16
Bid.Trace("<prov.DbConnectionFactory.GetConnection|RES|CPOOL> %d#, GetConnection failed because a pool timeout occurred.\n", this.ObjectID);
17
throw ADP.PooledOpenTimeout();
18
}
19
return connection;
20
}
注意第5行里面的GetConnectionPool的调用,继续看它内部的代码:(class DbConnectionFactory)![](/Images/OutliningIndicators/None.gif)
2
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
3
![](/Images/OutliningIndicators/InBlock.gif)
4
![](/Images/OutliningIndicators/InBlock.gif)
5
![](/Images/OutliningIndicators/InBlock.gif)
6
![](/Images/OutliningIndicators/InBlock.gif)
7
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
8
![](/Images/OutliningIndicators/InBlock.gif)
9
![](/Images/OutliningIndicators/InBlock.gif)
10
![](/Images/OutliningIndicators/InBlock.gif)
11
![](/Images/OutliningIndicators/InBlock.gif)
12
![](/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
13
![](/Images/OutliningIndicators/InBlock.gif)
14
![](/Images/OutliningIndicators/InBlock.gif)
15
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
16
![](/Images/OutliningIndicators/InBlock.gif)
17
![](/Images/OutliningIndicators/InBlock.gif)
18
![](/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
19
![](/Images/OutliningIndicators/InBlock.gif)
20
![](/Images/OutliningIndicators/ExpandedBlockEnd.gif)
1
private DbConnectionPool GetConnectionPool(DbConnection owningObject, DbConnectionPoolGroup connectionPoolGroup)
2
{
3
if (connectionPoolGroup.IsDisabled && (connectionPoolGroup.PoolGroupOptions != null))
4
{
5
Bid.Trace("<prov.DbConnectionFactory.GetConnectionPool|RES|INFO|CPOOL> %d#, DisabledPoolGroup=%d#\n", this.ObjectID, connectionPoolGroup.ObjectID);
6
DbConnectionPoolGroupOptions poolGroupOptions = connectionPoolGroup.PoolGroupOptions;
7
DbConnectionOptions connectionOptions = connectionPoolGroup.ConnectionOptions;
8
string connectionString = connectionOptions.UsersConnectionString(false);
9
connectionPoolGroup = this.GetConnectionPoolGroup(connectionString, poolGroupOptions, ref connectionOptions);
10
this.SetConnectionPoolGroup(owningObject, connectionPoolGroup);
11
}
12
return connectionPoolGroup.GetConnectionPool(this);
13
}
注意第9行,我们go on...(Class DbConnectionFactory)![](/Images/OutliningIndicators/None.gif)
2
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
3
![](/Images/OutliningIndicators/InBlock.gif)
4
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
5
![](/Images/OutliningIndicators/InBlock.gif)
6
![](/Images/OutliningIndicators/InBlock.gif)
7
![](/Images/OutliningIndicators/InBlock.gif)
8
![](/Images/OutliningIndicators/InBlock.gif)
9
![](/Images/OutliningIndicators/InBlock.gif)
10
![](/Images/OutliningIndicators/InBlock.gif)
11
![](/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
12
![](/Images/OutliningIndicators/InBlock.gif)
13
![](/Images/OutliningIndicators/ExpandedBlockEnd.gif)
1
if ((poolOptions == null) && ADP.IsWindowsNT)
2
{
3
if (group != null)
4
{
5
poolOptions = group.PoolGroupOptions;
6
}
7
else
8
{
9
poolOptions = this.CreateConnectionPoolGroupOptions(options);
10
}
11
}
12![](/Images/OutliningIndicators/None.gif)
依然是第9行(枯燥的数字。。。),继续往里面看:(Class DbConnectionFactory)![](/Images/OutliningIndicators/None.gif)
2
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
3
![](/Images/OutliningIndicators/InBlock.gif)
4
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
5
![](/Images/OutliningIndicators/InBlock.gif)
6
![](/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
7
![](/Images/OutliningIndicators/InBlock.gif)
8
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
9
![](/Images/OutliningIndicators/InBlock.gif)
10
![](/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
11
![](/Images/OutliningIndicators/ExpandedBlockEnd.gif)
12
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/ExpandedBlockEnd.gif)
1
internal SqlConnectionString(string connectionString) : base(connectionString, GetParseSynonyms(), false)
2
{
3
bool inProc = InOutOfProcHelper.InProc;
4
this._integratedSecurity = base.ConvertValueToIntegratedSecurity();
5
this._async = base.ConvertValueToBoolean("asynchronous processing", false);
6
this._connectionReset = base.ConvertValueToBoolean("connection reset", true);
7
this._contextConnection = base.ConvertValueToBoolean("context connection", false);
8
this._encrypt = base.ConvertValueToBoolean("encrypt", false);
9
this._enlist = base.ConvertValueToBoolean("enlist", ADP.IsWindowsNT);
10
this._mars = base.ConvertValueToBoolean("multipleactiveresultsets", false);
11
this._persistSecurityInfo = base.ConvertValueToBoolean("persist security info", false);
12
this._pooling = base.ConvertValueToBoolean("pooling", true);
13
this._replication = base.ConvertValueToBoolean("replication", false);
14
this._userInstance = base.ConvertValueToBoolean("user instance", false);
15
this._connectTimeout = base.ConvertValueToInt32("connect timeout", 15);
16
this._loadBalanceTimeout = base.ConvertValueToInt32("load balance timeout", 0);
17
this._maxPoolSize = base.ConvertValueToInt32("max pool size", 100);
18
this._minPoolSize = base.ConvertValueToInt32("min pool size", 0);
19
this._packetSize = base.ConvertValueToInt32("packet size", 0x1f40);
===============================其他代码省略========================================
181
}
182
}
这段代码很长,但是我们注意上面的第17行,这里有一个默认值100,就是说,如果你没有在connection string中指定max pool size,那么该值就是100。![](/Images/OutliningIndicators/None.gif)
2
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
3
![](/Images/OutliningIndicators/InBlock.gif)
4
![](/Images/OutliningIndicators/InBlock.gif)
5
![](/Images/OutliningIndicators/InBlock.gif)
6
![](/Images/OutliningIndicators/InBlock.gif)
7
![](/Images/OutliningIndicators/InBlock.gif)
8
![](/Images/OutliningIndicators/InBlock.gif)
9
![](/Images/OutliningIndicators/InBlock.gif)
10
![](/Images/OutliningIndicators/InBlock.gif)
11
![](/Images/OutliningIndicators/InBlock.gif)
12
![](/Images/OutliningIndicators/InBlock.gif)
13
![](/Images/OutliningIndicators/InBlock.gif)
14
![](/Images/OutliningIndicators/InBlock.gif)
15
![](/Images/OutliningIndicators/InBlock.gif)
16
![](/Images/OutliningIndicators/InBlock.gif)
17
![](/Images/OutliningIndicators/InBlock.gif)
18
![](/Images/OutliningIndicators/InBlock.gif)
19
![](/Images/OutliningIndicators/InBlock.gif)
===============================其他代码省略========================================
181
![](/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
182
![](/Images/OutliningIndicators/ExpandedBlockEnd.gif)
那么,如果我们代码中对于dbconn进行了Open,没有进行Close,conn pool就会一直增长,一直涨到100,涨到这个Max Pool Size。原因我们看下面:(Class DbConnectionPool)
1
case 2:
2
Bid.PoolerTrace("<prov.DbConnectionPool.GetConnection|RES|CPOOL> %d#, Creating new connection.\n", this.ObjectID);
3
try
4
{
5
fromTransactedPool = this.UserCreateRequest(owningObject);
6
}
7
catch
8
{
9
if (fromTransactedPool == null)
10
{
11
Interlocked.Decrement(ref this._waitCount);
12
}
13
throw;
14
}
15![](/Images/OutliningIndicators/None.gif)
注意上面的第5行,UserCreateRequest,看这个方法:![](/Images/OutliningIndicators/None.gif)
2
![](/Images/OutliningIndicators/None.gif)
3
![](/Images/OutliningIndicators/None.gif)
4
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
5
![](/Images/OutliningIndicators/InBlock.gif)
6
![](/Images/OutliningIndicators/ExpandedBlockEnd.gif)
7
![](/Images/OutliningIndicators/None.gif)
8
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
9
![](/Images/OutliningIndicators/InBlock.gif)
10
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
11
![](/Images/OutliningIndicators/InBlock.gif)
12
![](/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
13
![](/Images/OutliningIndicators/InBlock.gif)
14
![](/Images/OutliningIndicators/ExpandedBlockEnd.gif)
15
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/ExpandedBlockEnd.gif)
上面写了这么多,结论就是,如果dbconn在Open之后,没有Close,那么会造成conn个数上涨,到100之后就会停下来。第101个链接的请求,是无法创建成功的。这样,db和web server很可能都是CPU很低,如0%,但是客户端的响应时间就是很长,造成性能下降。