SQL Server连接池没有检测到关闭的连接？ [英] SQL Server connection pool doesn't detect closed connections?

问题描述

多年来，我已经经历了很奇怪的问题，在连接到SQL服务器上所有的Web应用程序。

问题是，如果事情发生在数据库服务器（服务器重启或其它问题），德Web应用程序停止从该点工作，即使数据库服务器是活得好好的算账。

什么情况是，每一个ADO.NET操作（的ExecuteNonQuery，CreateReader，的BeginTransaction，...）失败，出现 InvalidOperationException异常：操作无效的连接被关闭的。看来，调用在SQLConnection.open（）检索从中...关闭应用程序池的连接！

根据该文件，连接池应该自动删除从连接池中切断连接，但apparantly在关闭的连接不被视为切断，因此呼叫为在SQLConnection.open（）愉快地返回一个关闭的连接，假定它是开放的，没有检查这一点。

我目前的解决办法是打开它后立即检查该连接的状态：

 使用（SqlConnection的连接=新的SqlConnection（的connectionString））
{
   connection.Open（）;

   如果（connection.State！= ConnectionState.Open）
   {
      SqlConnection.ClearAllPools（）;

      connection.Open（）;
   }

   // ...
}
 

这个解决办法似乎工作，但我觉得不舒服这样做。

所以，我的问题是：

1. 为什么在SQLConnection.open（）从连接池中返回关闭的连接？
2. 是我的解决方法有效吗？
3. 有没有更好的方式来处理呢？

解决方案

我做了一些类似的研究，连接池，前一阵子，一个稍微不同的理由，但希望能有点用处。我发现是：

1. ，即使你关闭code的连接，这是不实际被关闭了连接返回到池中 - 准备进一步使用
2. 如果该连接被切断（即SQL Server重新启动），当从池返回的连接为其他呼叫者使用和调用者做了。开就可以了，它的没有错误的当数据库服务器仍下跌了这一点。这是连接池的性能优势的部分，因为它实际上没有回去过到数据库服务器进行连接。
3. 当你真正尝试对连接（例如的ExecuteNonQuery）正是在这一点上，它实际上抛出一个异常
执行命令

连接会自动从池中删除，我的调查结果，这通常发生在几分钟之内后，这是最后一次使用。因此，它可能是一个计时问题 - 和它们被清理，但在此之前的连接尝试再次重新使用

这些都是一些文章我看了看，当时的：
<一href="http://groups.google.ch/group/microsoft.public.sqlserver.connect/browse_frm/thread/3f8e3476ef757f75/98bd92a21a4bff4b?q=connection+pooling+ep$p$pcht&rnum=3&hl=en#98bd92a21a4bff4b">Sql服务器谷歌集团
<一href="http://www.developer.com/net/article.php/10916_3729831_3/Using-Connection-Pooling-in-ADONET.htm">Using在ASP.NET 连接池

编辑：
这听起来奇怪的是，糟糕的连接保持在池中永远 - 你确定它肯定不会，而且它不只是多重利空连接？如果您确信那么它听起来像这些连接都没有在你的code释放正确。 这是另一个很好的文章我前一阵子看，上面写着（报价）：

  

自动冲洗连接

     

如果一个池的连接保持在   关闭，但可重复使用的状态   在4和8分钟（间隔   随意选择）的连接   池机制关闭物理   连接并丢弃汇集   连接。也就是说，除非有数字   剩余的连接大   比最小连接   配置的池（默认   为0）。请注意，必须连接   已被关闭由应用程序   前（和释放回池）   它可以是受自动   推出。如果您不关闭   在code连接或孤儿   Connection对象，汇集   机制将什么也不做。不在这里   没有的ConnectionString参数   更改超时值。

For years, I've experienced very weird problems on all my web applications that connect to a SQL server.

The problem is that if something happens to the database server (server restart or other problem), de web app stops working from that point on, even if the database server is alive and well afterwards.

What happens is that every ADO.NET operation (ExecuteNonQuery, CreateReader, BeginTransaction, ...) fails with a InvalidOperationException: "Invalid operation. The connection is closed". It seems that a call to SqlConnection.Open() retrieves a connection from the application pool which is... closed!

According to the documentation, the connection pool should automatically remove severed connections from the connection pool, but apparantly a closed connection isn't regarded as "severed", so the call to SqlConnection.Open() happily returns a closed connection, assuming it is open, without checking this.

My current workaround is to check for the state of the connection right after opening it:

using (SqlConnection connection = new SqlConnection( connectionString ))
{
   connection.Open();

   if (connection.State != ConnectionState.Open)
   {
      SqlConnection.ClearAllPools();

      connection.Open();
   }

   // ...
}

This workaround seems to work for now, but I don't feel comfortable doing this.

So my questions are:

1. Why does SqlConnection.Open() return closed connections from the connection pool?
2. Is my workaround valid?
3. Is there a better way to handle this?

解决方案

I did some similar research into connection pooling a while ago, for a slightly different reason, but hopefully will be of some use. What I found is:

1. even when you close a connection in code, it is returned to the pool without the connection actually being closed - ready for further use.
2. if that connection gets severed (i.e. SQL Server restarts), when the connection is returned from the pool for another caller to use and that caller does a .Open on it, it does not error at that point when the database server is still down. This is part of the performance benefit of connection pooling as it's not actually going back off to the database server to connect.
3. when you actually try to execute a command against the connection (e.g. ExecuteNonQuery) it is at that point it actually throws an exception

Connections are automatically removed from the pool, my findings were that this typically occurred within a few minutes after it was last used. So, it may be a timing issue - and they are being cleared up, but not before the connections are attempted to be reused again.

These were some articles I looked at, at the time:
Sql Server Google Group
Using Connection Pooling in ASP.NET

Edit:
It does sound odd that the bad connection stays in the pool forever - are you sure it definitely does, and it's not just multiple bad connections? If you are sure then it sounds like those connections aren't being released properly within your code. This is another very good article I read a while ago, that says (quote):

Automatically Flushing Connections

If a pooled connection remains in the "closed but reusable" state for between 4 and 8 minutes (an interval chosen at random) the connection pooling mechanism closes the physical connection and discards the pooled connection. That is unless the number of remaining connections is greater than the minimum connections configured for the pool (the default is 0). Note that a connection must have been closed by the application (and released back to the pool) before it can be subject to automatic release. If you don’t close the connection in code or orphan the Connection object, the pooling mechanism will do nothing. No, there are no ConnectionString arguments to change the timeout value.

这篇关于SQL Server连接池没有检测到关闭的连接？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！