sql-server - 为什么在这种情况下忽略 ConnectTimeout？

Question

运行此代码时:

static void Main(string[] args)
{
    SqlConnectionStringBuilder csb = new SqlConnectionStringBuilder();
    csb.DataSource = @"8.8.8.8"; // some inaccessible ip address
    csb.InitialCatalog = "Tempdb";
    csb.IntegratedSecurity = true;
    csb.ConnectTimeout = 1;
    DateTime start = DateTime.Now;
    try
    {
        new SqlConnection(csb.ToString()).Open();
    }
    catch (Exception ex)
    {
        Console.WriteLine(ex.Message);
    }
    finally
    {
        Console.Write(string.Format("{0} seconds", DateTime.Now.Subtract(start).TotalSeconds));
    }
}

我得到这个结果:

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)
47.6097605 seconds

我预计 ConnectTimeout 属性会产生影响。那么为什么在这种情况下会忽略 ConnectTimeout 属性呢？ (我也很好奇其他人看到的时间)。

更新:我注意到以下额外的行将时间跨度缩短到 26 秒？？？

csb.FailoverPartner=@"9.9.9.9";

Answer 1

编辑 : 尽管这看起来很奇怪，但我在反编译代码的深处放置了一个断点，并使用 --> 将超时设置为 1。有效 <-- 服务器名称，我让我的断点在那里然后继续，它给出了超时过期异常 正如预期的那样因此，似乎 ConnectTimeout 仅在能够解析服务器并等待连接时才适用。它不影响解析要连接的服务器。我认为所经历的时间是用于服务器解析，而不是用于“连接”的实际行为。至少这是我目前的假设。

我用反射器来看看被子下面发生了什么。也许有人来自 微软 可以在这里帮助我们，因为我还发现 ConnectTimeout 似乎对初始连接没有影响。

无论如何在内部建立连接，我认为以下方法被调用，在这个序列中:

  internal DbConnectionInternal CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options)
    {
      DbConnectionPoolGroupProviderInfo providerInfo = pool.PoolGroup.ProviderInfo;
      DbConnectionInternal internal2 = this.CreateConnection(options, providerInfo, pool, owningConnection);
      if (internal2 != null)
      {
        this.PerformanceCounters.HardConnectsPerSecond.Increment();
        internal2.MakePooledConnection(pool);
      }
      Bid.Trace("<prov.DbConnectionFactory.CreatePooledConnection|RES|CPOOL> %d#, Pooled database connection created.\n", this.ObjectID);
      return internal2;
    }

