Canceling query with while loop hangs forever

Question

I am trying to use query cancellation (via cancellation tokens) to cancel a long-running complex query. I have found that in some cases not only does cancellation fail to halt the query but also the call to CancellationToken.Cancel() hangs indefinitely. Here is a simple repro that replicates this behavior (can be run in LinqPad):

void Main()
{   
    var cancellationTokenSource = new CancellationTokenSource();
    var blocked = RunSqlAsync(cancellationTokenSource.Token);
    blocked.Wait(TimeSpan.FromSeconds(1)).Dump(); // false (blocked in SQL as expected)
    cancellationTokenSource.Cancel(); // hangs forever?!
    Console.WriteLine("Finished calling Cancel()");
    blocked.Wait();
}

public async Task RunSqlAsync(CancellationToken cancellationToken)
{
    var connectionString = new SqlConnectionStringBuilder { DataSource = @".\sqlexpress", IntegratedSecurity = true, Pooling = false }.ConnectionString;
    using (var connection = new SqlConnection(connectionString))
    {
        await connection.OpenAsync().ConfigureAwait(false);

        using (var command = connection.CreateCommand())
        {
            command.CommandText = @"
                WHILE 1 = 1
                BEGIN
                    DECLARE @x INT = 1
                END
            ";
            command.CommandTimeout = 0;
            Console.WriteLine("Running query");
            await command.ExecuteNonQueryAsync(cancellationToken).ConfigureAwait(false);
        }
    }
}

Interestingly, the same query run in SqlServer Management Studio cancels instantly via the "Cancel Executing Query" button.

Is there some caveat to query cancellation where it cannot cancel tight WHILE loops?

My version of SqlServer:

Microsoft SQL Server 2012 - 11.0.2100.60 (X64) Feb 10 2012 19:39:15 Copyright (c) Microsoft Corporation Express Edition (64-bit) on Windows NT 6.2 (Build 9200: )

I am running on Windows 10, and .NET's Environment.Version is 4.0.30319.42000.

EDIT

Some additional information:

Here is the stack trace pulled from Visual Studio when cancellationToken.Cancel() hangs:

Another thread is stuck here:

Additionally, I tried updating to SqlServer Express 2017 and I am seeing the same behavior.

EDIT

I've filed this as a bug with corefx: https://github.com/dotnet/corefx/issues/26623

Answer 1

I can reproduce the issue in a console application. (The code in the question was code from LINQPad.)

I'm going to make this an answer and say that this is a bug in ADO.NET. ADO.NET should send a query cancellation signal to SQL Server. I can see from the CPU usage, that SQL Server continues executing the loop. Therefore, it did not receive cancellation from the client. We also know that SSMS is able to cancel this loop.

While the loop is running I can see that the console app is using 50% of one CPU core and receiving data from SQL Server at 70MB/sec. I do not know what data this is. It might be ROWCOUNT information or something related.

I think the bug is related to the fact that the loop is continuously sending data so that ADO.NET never has an opportunity to send the cancellation. It's still a bug and it would be a community service if you reported it. You can link to this question.

If the loop is throttled using ...

            WHILE 1 = 1
            BEGIN
                DECLARE @x INT = 1
                WAITFOR DELAY '00:00:01' --new
            END

... then cancellation is quick.

Also, you can generally not rely on cancellation being quick. If the network dropped it might take 30sec for the client to notice this and throw.

Therefore you need to code your program so that it continues executing and not wait for the query to finish. It could look like this:

var queryTask = ...;
var cancellationToken = ...;

await Task.WhenAll(queryTask, cancellationToken);

That way cancellation always looks instantaneous. Make sure that resources are still disposed. All SQL interaction should be encapsulated in queryTask so that it simply continues in the background and eventually cleans up.