使用 CancellationToken 取消 SQL Server 查询 [英] Canceling SQL Server query with CancellationToken

问题描述

我在 SQL Server 中有一个长时间运行的存储过程，我的用户需要能够取消该过程.我编写了一个小型测试应用程序，如下所示，它演示了 SqlCommand.Cancel() 方法非常有效:

I have a long-running stored procedure in SQL Server that my users need to be able to cancel. I have written a small test app as follows that demonstrates that the SqlCommand.Cancel() method works quite nicely:

    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 模式，我希望 SqlCommand 上采用 CancellationToken 参数的异步方法也能同样有效地工作.不幸的是，我发现在 CancellationToken 上调用 Cancel() 导致 InfoMessage 事件处理程序不再被调用，但底层存储过程继续执行.我的异步版本测试代码如下:

Upon calling cmd.Cancel(), I can verify that the underlying stored procedure stops executing essentially immediately. Given that I use the async/await pattern quite heavily in my application, I was hoping that the async methods on SqlCommand that take CancellationToken parameters would work equally well. Unfortunately, I found that calling Cancel() on the CancellationToken caused the InfoMessage event handler to no longer be called, but the underlying stored procedure continued to execute. My test code for the async version follows:

    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，以防万一.

Am I missing something in how the CancellationToken is supposed to work? I'm on .NET 4.5.1 and SQL Server 2012 in case it matters.

我将测试应用程序重写为控制台应用程序，以防同步上下文是一个因素并且我看到相同的行为——CancellationTokenSource.Cancel() 的调用不会停止执行底层存储过程.

I rewrote the test app as a console app in case the synchronization context was a factor and I see the same behavior -- the invocation of CancellationTokenSource.Cancel() does not stop the execution of the underlying stored procedure.

这是我正在调用的存储过程的主体，以防万一.它以一秒为间隔插入记录并打印结果，以便于查看取消尝试是否及时生效.

Here's the body of the stored procedure I'm calling in case that matters. It inserts records and prints results at one-second intervals to make it easy to see whether cancellation attempts took effect promptly.

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;

推荐答案

在查看您的存储过程正在做什么之后，它似乎以某种方式阻止了取消.

After looking at what your stored procedure is doing, it appears that it is somehow blocking the cancellation.

如果你改变了

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

删除 WITH NOWAIT 子句，然后取消按预期工作.但是，这会阻止 InfoMessage 事件实时触发.

to remove the WITH NOWAIT clause, then the cancellation works as expected. However, this prevents the InfoMessage events from firing in real time.

您可以通过其他方式跟踪长时间运行的存储过程的进度，或者注册令牌取消并调用 cmd.Cancel()，因为您知道这样做有效.

You could track progress of the long running stored procedure some other way or register for the token cancellation and call cmd.Cancel() since you know that works.

要注意的另一件事是，使用 .NET 4.5，您可以只使用 Task.Run 而不是实例化 TaskFactory.

One other thing to note, with .NET 4.5, you can just use Task.Run instead of instantiating a TaskFactory.

这里有一个可行的解决方案:

So here's a working solution:

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().我不确定为什么会这样，但是将同步方法包装在 Task 中提供了类似的用法.

In my testing of this, I had to wrap ExecuteNonQuery in a Task in order for cmd.Cancel() to work. If I used ExecuteNonQueryAsync, even without passing it a token, then the system would block on cmd.Cancel(). I'm not sure why that's the case, but wrapping the synchronous method in a Task provides a similar usage.

这篇关于使用 CancellationToken 取消 SQL Server 查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！