Canceling SQL Server query with CancellationToken

Question

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();
        }
    }

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();
    }

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.

EDIT: 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.

EDIT: 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;

Solution

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

If you change

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

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

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.

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();
}

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.

Comments

[loop]

can you try running your TestSqlServerCancelSprocExecution() after canceling the Task first.

[Venki]

@Dan - Could this because of the current context - I am guessing here. Please try cmd.ExecuteNonQueryAsync(cts.Token).ConfigureAwait(false);

[Dan Hermann]

@JohnSaunders I didn't read that anywhere. I assumed (obviously incorrectly) that because CancellationTokens in the TPL are explicitly cooperative that SqlCommand would employ the same mechanism for cancellation in an async method when it receives a request for cancellation via a CancellationToken as it does when it receives a request for cancellation via the Cancel method in a synchronous method.

[Rhys Jones]

For anyone else with this problem there is a known bug in async cancel, see github.com/dotnet/SqlClient/issues/44.

[Dan Hermann]

This works great. It's annoying that the presence of the WITH NOWAIT clause was the culprit, but at least there's a pretty straightforward workaround.

[Hamza_L]

@CoderDennis, I'm using this solution, but an exception is thrown when I cancel : "SqlException, ... operation canceled by user". Do you know why ?

[CoderDennis]

@Hamza_L it's been a while since I looked at this code, but I think that's why I wrapped the task run in the try...catch block. If you cancel, it will raise the exception.

[Kidquick]

@Hamza_L The "operation cancelled by user" exception is exactly what should happen since the cancel operation was triggered by the user clicking the cancel button. If you don't want it to raise the exception then just swallow it.

[granadaCoder]

The best "swallow" code I could come up with was : catch (SqlException sqlex) { switch (sqlex.ErrorCode) { case -2146232060: /* -2146232060 is fairly generic , check .Message too */ if (!sqlex.Message.Contains("cancelled")) { throw; } break; default: throw; } }

[user890332]

You need to put the try catch in the await task or it throws an error and stops the program. I put the try catch around the cmd.ExecuteNonQuery();