多线程 Windows 服务中多个同时 SQL 连接超时
我有一个用 VS 2010 (.NET 4.0) 开发的多线程 Windows 服务，它可以有几个到几十个线程，每个线程通过 Internet 从慢速服务器检索数据，然后使用本地数据库记录此数据数据（因此该过程受 Internet 限制，而不是 LAN 或 CPU 限制）。
有规律地，我同时从多个线程中收到大量/混乱/突发的以下错误：
System.Data.SqlClient.SqlException (0x80131904)：超时已过期。操作完成之前超时时间已过，或者服务器没有响应。
此错误的调用堆栈通常为：
在 System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)
在 System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
在 System.Data.ProviderBase.DbConnectionClosed.OpenConnection（DbConnection 外连接，DbConnectionFactory 连接工厂）
在 System.Data.SqlClient.SqlConnection.Open()
我没有在连接字符串中指定连接超时，并且还有其他应用程序和进程在此数据库中工作。有人遇到过这种行为吗？如果有，采取了什么措施来防止这种行为？
我的数据访问层中最常调用的方法如下所示，并且我所有其他 DAL 方法都遵循相同的方法：
using (SqlConnection con = new SqlConnection(GetConnectionString()))
using (SqlCommand cmd = new SqlCommand("AddGdsMonitorLogEntry", con))
{
cmd.CommandType = CommandType.StoredProcedure;
/* setting cmd.Parameters [snipped] */
// We have been getting some timeouts writing to the log; wait a little longer than the default.
cmd.CommandTimeout *= 4;
con.Open();
cmd.ExecuteNonQuery();
}
非常感谢！
编辑
鉴于有关镜像环境中发生的这种情况的评论，我确实应该提到有问题的数据库是镜像的。它在 SSMS 中标记为“主体，同步”，处于“高安全性，无需自动故障转移（同步）”模式。
编辑 2011 年 5 月 26 日
我在 SQL Server 日志中没有看到任何表明存在任何问题的内容。 （我无权访问该服务器上的 Windows 事件查看器，但我已要求有人来找我。）
I have a multithreaded Windows Service I've developed with VS 2010 (.NET 4.0) which can have anywhere from a few to a few dozen threads, each retrieving data from a slow server over the Internet and then using a local database to record this data (so the process is Internet-bound, not LAN or CPU bound).
With some regularity, I am getting a flood/flurry/burst of the following error from several threads simultaneously:
System.Data.SqlClient.SqlException (0x80131904): Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
The call stack for this error is typically:
at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
I'm not specifying a Connection Timeout in the connection string, and there are other applications and processes working in this database. Has anyone come across this kind of behavior and if so what was done to prevent it?
The most commonly-called method in my data access layer looks like this, and all my other DAL methods follow the same approach:
using (SqlConnection con = new SqlConnection(GetConnectionString()))
using (SqlCommand cmd = new SqlCommand("AddGdsMonitorLogEntry", con))
{
cmd.CommandType = CommandType.StoredProcedure;
/* setting cmd.Parameters [snipped] */
// We have been getting some timeouts writing to the log; wait a little longer than the default.
cmd.CommandTimeout *= 4;
con.Open();
cmd.ExecuteNonQuery();
}
Thanks very much!
EDIT
Given comments about this occurring in mirrored environments, I should indeed mention that the database in question is mirrored. It's marked in SSMS as "Principal, Synchronized", in "High safety without automatic failover (synchronous)" mode.
EDIT 5/26/11
I am seeing nothing in the SQL Server logs to indicate any problems. (I don't have access to the Windows Event Viewer on that server, but I've asked for someone to look for me.)
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（5）
根据 MSDN 博客今天刚刚创建的帖子（谷歌万岁！）：
这篇文章本身很有趣，讨论了 TCP/IP 连接重试算法。感谢所有说“嘿，这看起来与镜像有关......”的人！请注意对此的评论是“由于 SQL Server 响应缓慢或由于网络延迟”。
UGH!!!
感谢所有发帖的人。现在我们都必须请求 .NET Framework 的补丁（或其他一些 ADO.NET 补丁机制），因此我们不必等待（并购买）Visual Studio 11...
According to the MSDN Blog post just created today (hooray for Google!):
The post itself is an interesting read, talking about a TCP/IP connection retry algorithm. And kudos to all the folks who said "hey this looks like it's related to mirroring..."! And note the comment about this being "because of slow response from SQL Server or due to network delays".
UGH!!!
Thanks to everyone who posted. Now we must all ask for a patch to the .NET Framework (or some other ADO.NET patching mechanism), so we don't have to wait for (and buy) Visual Studio 11...
连接超时与命令超时不同。命令超时适用于已建立连接但由于某些内部原因服务器无法在要求的时间内返回任何结果的情况。默认命令超时为 30 秒。
http://msdn.microsoft.com/en -us/library/system.data.sqlclient.sqlcommand.commandtimeout.aspx
尝试在连接字符串中指定连接超时。默认值为 15 秒，这可能是您看到问题的原因。
您还可以在代码中指定连接超时：
http://msdn.microsoft.com/en -us/library/system.data.sqlclient.sqlconnection.connectiontimeout.aspx
Connection timeout is a different thing than command timeout. Command timeout applies to situation when you have connection established, but due to some internal reasons server cannot return any results within required time. Default command timeout is 30 seconds.
http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.commandtimeout.aspx
Try to specify connection timeout in the connection string. Default value is 15 seconds what may be the reason of the issue you see.
You can also specify connection timeout in code:
http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.connectiontimeout.aspx
我每隔一段时间就会在我们拥有的旧数据库服务器上（现在已经有 10 年历史了）得到这个信息。当它确实发生时，那是因为有东西不断地通过连接/查询来锤击那个东西。我的猜测是，当它发生时，您会发现数据库服务器处于负载状态（或大量连接或类似的情况）无论如何，根据我的经验，如果您可以优化代码，优化数据库，获得更强大的功能数据库服务器等都有帮助。 Piotr 建议您可以做的另一件事就是延长连接超时时间。不过，我仍然会仔细检查并优化一些东西（从长远来看应该有所帮助）。
I get this every once in a while on this old database server that we have (coming up on 10 years old now). When it does happen though it's because something is hammering that thing with connections/queries constantly. My guess is that you'll find that when it happens the database server is under load (or a high number of connections or something along those lines) Anyway, in my experience if you can optimize the code, optimize the database, getting a beefier database server, etc. all helps. Another thing you can do, which Piotr suggests, is simply up the timeout for the connection. I'd still go through and optimize some stuff though (should help in the long run).
我已经能够在某种程度上可靠地重现这个问题。我有一项服务，当请求处理作业时，它会在新的应用程序域/线程中开始处理。该线程将同时执行 10 到 16 个数据库查询。当我依次运行其中 30 个作业时，随机的一两个作业将因超时错误而崩溃。
我更改了连接字符串以使用 Pooling=false 关闭连接池，然后错误更改为以下内容。由于连接发生在 Parallel.For 内部，因此在聚合异常中会抛出 3 或 4 次
I have been able to somewhat reliably reproduce this problem. I have a service that when a processing job is requested it kicks off processing in a new appdomain / thread. This thread will execute 10 to 16 database queries simultaneously. When I run 30 of these jobs one after another then a random one or two of the jobs will crash with the timeout error.
I changed the connection string to turn off Connection Pooling with Pooling=false and then the error changed to the following. This gets thrown 3 or 4 times inside an aggregate exception, since the connections are happening inside a Parallel.For
优化您在远程服务器上执行的查询总是有帮助的。对每个查询进行计时并查找长时间运行的查询。如果您只是进行读取，请使用 (NOLOCK) 有关 SELECT 语句的提示。这对我来说是救星。只需阅读它即可确保它适合您的应用程序。如果您有权访问远程数据库，请确保索引不会碎片化。这将导致查询执行速度大幅减慢。确保重建/重新组织索引作为 SQL 维护计划的一部分。在适当的地方添加新索引。
延长超时可能会使情况变得更糟。如果您让查询运行更长时间，则可能会有更多查询超时。超时是为了保护服务器和其他访问它的客户端。稍微提高一点并不是什么大问题，但您不希望查询长时间运行而导致服务器崩溃。
Optimizing the queries you are executing on the remote server will always help. Time each query and look for long running ones. If you are just doing reads then use the (NOLOCK) hint on the SELECT statements. This was a life saver for me. Just read up on it to make sure it is appropriate in your application. If you have access to the remote database make sure the indexes are not to fragmented. This will cause a major slow down in query execution. Make sure indexes are rebuilt/reorganized as part of the SQL maintenance plan. Add new indexes where appropriate.
Extending the timeout may make matters worse. If you let queries run longer then, potentially, more queries will time out. The timeout is there to protect the server and other clients accessing it. Bumping it up a little is not a huge deal but you don't want queries running for a long time killing the server.