Sqlclient: Canceling SQL Server query with while loop hangs forever

[madelson]

See StackOverflow post (https://stackoverflow.com/questions/48461567/canceling-query-with-while-loop-hangs-forever?noredirect=1#comment83955305_48461567) here for a full description of the issue.

Essentially, the issue is that for a certain query I am finding that calling CancellationTokenSource.Cancel() hangs indefinitely instead of canceling the query. The same query cancels instantly in SQL Server Management Studio. Here is code the reproduces the issue:

void Main()
{   
    var cancellationTokenSource = new CancellationTokenSource();
    var blocked = RunSqlAsync(cancellationTokenSource.Token);
    Console.WriteLine(blocked.Wait(TimeSpan.FromSeconds(1))); // 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);
        }
    }
}

[EgorBo]

[madelson]

[saurabh500]

[madelson]

[madelson]

[saurabh500]

[madelson]

[madelson]

[divega]

[madelson]

[divega]

[Wraith2]

[Samirat]

[Wraith2]

[madelson]