SqlConnection即时超时

Question

编写一个非常简单的工具来ping SQL服务器并确保可以访问一串连接字符串，我遇到了一个非常奇怪的问题:在迭代几个连接字符串时，前六个连接字符串大约在我指定的几秒钟后超时(在这个特殊情况下是11秒)，并记录相应的尝试和异常当前墙时间，但是突然之间大约有十几个连接字符串瞬间超时，然后恢复正常超时。

这是在一台机器上运行的，它没有任何网络访问要测试的所有连接字符串中使用的IP。在日志中，如下所示：

2018-04-06 15:59:40     Connection name: 
2018-04-06 15:59:40     SQL Error:Connection Timeout Expired.  Th
or the server was unable to respond back in time.  The duration s
2018-04-06 15:59:40     Connection name: 
2018-04-06 15:59:40     SQL Error:Connection Timeout Expired.  Th
or the server was unable to respond back in time.  The duration s
2018-04-06 15:59:40     Connection name: 
2018-04-06 15:59:40     SQL Error:Connection Timeout Expired.  Th
or the server was unable to respond back in time.  The duration s
2018-04-06 15:59:40     Connection name: 
2018-04-06 15:59:40     SQL Error:Connection Timeout Expired.  Th
or the server was unable to respond back in time.  The duration s
2018-04-06 15:59:40     Connection name: 
2018-04-06 15:59:40     SQL Error:Connection Timeout Expired.  Th
or the server was unable to respond back in time.  The duration s
2018-04-06 15:59:40     Connection name: 
2018-04-06 15:59:40     SQL Error:Connection Timeout Expired.  Th
or the server was unable to respond back in time.  The duration s
2018-04-06 15:59:40     Connection name: 
2018-04-06 15:59:40     SQL Error:Connection Timeout Expired.  Th
or the server was unable to respond back in time.  The duration s
2018-04-06 15:59:40     Connection name: 
2018-04-06 15:59:40     SQL Error:Connection Timeout Expired.  Th
or the server was unable to respond back in time.  The duration s
2018-04-06 15:59:40     Connection name: 
2018-04-06 15:59:40     SQL Error:Connection Timeout Expired.  Th
or the server was unable to respond back in time.  The duration s
2018-04-06 15:59:40     Connection name: 
2018-04-06 15:59:40     SQL Error:Connection Timeout Expired.  Th
or the server was unable to respond back in time.  The duration s
2018-04-06 15:59:40     Connection name: 
2018-04-06 15:59:54     SQL Error:Connection Timeout Expired.  Th
or the server was unable to respond back in time.  The duration s
2018-04-06 15:59:54     Connection name: 
2018-04-06 15:59:54     SQL Error:Connection Timeout Expired.  Th
or the server was unable to respond back in time.  The duration s
2018-04-06 15:59:54     Connection name: 
2018-04-06 16:00:08     SQL Error:Connection Timeout Expired.  Th
or the server was unable to respond back in time.  The duration s
2018-04-06 16:00:08     Connection name: 
2018-04-06 16:00:22     SQL Error:Connection Timeout Expired.  Th
or the server was unable to respond back in time.  The duration s
2018-04-06 16:00:22     Connection name: 
2018-04-06 16:00:35     SQL Error:Connection Timeout Expired.  Th
or the server was unable to respond back in time.  The duration s
2018-04-06 16:00:35     Connection name: 
2018-04-06 16:00:49     SQL Error:Connection Timeout Expired.  Th
or the server was unable to respond back in time.  The duration s

看看在一堆即时超时之后，最后一个条目在15:59:40下记录了15:59:54 (这是正常的)，然后另一个连接立即超时，16:00:08之后的所有内容再次正常退出。

在我看来，这些代码完全是无辜的教科书：

foreach (ConnectionStringSettings cs in ConfigurationManager.ConnectionStrings)
{
    if (cs.Name.Equals("LocalSqlServer") && !args.Contains("NoSkipLocal", StringComparer.OrdinalIgnoreCase)) continue;

    SqlConnectionStringBuilder b = new SqlConnectionStringBuilder(cs.ConnectionString)
    {
        ConnectTimeout = timeout == 0 ? 10 : timeout
    };

    SqlConnection conn = new SqlConnection(b.ConnectionString);
    try
    {
        conn.Open();
        LogToConsole("Connection success", ConsoleColor.Green);
    }
    catch (SqlException sex)
    {
        LogToConsole("SQL Error:" + sex.Message, ConsoleColor.Red);
    }
    catch (Exception ex)
    {
        LogToConsole("Error:" + ex.Message, ConsoleColor.Red);
    }

static void LogToConsole(string message, ConsoleColor color = ConsoleColor.Gray)
{
    ConsoleColor fgColor = Console.ForegroundColor;
    Console.ForegroundColor = color;
    Console.WriteLine(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "\t" + message);
    Console.ForegroundColor = fgColor;
}

还有其他人在ADO.NET SqlConnection对象中经历过即时超时吗？我原以为连接失败很可能是瞬间发生的，但至少消息应该是不同的。但它与14秒左右的时间完全相同。

Answer 1

将Pooling = false添加到连接字符串生成器实例化解决了这个问题。现在，每个连接在抛出异常之前等待指定的秒数。

当连接池打开时，仅释放SqlConnection是不够的，ADO.NET池仍然保存到特定DB的一个条目，并存储最后一个错误状态，因此当下一个连接对象被实例化时，连接字符串指向同一连接字符串时，超时连接立即返回。

在我的特殊情况下，这是一种很好的行为，因为它为我节省了测试连接字符串的时间，但在现实世界中，它可能会带来严重的副作用。