Report from the battlefield #9 - async/await + MARS

Home

This post from Report from the battlefield series will be about my own mistake. It is related to async/await and MARS i.e. Multiple Active Result Sets. async/await allows us to use asynchronous programming more easily. MARS is a feature of MSSQL that allows us to have more than one pending request opened per connection at the same time. For example, it may be useful if we have 2 nested loops i.e. internal and external. External loops iterate through one result set and the internal one through another. Ok, but you probably wonder what MARS has in common with async/await.

A few days ago my application started failing due to InvalidOperationException exception with the additional message saying that The connection does not support MultipleActiveResultSets. Well, I used MARS in the past so I simply enabled it in the connection string by setting MultipleActiveResultSets attribute to true.

However, later I realized that my application should not require MARS at all so I started digging into what was wrong. It turned out that the problem was related to my silly mistake in using async/await. Let's look at the following simplified version of the problematic code. We have a trivial Main method:

static void Main()
{
   Start().GetAwaiter().GetResult();
}

Start is an async method responsible for opening a connection to DB and executing other async methods:

private static async Task Start()
{
   using (var c = new SqlConnection(ConnectionString))
   {
      c.Open();

      await Func1(c);
      await Func2(c);
      await Func3(c);
   }
}

Func1, Func2 and Func3 are responsible for reading data and processing them. In our case, for simplification, they all will do the same thing:

private static async Task Func1(SqlConnection c) => await ReadData(c);
private static async Task Func2(SqlConnection c) => ReadData(c);
private static async Task Func3(SqlConnection c) => await ReadData(c);

And here is the ReadData method. It's also simple. It simply reads data from a table:

private static async Task ReadData(SqlConnection c)
{
   var cmd = c.CreateCommand();

   cmd.CommandText = "SELECT * FROM dbo.Fun";

   using (var reader = await cmd.ExecuteReaderAsync())
   {
      while (await reader.ReadAsync())
      {
         // Process data
      }
   }
}

If you run this code, the aforementioned InvalidOperationException exception will be thrown in the line with ExecuteReaderAsync. The question is why? Well, in this short code it is rather easy to spot that in Func2 method await is missing before ReadData. But, do you know why it is a problem? If not, don't worry it's a little bit tricky.

Here is an explanation. Without await the simplified flow is as follows:

• ...
• Start executes Func2.
• Func2 executes ReadData.
• ReadData executes ExecuteReaderAsync.
• ReadData awaits for the result from ExecuteReaderAsync.
• The control returns to caller i.e. Func2.
• However, Func2 does not use await so it returns completed task to Start method.
• From the point of view of Start processing of Func2 is finished so it executes Func3.
• Func3 executes ReadData
• The previous call to ReadData may be still in progress.
• It also means that ReadData will call ExecuteReaderAsync when another result set is still being processed.
• The exception is thrown.

Adding missing await fix the problem. Thanks to that the task returned from Func2 will not be completed until call to ReadData is over. And if so Start will not execute Func3 immediately. The final well known conclusion is:

Always async/await all the way down.

^*The picture at the beginning of the post comes from own resources and shows Laurel forest on La Gomera.