进而:

 protected override DbConnectionInternal CreateConnection(DbConnectionOptions options, object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection)
    {
      string instanceName;
      SqlConnectionString str = (SqlConnectionString) options;
      if (str.ContextConnection)
      {
        return this.GetContextConnection(str, poolGroupProviderInfo, owningConnection);
      }
      bool redirectedUserInstance = false;
      DbConnectionPoolIdentity current = null;
      if (str.IntegratedSecurity)
      {
        if (pool != null)
        {
          current = pool.Identity;
        }
        else
        {
          current = DbConnectionPoolIdentity.GetCurrent();
        }
      }
      if (!str.UserInstance)
      {
        goto Label_00F1;
      }
      redirectedUserInstance = true;
      if ((pool == null) || ((pool != null) && (pool.Count <= 0)))
      {
        using (SqlInternalConnectionTds tds = null)
        {
          SqlConnectionString connectionOptions = new SqlConnectionString(str, str.DataSource, true, false);
          tds = new SqlInternalConnectionTds(current, connectionOptions, null, "", null, false);
          instanceName = tds.InstanceName;
          if (!instanceName.StartsWith(@"\\.\", StringComparison.Ordinal))
          {
            throw SQL.NonLocalSSEInstance();
          }
          if (pool != null)
          {
            SqlConnectionPoolProviderInfo info2 = (SqlConnectionPoolProviderInfo) pool.ProviderInfo;
            info2.InstanceName = instanceName;
          }
          goto Label_00DB;
        }
      }
      SqlConnectionPoolProviderInfo providerInfo = (SqlConnectionPoolProviderInfo) pool.ProviderInfo;
      instanceName = providerInfo.InstanceName;
    Label_00DB:
      str = new SqlConnectionString(str, instanceName, false, null);
      poolGroupProviderInfo = null;
    Label_00F1:
      return new SqlInternalConnectionTds(current, str, poolGroupProviderInfo, "", (SqlConnection) owningConnection, redirectedUserInstance);
    }

进而:

 internal SqlInternalConnectionTds(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, object providerInfo, string newPassword, SqlConnection owningObject, bool redirectedUserInstance) : base(connectionOptions)
    {
      this._instanceName = string.Empty;
      if (connectionOptions.UserInstance && InOutOfProcHelper.InProc)
      {
        throw SQL.UserInstanceNotAvailableInProc();
      }
      this._identity = identity;
      this._poolGroupProviderInfo = (SqlConnectionPoolGroupProviderInfo) providerInfo;
      this._fResetConnection = connectionOptions.ConnectionReset;
      if (this._fResetConnection)
      {
        this._originalDatabase = connectionOptions.InitialCatalog;
        this._originalLanguage = connectionOptions.CurrentLanguage;
      }
      RuntimeHelpers.PrepareConstrainedRegions();
      try
      {
        TimeoutTimer timeout = TimeoutTimer.StartSecondsTimeout(connectionOptions.ConnectTimeout);
        this.OpenLoginEnlist(owningObject, timeout, connectionOptions, newPassword, redirectedUserInstance);
      }
      catch (OutOfMemoryException)
      {
        base.DoomThisConnection();
        throw;
      }
      catch (StackOverflowException)
      {
        base.DoomThisConnection();
        throw;
      }
      catch (ThreadAbortException)
      {
        base.DoomThisConnection();
        throw;
      }
      if (Bid.AdvancedOn)
      {
        Bid.Trace("<sc.SqlInternalConnectionTds.ctor|ADV> %d#, constructed new TDS internal connection\n", base.ObjectID);
      }
    }

然后，默认情况下(没有故障转移伙伴):

private void LoginNoFailover(ServerInfo serverInfo, string newPassword, bool redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, TimeoutTimer timeout)
    {
      if (Bid.AdvancedOn)
      {
        Bid.Trace("<sc.SqlInternalConnectionTds.LoginNoFailover|ADV> %d#, host=%ls\n", base.ObjectID, serverInfo.UserServerName);
      }
      int num = 100;
      this.ResolveExtendedServerName(serverInfo, !redirectedUserInstance, owningObject);
      while (true)
      {
        if (this._parser != null)
        {
          this._parser.Disconnect();
        }
        this._parser = new TdsParser(base.ConnectionOptions.MARS, base.ConnectionOptions.Asynchronous);
        try
        {
          this.AttemptOneLogin(serverInfo, newPassword, true, timeout, owningObject);
          break;
        }
        catch (SqlException exception)
        {
          if (((this._parser == null) || (this._parser.State != TdsParserState.Closed)) || (this.IsDoNotRetryConnectError(exception.Number) || timeout.IsExpired))
          {
            throw;
          }
          if (timeout.MillisecondsRemaining <= num)
          {
            throw;
          }
        }
        if (this.ServerProvidedFailOverPartner != null)
        {
          this.LoginWithFailover(true, serverInfo, this.ServerProvidedFailOverPartner, newPassword, redirectedUserInstance, owningObject, connectionOptions, timeout);
          return;
        }
        if (Bid.AdvancedOn)
        {
          Bid.Trace("<sc.SqlInternalConnectionTds.LoginNoFailover|ADV> %d#, sleeping %d{milisec}\n", base.ObjectID, num);
        }
        Thread.Sleep(num);
        num = (num < 500) ? (num * 2) : 0x3e8;
      }
      if (this.PoolGroupProviderInfo != null)
      {
        this.PoolGroupProviderInfo.FailoverCheck(this, false, connectionOptions, this.ServerProvidedFailOverPartner);
      }
      base.CurrentDataSource = serverInfo.UserServerName;
    } 

进而:

internal void Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, bool ignoreSniOpenTimeout, long timerExpire, bool encrypt, bool trustServerCert, bool integratedSecurity)
    {
      if (this._state == TdsParserState.Closed)
      {
        this._connHandler = connHandler;
        if (SNILoadHandle.SingletonInstance.SNIStatus != 0)
        {
          this.Errors.Add(this.ProcessSNIError(this._physicalStateObj));
          this._physicalStateObj.Dispose();
          this.ThrowExceptionAndWarning();
        }
        if (integratedSecurity)
        {
          this.LoadSSPILibrary();
          this._sniServerUserName = new byte[s_maxSSPILength];
          Bid.Trace("<sc.TdsParser.Connect|SEC> SSPI authentication\n");
        }
        else
        {
          Bid.Trace("<sc.TdsParser.Connect|SEC> SQL authentication\n");
        }
        byte[] instanceName = null;
        this._physicalStateObj.CreatePhysicalSNIHandle(serverInfo.ExtendedServerName, ignoreSniOpenTimeout, timerExpire, out instanceName, integratedSecurity, this._sniServerUserName, false, this._fAsync);
        if (this._physicalStateObj.Status != 0)
        {
          this.Errors.Add(this.ProcessSNIError(this._physicalStateObj));
          this._physicalStateObj.Dispose();
          Bid.Trace("<sc.TdsParser.Connect|ERR|SEC> Login failure\n");
          this.ThrowExceptionAndWarning();
        }
        this._server = serverInfo.ResolvedServerName;
        if (connHandler.PoolGroupProviderInfo != null)
        {
          connHandler.PoolGroupProviderInfo.AliasCheck(serverInfo.ResolvedServerName);
        }
        this._state = TdsParserState.OpenNotLoggedIn;
        this._physicalStateObj.SniContext = SniContext.Snix_PreLoginBeforeSuccessfullWrite;
        this._physicalStateObj.TimeoutTime = timerExpire;
        bool marsCapable = false;
        this.SendPreLoginHandshake(instanceName, encrypt);
        this._physicalStateObj.SniContext = SniContext.Snix_PreLogin;
        switch (this.ConsumePreLoginHandshake(encrypt, trustServerCert, out marsCapable))
        {
          case PreLoginHandshakeStatus.SphinxFailure:
            this._fMARS = false;
            this._physicalStateObj._sniPacket = null;
            this._physicalStateObj.SniContext = SniContext.Snix_Connect;
            this._physicalStateObj.CreatePhysicalSNIHandle(serverInfo.ExtendedServerName, ignoreSniOpenTimeout, timerExpire, out instanceName, integratedSecurity, this._sniServerUserName, false, this._fAsync);
            if (this._physicalStateObj.Status != 0)
            {
              this.Errors.Add(this.ProcessSNIError(this._physicalStateObj));
              Bid.Trace("<sc.TdsParser.Connect|ERR|SEC> Login failure\n");
              this.ThrowExceptionAndWarning();
            }
            break;

          case PreLoginHandshakeStatus.InstanceFailure:
            this._physicalStateObj.Dispose();
            this._physicalStateObj.SniContext = SniContext.Snix_Connect;
            this._physicalStateObj.CreatePhysicalSNIHandle(serverInfo.ExtendedServerName, ignoreSniOpenTimeout, timerExpire, out instanceName, integratedSecurity, this._sniServerUserName, true, this._fAsync);
            if (this._physicalStateObj.Status != 0)
            {
              this.Errors.Add(this.ProcessSNIError(this._physicalStateObj));
              Bid.Trace("<sc.TdsParser.Connect|ERR|SEC> Login failure\n");
              this.ThrowExceptionAndWarning();
            }
            this.SendPreLoginHandshake(instanceName, encrypt);
            if (this.ConsumePreLoginHandshake(encrypt, trustServerCert, out marsCapable) == PreLoginHandshakeStatus.InstanceFailure)
            {
              Bid.Trace("<sc.TdsParser.Connect|ERR|SEC> Login failure\n");
              throw SQL.InstanceFailure();
            }
            break;
        }
        if (this._fMARS && marsCapable)
        {
          this._sessionPool = new TdsParserSessionPool(this);
        }
        else
        {
          this._fMARS = false;
        }
      }
    }

我不确定这一切是如何拼凑起来的，但infiniteTimeout 似乎是真的。

不确定这是否有帮助，但我认为值得深入研究