sql error when using CancellationToken and cancelling it fast multiple times

[omuleanu]

this is my code (blazor server):

@inject IDbContextFactory<MyContext> cxf
<button type="button" id="btn1" @onclick="btnClick">Load</button>

    private CancellationTokenSource cts;
    private CancellationTokenSource prevCts;

    private async Task btnClick()
    {
        if (prevCts is not null) prevCts.Cancel();
        cts = new CancellationTokenSource();
        prevCts = cts;

        using var cx = cxf.CreateDbContext();

        var res = await cx.Lunches.ToArrayAsync(cts.Token);
        var res1 = await cx.Categories.ToArrayAsync(cts.Token);
        var res2 = await cx.Countries.ToArrayAsync(cts.Token);
    }

to get the error I'm using js to click the button multiple times instantly:
$('#btn1').click().click().click()
usually 3 clicks is enough to get the error

Error: Microsoft.Data.SqlClient.SqlException (0x80131904): The request failed to run because the batch is aborted, this can be caused by abort signal sent from client, or another request is running in the same session, which makes the session busy.
Operation cancelled by user.
   at Microsoft.Data.SqlClient.SqlCommand.<>c.<ExecuteDbDataReaderAsync>b__208_0(Task`1 result)
   at System.Threading.Tasks.ContinuationResultTaskFromResultTask`2.InnerInvoke()
   at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)
--- End of stack trace from previous location ---
   at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)
   at System.Threading.Tasks.Task.ExecuteWithThreadLocal(Task& currentTaskSlot, Thread threadPoolThread)
--- End of stack trace from previous location ---
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.AsyncEnumerator.InitializeReaderAsync(AsyncEnumerator enumerator, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.ExecuteAsync[TState,TResult](TState state, Func`4 operation, Func`4 verifySucceeded, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.AsyncEnumerator.MoveNextAsync()
   at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ToListAsync[TSource](IQueryable`1 source, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ToListAsync[TSource](IQueryable`1 source, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ToArrayAsync[TSource](IQueryable`1 source, CancellationToken cancellationToken)
...
--- End of stack trace from previous location ---

when 3 clicks doesn't throw the error, with about 15 I can get this error:

 Error: System.InvalidOperationException: Operation cancelled by user.
   at Microsoft.Data.SqlClient.SqlCommand.<>c.<ExecuteDbDataReaderAsync>b__208_0(Task`1 result)
   at System.Threading.Tasks.ContinuationResultTaskFromResultTask`2.InnerInvoke()
   at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)
--- End of stack trace from previous location ---
   at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)
   at System.Threading.Tasks.Task.ExecuteWithThreadLocal(Task& currentTaskSlot, Thread threadPoolThread)
--- End of stack trace from previous location ---
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.AsyncEnumerator.InitializeReaderAsync(AsyncEnumerator enumerator, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.ExecuteAsync[TState,TResult](TState state, Func`4 operation, Func`4 verifySucceeded, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.AsyncEnumerator.MoveNextAsync()
   at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ToListAsync[TSource](IQueryable`1 source, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ToListAsync[TSource](IQueryable`1 source, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ToArrayAsync[TSource](IQueryable`1 source, CancellationToken cancellationToken)
...

Note when the error doesn't occur (with 2 clicks always), I would see (in ExpressProfiler) that for the cancelled clicks only 1 db request is made and the last one makes all the requests.

Include provider and version information

EF Core version: 7
Database provider: (e.g. Microsoft.EntityFrameworkCore.SqlServer)
Target framework: (e.g. .NET 7.0)
Operating system:
IDE: Visual Studio 2022 17.4

[roji]

@omuleanu what exactly is the behavior you find problematic above? When you trigger a cancellation token, the operation to which it was given is supposed to throw an OperationCanceledException - since the operation can't complete. It's your responsibility to catch that exception and handle appropriately (possibly exiting the button click handler right away).

Now, SqlClient specifically doesn't throw OperationCanceledException, but instead throws a SqlException (see dotnet/SqlClient#26); but that doesn't seem to be what you're complaining about (I admit the InvalidOperationException is even more problematic).

[omuleanu]

@roji I wanted to know if this is expected behaviour, I'm not really sure how am I supposed to use the CancellationToken in my scenario.

I have a list with a pager and when the user clicks next page button fast I wanted to cancel the previous request since it is not needed anymore.

And it worked with 2 instant clicks, but with 3 I got the SqlException batch aborted etc. and I thought it might be something unexpected.

[roji]

It's expected. Cancellation is also a race condition: when you trigger the token, the query may have already completed (or not), so you may or may not see a cancellation-related exception. It's a matter of timing and how fast your database query happens to run.

In any case, if you want to just ignore the cancellation in the previous request, wrap the relevant lines in a try/catch and simply return; SqlClient makes it difficult to actually identify that the exception is a cancellation (as opposed to something else), but there's not much I can do about that.

One more thing: think carefully whether it actually makes sense to allow the user to click twice and to cancel the previous request... This means you potentially have two handlers executing concurrently, and potentially updating the UI with different data coming back, at the same time. This can cause lots of chaos. It's pretty standard to simply disable a button after it's already been clicked, until the previous handler completes - after all, what use would there be in cancelling the existing handler and then doing the exact same thing again?

[omuleanu]

I was thinking if I click on page 2 and while loading I click on 3 meaning I changed my mind about loading 2 and want page 3 now; so to make it a tiny bit more responsive, instead of locking until the result is back, also I was interested to learn how the Cancellation works.
Anyway, I have an IsLoading flag and I'll ignore the requests while IsLoading == true

[roji]

I was thinking if I click on page 2 and while loading I click on 3 meaning I changed my mind about loading 2 and want page 3 now; so to make it a tiny bit more responsive, instead of locking until the result is back, also I was interested to learn how the Cancellation works.

You can certainly do that - swallowing cancellation exceptions and making sure things are sane in the face of concurrent updating - though I'm not sure that makes for an intuitive UI :)

In any case, am going to go ahead and close this as it seems that everything is working as expected. But feel free to post back here with further questions.