sql server - How can I get a reliable timeout on a blocked/slow database operation in C# with Dapper, since commandTimeout is not robust enough?

Question

SQL Server's .NET Client in Microsoft.Data.SqlClient gives us an IDbConnection that supports operations, many of which have a commandTimeout parameter. However, I learned that commandTimeout is applied at a lower level and does not guarantee or even always try to timeout if the whole operation exceeds that time. (I'm using Dapper's QueryAsync, but I think any database access approach would encounter the same problem.)

So how do I set things up so that I can time out or otherwise throw an exception? I tried creating a WithTimeout extension method based on awaiting a Delay, but that also isn't guaranteed to fire in a timely fashion. My code now has seemingly random hangs (probably due to blocking locks) which I could easily handle if they threw an exception, but currently it seems that there is no way to force the database QueryAsync operation. How can I get a real timeout on a blocked/slow database operation in C# using Dapper?

It appears that Dapper doesn't support cancellation tokens.Is there some broader approach to querying the database that could guarantee either completion or an error in a certain time, say 20 seconds?

question from:https://stackoverflow.com/questions/65902725/how-can-i-get-a-reliable-timeout-on-a-blocked-slow-database-operation-in-c-sharp

Answer 1

Is there some broader approach to querying the database that could guarantee either completion or an error in a certain time, say 20 seconds?

Yes, there is. There is a broader approach to performing asynchronous IO that guarantees completion or error in a specified time.

In C#, one can create CancellationTokens that are cancelled after a given time span. Most async methods accept a CancellationToken. IDbConnection.QueryAsync is no exception.

You create a cancellation token source, which takes an optional argument specifying the delay before the task is cancelled (as a TimeSpan or int

See example:

try
{
  IDbConnection db = ...;
  var cts = new CancellationTokenSource(10_000);
  var cmd = new CommandDefinition(commandText: "your query", cancellationToken: cts.Token);
  await db.QueryAsync<Foo>(cmd);
  // Success.
}
catch (TaskCanceledException e)
{
// Failure due to timeout.
}