Runtime: Async cancel DbCommand

[rgarrison12345]

I propose adding an asynchronous cancel method to DbCommand

API proposal on DbCommand

public virtual void CancelAsync(CancellationToken token = default)
{
throw new NotImplementedException();
}

[msftbot[bot]]

Tagging subscribers to this area: @roji, @ajcvickers
See info in area-owners.md if you want to be subscribed.

[roji]

@rgarrison12345 the way to cancel an asynchronous database operation, is to trigger the cancellation token passed to DbCommand.ExecuteReaderAsync, just like with any standard async operation. Why do you think a CancelAsync is needed?

[rgarrison12345]

Hello @roji, I'm familiar with cancellation token cancelling async database operation from the client code perspective. However does that cancel the operation on just the client C# execution? I'm thinking cancel async would send a kill command or equivalent to the server to also stop the query execution on the server. For example I am reading through a data reader, and don't need to read all records, I call cancel async to cancel the query on the server.

I might be wrong on this entirely, I read the documentation on DbCommand.Cancel and thought it would also be applicable to async operations.

[roji]

@rgarrison12345 what exactly DbCommand.Cancel does - and what the cancellation token does - isn't something that's defined by System.Data itself; it's entirely database- and provider-specific. ADO.NET is only an abstraction, and since databases can be very different in what they support and what they don't, it's not possible to specify at this level exactly what happens.

Having said that, I'd definitely expect good implementations of both DbCommand.Cancel and async cancellation to attempt to cancel the execution at the server (again, assuming that is supported by the given database system). Are there any docs anywhere that led you to believe that only the client C# execution would be cancelled by the token?

[rgarrison12345]

Hello @roji, there wasn't any documentation anywhere that led me to believe that. That was me saying there's possibility a provider may not implement like that. Like you were saying it's up to the provider for a good implementation. I've seen in most places the cancellation token calls the sync version of DbCommand.Cancel. I've seen some Microsoft documentation in the past that recommends async all the way up. Calling the sync version of Cancel after the token expired seemed out of line with that recommendation. But I suppose the sync version would need to be called in that scenario.

Maybe a better suggestion is to add a boolean property on DbCommand that indicates that the provider can cancel query execution on the server. As you mentioned the provider may not support that operation.

[roji]

I've seen in most places the cancellation token calls the sync version of DbCommand.Cancel.

Out of curiosity, can you point to who does this? It's true that in ADO.NET, the standard is for the default implementation of async APIs (which were added later) to call their sync counterparts. This means that unless providers provide a true async implementation, they effectively provider async-over-sync behavior (i.e. no async). This isn't problematic in itself, but I'm still unaware of providers hooking up async cancellation tokens to Dbcommand.Cancel or how they'd wire the resulting cancellation back to the token etc.

In any case, none of this seems to have anything to do with whether cancellation cancels the query in the database, or just client-side - that's totally orthogonal to sync vs. async.

Maybe a better suggestion is to add a boolean property on DbCommand that indicates that the provider can cancel query execution on the server. As you mentioned the provider may not support that operation.

What would be the purpose of that? How would code interacting with an ADO.NET provider change its behavior based on the value of that property? What exactly happens when cancellation is trigger - and whether the server execution is cancelled - seems like it should be documented by each provider.

[rgarrison12345]

Hello @roji, true the provider should have this well documented.

I'm working in a multi RDBMS environment and coding to the abstract classes. At runtime I will not know what provider is being loaded by my application, it's more configuration driven. In this case I will not know if the cancel operation is supported by the provider. I saw in the DbCommand source code there is no default implementation for Cancel method, which makes sense. The method must be overridden by derived type because it is abstract method. So in this case the provider may throw not implemented exception.

I understand your point, I might be looking for a solution in search of a problem :D

[roji]

Unfortunately ADO.NET doesn't (can indeed, cannot) fully abstract away the database with all its details. I believe that a properly-written provider would indeed take care of cancelling queries at the server, but if a provider doesn't, that's definitely to be taken up with them.

I'm going to go ahead and close this as not needed, but am happy to continue the conversation or to hear other ideas on this.

[mikependon]

Are there any docs anywhere that led you to believe that only the client C# execution would be cancelled by the token?

Hey @roji, I will try to see if I can prove this in SQL Server.

[roji]

@mikependon I'd certainly be interested in the results of your investigation, but I don't think it would impact this issue much - SqlClient is just one provider and its behavior doesn't really dictate what other providers should do in this instance.

