Отмена запроса SQL Server с помощью CancellationToken

У меня есть длительная хранимая процедура в SQL Server, которую мои пользователи должны иметь возможность отменить. Я написал небольшое тестовое приложение следующим образом, которое демонстрирует, что метод SqlCommand.Cancel() работает довольно хорошо:

    private SqlCommand cmd;
    private void TestSqlServerCancelSprocExecution()
    {
        TaskFactory f = new TaskFactory();
        f.StartNew(() =>
            {
              using (SqlConnection conn = new SqlConnection("connStr"))
              {
                conn.InfoMessage += conn_InfoMessage;
                conn.FireInfoMessageEventOnUserErrors = true;
                conn.Open();

                cmd = conn.CreateCommand();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "dbo.[CancelSprocTest]";
                cmd.ExecuteNonQuery();
              }
           });
    }

    private void cancelButton_Click(object sender, EventArgs e)
    {
        if (cmd != null)
        {
            cmd.Cancel();
        }
    }

При вызове cmd.Cancel() я могу проверить, что основная хранимая процедура перестает выполняться практически немедленно. Учитывая, что я использую шаблон async/await довольно сильно в своем приложении, я надеялся, что методы async на SqlCommand, которые принимают параметры CancellationToken, будут работать одинаково хорошо. К сожалению, я обнаружил, что вызов Cancel() в CancellationToken заставил обработчик события InfoMessage больше не вызываться, но основная хранимая процедура продолжала выполняться. Мой тестовый код для асинхронной версии:

    private SqlCommand cmd;
    private CancellationTokenSource cts;
    private async void TestSqlServerCancelSprocExecution()
    {
        cts = new CancellationTokenSource();
        using (SqlConnection conn = new SqlConnection("connStr"))
        {
            conn.InfoMessage += conn_InfoMessage;
            conn.FireInfoMessageEventOnUserErrors = true;
            conn.Open();

            cmd = conn.CreateCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "dbo.[CancelSprocTest]";
            await cmd.ExecuteNonQueryAsync(cts.Token);
        }
    }

    private void cancelButton_Click(object sender, EventArgs e)
    {
        cts.Cancel();
    }

Я пропустил что-то в том, как должен работать CancellationToken? Я на .NET 4.5.1 и SQL Server 2012, если это имеет значение.

EDIT: я переписал тестовое приложение в качестве консольного приложения, если контекст синхронизации был фактором, и я вижу то же поведение - вызов CancellationTokenSource.Cancel() не останавливает выполнение базовой хранимой процедуры.

EDIT: Здесь хранится тело хранимой процедуры, которую я вызываю в случае, если это имеет значение. Он вставляет записи и выводит результаты с интервалом в одну секунду, чтобы было легко увидеть, предприняли ли попытки аннулирования оперативно.

WHILE (@loop <= 40)
BEGIN

  DECLARE @msg AS VARCHAR(80) = 'Iteration ' + CONVERT(VARCHAR(15), @loop);
  RAISERROR (@msg,0,1) WITH NOWAIT;
  INSERT INTO foo VALUES (@loop);
  WAITFOR DELAY '00:00:01.01';

  SET @loop = @loop+1;
END;

Ответ 1

Посмотрев, что делает ваша хранимая процедура, кажется, что она каким-то образом блокирует отмену.

Если вы измените

RAISERROR (@msg,0,1) WITH NOWAIT;

чтобы удалить предложение WITH NOWAIT, тогда отмена будет работать, как ожидалось. Однако это предотвращает запуск событий InfoMessage в реальном времени.

Вы можете отслеживать ход длительной хранимой процедуры каким-либо другим способом или регистрировать для отмены маркера и вызывать cmd.Cancel(), так как вы знаете, что это работает.

Еще одно замечание: с .NET 4.5 вы можете просто использовать Task.Run вместо создания экземпляра TaskFactory.

Итак, здесь рабочее решение:

private CancellationTokenSource cts;
private async void TestSqlServerCancelSprocExecution()
{
    cts = new CancellationTokenSource();
    try
    {
        await Task.Run(() =>
        {
            using (SqlConnection conn = new SqlConnection("connStr"))
            {
                conn.InfoMessage += conn_InfoMessage;
                conn.FireInfoMessageEventOnUserErrors = true;
                conn.Open();

                var cmd = conn.CreateCommand();
                cts.Token.Register(() => cmd.Cancel());
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "dbo.[CancelSprocTest]";
                cmd.ExecuteNonQuery();
            }
       });
    }
    catch (SqlException)
    {
        // sproc was cancelled
    }
}

private void cancelButton_Click(object sender, EventArgs e)
{
    cts.Cancel();
}

В моем тестировании мне пришлось обернуть ExecuteNonQuery в Task, чтобы работать cmd.Cancel(). Если я использовал ExecuteNonQueryAsync, даже не передав ему токен, система блокируется на cmd.Cancel(). Я не уверен, почему этот случай, но обертывание синхронного метода в Задаче обеспечивает аналогичное использование.