c# - 很久之后SqlDependency Change/Error/Client error

Question

我有一个 Windows 服务在使用 SqlDependency 类监听表中的插入。

它可以正常工作几天，然后突然停止工作。

在正常情况下，我会收到更改事件

e.Type = SqlNotificationType.Change
e.Info = SqlNotificationInfo.Insert
e.Source = SqlNotificationSource.Data

如果没有任何变化，我会每 3600 秒收到一次超时事件

e.Type = SqlNotificationType.Change
e.Info = SqlNotificationInfo.Error
e.Source = SqlNotificationSource.Timeout

或者(不知道为什么会有两个不同的超时事件)

e.Type = SqlNotificationType.Change
e.Info = SqlNotificationInfo.Unknown
e.Source = SqlNotificationSource.Timeout

这可以工作一个星期或更长时间，但突然间，我不再收到更改事件，而是每 60 秒收到一个事件

e.Type = SqlNotificationType.Change
e.Info = SqlNotificationInfo.Error
e.Source = SqlNotificationSource.Client

SqlNotificationSource.Client 的 MSDN 文档说

A client-initiated notification occurred, such as a client-side time-out or as a result of attempting to add a command to a dependency that has already fired.

我认为这意味着创建依赖项时发生了超时。

相同的代码一直在运行，看起来像这样:

private void CreateDependency() {
    using (var connection = new SqlConnection(_connectionString)) {
        connection.Open();

        var command = new SqlCommand();
        command.CommandText = "SELECT ...";
        command.Connection = connection;

        new SqlDependency(command, "ServiceName", DependencyTimeout).OnChange += OnChange;

        command.ExecuteNonQuery();
    }
}

private void OnChange(object sender, SqlNotificationEventArgs e) {
    ((SqlDependency)sender).OnChange -= OnChange;

    if (e.Type == SqlNotificationType.Change && e.Info == SqlNotificationInfo.Insert) {
        _changeWorkerNotifier.Set(); // AutoResetEvent
    }

    CreateDependency();
}

如果我重新启动我的服务，它会再次正常工作。

我做了一些调查，发现错误似乎是在服务器上的计划备份之后开始的(每天都会发生)。有另一个应用程序，我大约在同一时间收到类似

的错误

A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The semaphore timeout period has expired.)

我最初的猜测是 SqlDependency.Start() 创建了一个到服务器的连接，该连接在计划备份期间出现故障并且永远不会恢复。但是第一行在MSDN documentation说

The SqlDependency listener will restart when an error occurs in the SQL Server connection.

关于如何解决这个问题有什么想法吗？
(我当然可以让服务失败并让服务管理器重新启动它。问题是该服务还做其他需要正确关闭的事情所以我不能只做 Environment.Exit(-1) 从事件处理程序。)

Answer 1

找到原因并解决。

首先，我发现那行

The SqlDependency listener will restart when an error occurs in the SQL Server connection.

仅出现在 .Net 4 文档中。

一些测试表明，这不仅仅是文档的更改!使用 CLR4 运行时不会出现 Client/Error 事件。

所以原因是连接错误，它在 .Net 4 中的 SqlDependency 内部处理，但在早期版本中没有。

在 .Net 2-3.5 中，可以使用 SqlDependency.Stop()/SqlDependency.Start() 在错误后恢复。

不太喜欢停止/启动解决方案，因为如果连接错误不可恢复，我需要逻辑来中断循环。我决定在出现错误时停止服务并让服务管理器重新启动它(这使得问题在事件日志等中可见)。

我的处理程序现在看起来像这样:

private void OnChange(object sender, SqlNotificationEventArgs e) {
    ((SqlDependency)sender).OnChange -= OnChange;

    if (e.Source == SqlNotificationSource.Timeout) {
        // just restart notification
    }
    else if (e.Source != SqlNotificationSource.Data) {
        Logger.Error("Unhandled change notification {0}/{1} ({2})", e.Type, e.Info, e.Source);
        ServiceRunner.ShutDown(true);
    }
    else if (e.Type == SqlNotificationType.Change && e.Info == SqlNotificationInfo.Insert) {
        _changeWorkerNotifier.Set(); // AutoResetEvent
    }
    else {
        Logger.Log("Ignored change notification {0}/{1} ({2})", e.Type, e.Info, e.Source);
    }

    CreateDependency();
}

** 编辑 **

这是我在启动时调用的代码

private void ClearOldSubscriptions() {
    using (var connection = new SqlConnection(_connectionString))
    using (var command = new SqlCommand()) {
        string sql =
            ////@"DECLARE @UniqueTimeout AS int = 3586; " +
            @"DECLARE @SubscriptionId AS int; " +
            @"DECLARE @Sql AS varchar(max); " +
            @"DECLARE SubscriptionCursor CURSOR LOCAL FAST_FORWARD " +
            @"    FOR " +
            @"        SELECT id " +
            @"        FROM sys.dm_qn_subscriptions " +
            @"      WHERE database_id = DB_ID() " +
            @"            AND timeout = @UniqueTimeout " +
            @"OPEN SubscriptionCursor; " +
            @"FETCH NEXT FROM SubscriptionCursor INTO @SubscriptionId; " +
            @"WHILE @@FETCH_STATUS = 0 " +
            @"BEGIN " +
            @"    SET @Sql = 'KILL QUERY NOTIFICATION SUBSCRIPTION ' + CONVERT(varchar, @SubscriptionId); " +
            @"    EXEC(@Sql); " +
            @" " +
            @"    FETCH NEXT FROM SubscriptionCursor INTO @SubscriptionId; " +
            @"END";

        command.Connection = connection;
        command.CommandType = CommandType.Text;
        command.CommandText = sql;
        command.Parameters.Add("@UniqueTimeout", SqlDbType.Int).Value = DependencyTimeout;

        connection.Open();

        command.ExecuteNonQuery();
    }
}

private void ClearNotificationQueue() {
    using (var connection = new SqlConnection(_connectionString))
    using (var command = new SqlCommand()) {
        string sql = 
            @"DECLARE @Conversation AS uniqueidentifier; " +
            @"DECLARE ConversationCursor CURSOR LOCAL FAST_FORWARD  " +
            @"    FOR " +
            @"        SELECT conversation_handle  " +
            @"        FROM {@Queue} " +
            @"     " +
            @"OPEN ConversationCursor; " +
            @"FETCH NEXT FROM ConversationCursor INTO @Conversation; " +
            @"WHILE @@FETCH_STATUS = 0  " +
            @"BEGIN " +
            @"    END CONVERSATION @Conversation WITH CLEANUP; " +
            @" " +
            @"    FETCH NEXT FROM ConversationCursor INTO @Conversation; " +
            @"END " +
            @"";
        sql = sql.Replace("{@Queue}", NotificationQueue);

        command.Connection = connection;
        command.CommandType = CommandType.Text;
        command.CommandText = sql;

        connection.Open();

        command.ExecuteNonQuery();
    }
}