Just to provide more information on why this is complex... In Npgsql, @vonzshik is completing long-awaited work that implements much better support for cancellation (and timeouts as well, incidentally). The new support - for both DbCommand.Cancel (sync) and cancellation tokens (async) - would first send a server-side PostgreSQL cancellation (so cancelling the query), and after a certain timeout, would break the socket client-side (important in case of a network partition). This shows that the same cancellation method can actually result in both server-side and client-side cancellation.

[vonzshik]

Isn't SqlServer checks by itself, if the client is still waiting for a query?

As for the async cancellation - is there really any point? Cancellation is supposed to be as fast as possible, and the request itself should be pretty small.

[roji]

@vonzshik

Isn't SqlServer checks by itself, if the client is still waiting for a query?

I have no idea what SqlClient or SqlServer do with regards to cancellation...

Cancellation is supposed to be as fast as possible, and the request itself should be pretty small.

Which request are you referring to? This discussion is about whether SqlClient implements client-side or server-side cancellation.

[vonzshik]

@vonzshik

Isn't SqlServer checks by itself, if the client is still waiting for a query?

I have no idea what SqlClient or SqlServer do with regards to cancellation...

I remember reading a discussion concerning that at the Npgsql repo. But I've been unable to find it.

Cancellation is supposed to be as fast as possible, and the request itself should be pretty small.

Which request are you referring to? This discussion is about whether SqlClient implements client-side or server-side cancellation.

That part was about the original proposal - DbCommand.CancelAsync.

[roji]

Ah, understood and agreed - allowing cancellation to be cancelled doesn't make much sense to me either.

[mikependon]

The new support - for both DbCommand.Cancel (sync) and cancellation tokens (async) - would first send a server-side PostgreSQL cancellation (so cancelling the query), and after a certain timeout, would break the socket client-side (important in case of a network partition). This shows that the same cancellation method can actually result in both server-side and client-side cancellation.

Cool @roji, we know that the Npgsql is quite advance. TBH, thanks for handling this case on this driver and also on the DB side itself.

By the way, will it really cancel if I issued multiple SQL Statements and/or the execution is in a middle of something? What will happened if the things are not wrapped with Transaction object?

but I don't think it would impact this issue much - SqlClient is just one provider and its behavior doesn't really dictate what other providers should do in this instance.

Thinking to just investigate it in SQL Server at first. It seems no need to do in PostgreSQL, as what you said above.

Isn't SqlServer checks by itself, if the client is still waiting for a query?

@vonzshik - TBH, I do not know, but this is quite interesting if this is really happening in the SQL Server side. Just a common question, how do SQL Server know if somebody (clients) are waiting for a result or whatever? I guess, something must be triggered from somewhere, in this case the client. IMHO

[roji]

By the way, will it really cancel if I issued multiple SQL Statements and/or the execution is in a middle of something? What will happened if the things are not wrapped with Transaction object?

