CODEWITHSUNDEEP
c#sql-serverasync-await
m3z
m3z

Reputation: 990

ExecuteScalarAsync hangs but ExecuteScalar returns instantly

So I've run into a little issue that puzzles me and I've not been able to find a good explanation for this - I imagine I'm probably mis-using the async/await feature somehow but I really don't know what I'm doing wrong.

So I have some sql code that queries my database and returns a single value. I was therefore using ExecuteScalarAsync to get that value out into c#.

The code is as follows:

public void CheckOldTransactionsSync()
{
    CheckOldTransactions().Wait();
}

public async Task CheckOldTransactions()
{
    DateTimeOffset beforeThis = DateTime.UtcNow.Subtract(TimeSpan.FromHours(6));

    using (SqlConnection connection = new SqlConnection(SqlConnectionString))
    {
        await connection.OpenAsync(cts.Token);
        using (SqlCommand command = new SqlCommand(@"SELECT TOP 1 1 AS value FROM SyncLog WHERE [TimeStamp] < @BeforeThis;", connection))
        {
            command.Parameters.Add("@BeforeThis", System.Data.SqlDbType.DateTimeOffset, 7);

            command.Prepare();
            command.Parameters["@BeforeThis"].Value = beforeThis;

            Int32 oldTransactions = (Int32)await command.ExecuteScalarAsync(cts.Token);

            // do stuff with oldTransactions
        }
    }
}

So elsewhere in my code the CancellationTokenSource called cts is created and set to expire after 2 minutes using the CancelAfter method.

Now I've stepped through this code with the debugger and I reach the line where I await the call to ExecuteScalarAsync without a problem. However I seem to have two issues with the execution of that line which are that it doesn't seem to return and 2 it ignores my cancellation token and is still running some time after my two minute cancellation token has expired.

Now I've run the sql query in Sql Studio and it returns very quickly - the table has only around 4000 rows at this time.

I've resolved the problem for now by changing that line to:

Int32 oldTransactions = (Int32) command.ExecuteScalar();

Which returns almost instantaneously.

That is the only line of code I've changed and I changed it back just to make sure and the same issue occurred. So my question is, what did I do wrong with the asynchronous call?

Upvotes: 6

Views: 6631

Answers (1)

usr
usr

Reputation: 171178

You are calling Wait.

That's a classic ASP.NET deadlock. Don't block, or use synchronous IO.

Upvotes: 5

Related Questions