Cancel Long Running Query using Cancellation Token AND SqlCommand.Cancel()

Question

I need to be able to cancel a long running query programmatically through our application. The code below will kick off a long running query and give control back to the main thread. At any arbitrary time, a user could choose to cancel the query. It both cancels the Task as well as cancels the query in SQL Server. I can check the status of the query in SQL Server with select * from sys.query_store_runtime_stats to verify that the query was in fact aborted. This is important as I need to make sure it's not just canceled in the app but in the database as well.

The code below does this, but I'm hung up on the line where I Register the cmd.Cancel method with the cancellationToken, cancellationToken.Register(cmd.Cancel); Is there a potential issue since I'm referencing a variable created inside the scope of the Task.Run from the Main method(outside the Task.Run scope)?

Also, I'm including the cmd.Cancel bit so that the SQL query actually gets cancelled and not just the task that kicked it off. Is there a better way to ensure that the long running SQL query currently being executed in the database is aborted? (again, the code below does this, just wondering if there is a better solution for cancelling a SQL query from the c# code that initiated the SQL query)

I'm using .NET Core 3.1 preview 3 and SQL Server 2017 with the code posted below.

using Microsoft.Data.SqlClient;
using System;
using System.Threading;
using System.Threading.Tasks;
public class Program
{
 public static void Main(string[] args)
 {
 var tokenSource = new CancellationTokenSource();
 var cancellationToken = tokenSource.Token;
 Task.Run(async () =>
 {
 await using var cn = new SqlConnection(CONNECTION_STRING);
 await using var cmd = new SqlCommand(LONG_RUNNING_SQL, cn);
 cancellationToken.Register(cmd.Cancel); // is this bad??
 await cn.OpenAsync(cancellationToken);
 await using var reader = await cmd.ExecuteReaderAsync(cancellationToken);
 while (await reader.ReadAsync())
 {
 }
 }, cancellationToken);
 Console.WriteLine("Press z+<Enter> to Stop");
 while (true)
 {
 if (Console.Read() == 'z')
 {
 tokenSource.Cancel();
 break;
 }
 }
 }
 private const string CONNECTION_STRING = "server=.;database=scratch;trusted_connection=true;";
 private const string LONG_RUNNING_SQL = @"
Declare @start bigint, @end bigint
Select @start=1, @end=999999
;With NumberSequence( Number ) as
(
 Select @start as Number
 union all
 Select Number + 1
 from NumberSequence
 where Number < @end
)
Select * From NumberSequence Option (MaxRecursion 0)
";
}

• IEatBagels: Hello Chris, could you give us more details regarding your code?

Answer 1

It both cancels the Task as well as cancels the query in SQL Server. I can check the status of the query in SQL Server with select * from sys.query_store_runtime_stats to verify that the query was in fact aborted. This is important as I need to make sure it's not just canceled in the app but in the database as well.

Baaaack up a few steps. Your strategy to rely on proof of query completion by looking in a secondary table seems strange to me. If your long-running query were an update, insert etc., you would want to consider

• Running your query in a transaction that does not have any kind of auto-commit enabled
• Only committing at the end, once you're sure that there will be no desire for cancellation
• If cancellation occurs before the commit, then you can be sure that the query was cancelled with no effect on the database

But that's not required, because your long-running query is only a select. Is there a specific reason that you don't trust Cancel to do what it's designed to do? Doing the query_store_runtime_stats check manually as a part of development testing is fine, as long as you aren't baking it in programmatically.

Answer 2

1) You can listen for requests for your application to close without making your own loop, and without having to push the logic of your application into another task

var cts = new CancellationTokenSource();
AppDomain.CurrentDomain.ProcessExit += (sender, eventArgs) => cts.Cancel();
Console.CancelKeyPress += (sender, eventArgs) => { cts.Cancel(); eventArgs.Cancel = true; };

So you don't need Task.Run anymore. This is also a better, more consistent check for closing that is better implemented than anything you can hand roll in a main method.

(fyi. Unless you choose to leak these (and know what that means) you'll need to dispose cts and unregistered the handlers)

2) I almost certain passing a cancellation token to ExecuteReaderAsync is sufficient to cancel the query. You can verify this, but I think that cmd.Cancel for for people using ExecuteReader (non-async), which doesn't have a cancellationtoken.

3) Some other comments. It looks like you're not actually reading anything, is this code incomplete? A simple while loop is also probably not a great event loop (eg a delay would be good) but you don't need to worry about that anyway.

• Triynko: 2) Absolutely not. I ended up here because I'm 100% certain passing a cancellation token to ExecuteReaderAsync will NOT cancel a query. I'm calling a long-running sproc and it won't work. In fact, if you pass a cancellationToken to ExecuteReaderAsync, it seems to BLOCK other registered cancellation callbacks on the cancellation token. So, for example, if you register a callback on the token that invokes SqlCommand.Cancel (attempting to manually cancel the command) it won't be called until AFTER the callback ExecuteReader registers finishes, and that one blocks until the command completes.
• Triynko: There's literally no way to cancel a SQL query in .NET that's effective from code. Cancelling the SqlCommand doesn't work. Closing the SqlConnection does work. Cancelling the passed CancellationToken doesn't work. It's insane.