SqlClient: Canceling SQL Server query with while loop hangs forever
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);
}
}
}
About this issue
- Original URL
- State: open
- Created 6 years ago
- Reactions: 5
- Comments: 35 (26 by maintainers)
Reopening issue as the change was reverted in 4.0.0-preview3 (https://github.com/dotnet/SqlClient/pull/1352)
Any chance of having it re-triaged (or maybe even fixed đ ), please? Itâs a bit silly that we need to use sync methods of the SQL Client because async is broken⌠@cheenamalhotra ?
It isnât really a problem of how expensive the locks are because as you say theyâre pretty cheap most of the time. The problem is that the EndExecute process inside the library is taking a lock very early and holding it for a long time and because that same lock is required for cancellation it prevents cancel, it effectively serializes it to occur after the wait so youâll get a cancellation but it wonât happen until the query is finished which isnât the intention.
the compareexchange isnât for perf itâs just for making the state transition atomic and preventing both EndExecuteInternal (which is Close from the data reader confusingly) and Cancel canât both be executing in a way that they think theyâve succeeded. Instead of locking to serialize we use the successful change of state to identify the âwinnerâ and let them execute.
This isnât the same issue as #109 and @EgorBo is spot on at pinpointing the problem. At the moment a lock is taken in EndExecuteNonQuery and then in TdsParserStateObject.Cancel the lock is attempted and cancellation isnât allowed if it canât be acquired.
If I take out that acquired check meaning that itâll take the lock if it can but will still cancel even if it canât get it then the test works. The rest of the manual test suite also passes.
But the change log for the 4.0 release says the issue was fixed. How come?
As recently announced in the .NET Blog, focus on new SqlClient features an improvements is moving to the new Microsoft.Data.SqlClient package. For this reason, we are moving this issue to the new repo at https://github.com/dotnet/SqlClient. We will still use https://github.com/dotnet/corefx to track issues on other providers like System.Data.Odbc and System.Data.OleDB, and general ADO.NET and .NET data access issues.
@saurabh500 thanks. I thought Iâd seen some issues with the label âbugâ so I wanted to make sure that this wasnât being considered âby designâ per some of the earlier discussion.