The way Npgsql sends batched commands to PostgreSQL, the batch is in an implicit transaction (unless there's an explicit one wrapping it). Also, in PostgreSQL any type of error - including cancellation - immediately puts the ongoing transaction in a failed state, from which it can only be rolled back. This means that if any statement within the batch is cancelled, the whole batch is immediately cancelled and rolled back.

BTW note that in PostgreSQL, cancellation unfortunately isn't tied to a specific statement or command. That is, when you invoke NpgsqlCommand.Cancel (or trigger a cancellation token), Npgsql is in effect telling PostgreSQL to cancel the currently running statement, without any way of forcing it to be a specific statement. This may cause race conditions where you intend to cancel some command, but end up cancelling another later one instead.

Thinking to just investigate it in SQL Server at first.

Would be great to have more info on that.

[vonzshik]

@vonzshik - TBH, I do not know, but this is quite interesting if this is really happening in the SQL Server side. Just a common question, how do SQL Server know if somebody (clients) are waiting for a result or whatever? I guess, something must be triggered from somewhere, in this case the client. IMHO

I think it's KeepAlive. If it is so, it should be possible to catch it with something like Wireshark.

[mikependon]

Tested with a very simple case. Within the code below, I had delayed the SQL execution for 5 seconds.

namespace DbCommandCancelCheck
{
    class Program
    {
        static void Main(string[] args)
        {
            TestDbCommandAsync().Wait();
        }

        static async Task TestDbCommandAsync()
        {
            using (var connection = await new SqlConnection("Server=.;Database=TestDB;Integrated Security=True;").EnsureOpenAsync())
            {
                using (var command = (DbCommand)connection.CreateCommand())
                {
                    // Notice the 5 seconds delay here
                    command.CommandText = "SELECT GETUTCDATE(); WAITFOR DELAY '00:00:05'; SELECT GETUTCDATE();";
                    command.CommandTimeout = 0;
                    await TestDbDataReaderAsync(command);
                }
            }
        }

        static async Task TestDbDataReaderAsync(DbCommand command)
        {
            /*
             * Here, if I put 10000 (or 10 seconds), the program just continue, 
             * otherwise it throws a 'Cancellation problem'
             */
            var delay = 2000;
            var tokenSource = new CancellationTokenSource();
            using (var reader = command.ExecuteReaderAsync(tokenSource.Token))
            {
                Thread.Sleep(delay); 
                //token.Cancel();
                var result = reader.Result;
                do
                {
                    while (await result.ReadAsync())
                    {
                        Console.WriteLine(result.GetDateTime(0));
                    }
                }
                while (result.NextResult());
            }
        }
    }
}

Cancellation Scenario

Firstly, I had tested the Cancellation() scenario. Within the method TestDbDataReaderAsync(), I had added a delay of 2000 (or 2 seconds) and I had cancelled the code using the token.Cancel() (or CancellationTokenSource.Cancel()). The output to my profiling is below.

Non-Cancellation Scenario

EDIT:

Secondly, I had tested the code without the Cancellation() by simply increasing the delay to 10000 (or 10 seconds) still with a call to the token.Cancel() (or CancellationTokenSource.Cancel()) within the TestDbDataReaderAsync() method. The output is below.

Disclaimer

The code is only tested on the DbCommand.ExecuteReaderAsync() cancellation token object and the expected behaviour is CORRECT for SQL Server. I had not tested the other DB Provider and also the DbCommand.Cancel() method.

I would assume that the sync version will work the same as the async version.

[mikependon]

@roji - but you know what, it seems the request of having the CancelAsync() method is practical :stuck_out_tongue_winking_eye: . If you are to implement this, the management of the CancellationToken object will be placed inside the DbCommand object context instead.

[roji]

@mikependon thanks for investigating this! I can indeed see that triggering the cancellation token cancels the server-side query. The remaining question is what happens when that's not possible, e.g. because the network is down. If only server-cancellation is implemented, then cancelling won't work and the program will hang (potentially forever). To test this, you can execute a WAITFOR, and then manually shut down the network interface or similar on your machine.

PS An easier way to trigger a cancellation after a certain time is to simply create a CancellationTokenSource and pass the timeout (2000) in the constructor; it will automatically trigger for you after those 2 seconds happened (saves you from having to do Thread.Sleep etc).

[roji]

but you know what, it seems the request of having the CancelAsync() method is practical stuck_out_tongue_winking_eye . If you are to implement this, the management of the CancellationToken object will be placed inside the DbCommand object context instead.

I still don't understand how CancelAsync would (or should) be any different from simply triggering the cancellation token as you've down... The standard way to cancel stuff in the .NET async world is to trigger a cancellation token - DbCommand.Cancel really only exists because it comes from the sync world. Why do we also need a CancelAsync?

[mikependon]

@roji - Just to be clear. I am okay with the existing CancellationToken approach via Async methods. The propose CancelAsync would do as same as that, the only difference is the method invocation and the Token management, may happen inside the DbCommand object context itself. I just commented "PRACTICAL" in the sense of having an equivalent async method, but no difference at all. If I am to develop this as community request, I would put this is as the least important and a Nice-To-Have feature only. IMHO

[roji]

@mikependon sure, I'm just still trying to understand your proposal. Are you saying that CancelAsync would be nice, because the user wouldn't need to worry about cancellation tokens? If so, that's true, but since every other async .NET operation out there uses tokens, I'm not sure why we should provide another mechanism here specifically (i.e. what's special here, compared to any other async operation in .NET, aside from a sync Cancel method already existing). I'd be happy to continue this conversation, but up to you and @rgarrison12345.

[mikependon]

@roji - although it is the best practice, but I think, it is important to take note that not all applications are using CancellationToken. Imagine sending a long running SQL Statement or executing a long running SP from the DbCommand ExecuteXXX commands, and you have a requirement to just cancel it if that took more than 30 seconds. If the user does not passed the Tokens (as they do not care), they could atleast still have the way to cancel it via CancelAsync() or Cancel() method. --> I am trying to create my own speculation here.

Disclaimer: I itself preferred using the CancellationToken in all cases, that's why I also introducing that to RepoDB as the request is also quite important.

[roji]

you have a requirement to just cancel it if that took more than 30 seconds

It sounds like you're thinking about timeout rather than cancellation - this is what DbCommand.CommandTimeout is for. No need to deal with cancellation tokens or anything,

Otherwise, any cancellation mechanism needs some way for the user to signal the cancellation (e.g. when the user pressed a "cancel" button in a UI). I just can't see why calling a CancelAsync method is in any way preferable or easier than passing a cancellation token, which is the standard .NET way of doing things. You say that "not all applications are using CancellationToken" - but is there any reason why they shouldn't, for this case?

[mikependon]

You are correct in the case of UI, my sample of 30 seconds only collide with the possibility of CommandTimeout, but definitely it is not. CommandTimeout is a behavior that throws an exception if the execution exceeds the certain period (i.: 30 secs), like ConnectionTimeout in the case of DbConnection, whereas the CancelAsync is a manual trigger from the client and/or UI. And again, you are correct, no need to think this one for now, as the standard CancellationToken would handle this.

[rgarrison12345]

Hello @roji, @mikependon

My original thought on this was I am using new IAsyncEnumerable. I have scenario where I am doing a read through a data reader and yielding a result in an iterator function. If I reach a certain condition I yield break and exit the iterator function. In that scenario where I yield break I want to be able to call CancelAsync. I don't want to call CancellationTokenSource.Cancel because I don't want OperationCanceledException being thrown. I just want to send a command to the remote server to cancel the query and keep going.

I don't want to beat a dead horse here, just explaining my original use case.

[mikependon]

@rgarrison12345 Cannot be the OperationCancelledException be overriden?

[rgarrison12345]

Sure, I can catch the exception and take no action, I would have just preferred not to do that.

[mikependon]

@rgarrison12345 - I mean, cannot you define the behavior of the token from the Cancellation source?

[rgarrison12345]

@mikependon Not sure on the answer to that question.

[mikependon]

I just dived into it, the Cancel(Boolean) is whether you want to proceed or not if the first exception occurs, maybe this can help with your scenario. Otherwise, what you are requesting is really a complete new behavior, if you would like to just ignore an exception. Link is here.

[roji]

I don't want to call CancellationTokenSource.Cancel because I don't want OperationCanceledException being thrown. I just want to send a command to the remote server to cancel the query and keep going.

@rgarrison12345 do you mean you don't want the query in progress to throw any exception when cancelled? What are your expectations for the calling code, i.e. the DbDataReader being used to consume the query's resultset? If no exception gets throw, does the resultset simply stop at some arbitrary point because cancellation happened? That's seems like a highly problematic API behavior (especially since it's trivial to catch the exception and do nothing as @mikependon suggested).

@mikependon unless I'm mistaken, Cancel(boolean) manages what happens if exceptions are thrown from cancellation token callbacks (i.e. code that is attached to a cancellation token via Register). I don't think that's related to the problem being discussed, which is about the OperationCanceledException that is thrown when ThrowIfCancellationRequested is invoked on a cancelled token.

[rgarrison12345]

Hello @roji, I think what you're saying is probably the only way to handle my scenario. Just catch an exception and move on. I am building a graphic interface that's purpose is to audit data. That data comes from a combination of MySql, FireBrid, and SqlServer. I don't actually have control over the definition of stored procedures/views in any of the RDMBS referenced. So it can be difficult at times, hence problematic API development. I'm trying to "normalize" data interaction to reduce provider dependent code. For example Firebird doesn't override "async" base class code and implement it themselves. Agreed that is their problem.

I think what would be great (from a Provider developer perspective) is to have a form of best practice documentation. Such as cancelling the query execution on the server when the cancellation token cancel method is called. Which you mentioned above as being what you would expect a well-behaved provider to do.

I think it would be great to have a way that and while different providers expose different functionality through their drivers. I think a means of suggesting best ".NET" practices is great. Some providers port over their java/python or other language driver just to have a .NET driver. While that is the driver makers problem, would be great to have that documentation readily available.

Maybe that documentation is around if it is can you point me in that direction?

[roji]

Maybe that documentation is around if it is can you point me in that direction?

Some of the desired behavior can be found in the actual ADO.NET API docs. Another important project is @bgrainger's ADO.NET specification tests, which is a test suite for how ADO.NET providers should behave. However, that test suite obviously cannot check if a query is actually cancelled at the server, since there's no way to check that in a cross-database way.