Lesson Learned #193: MARS vs Connection Retry-Logic

[Jose_Manuel_Jurado]

Time ago, we discussed about ir MARS works in Azure SQL Managed Instance or Azure SQL Database in this URL Today I would like to include the change that we need to perform in our connection retry-logic policy if you want to use MARS. 

Basically, we need to understand that using MARS you can open multiple DataReaders using the same connection, but, if, for example,  retrieving the data, we have an issue at networking level, we are going to receive the following error message:  System.Exception.Message.get returned "A transport-level error has occurred when receiving results from the server. (provider: Session Provider, error: 19 - Physical connection is not usable)" . This issue caused the connection will be closed.

In this scenario, the best approach is to detect that the connection has been closed and reopen a new one and continue working using MARS, if we don't perform this operation the following resultset that we want to open will give error because the connection is closed. 

Following, I would like to share with you a piece of code of C#.

• The first function called StartWithRetry has several parameters:
  • iIterations: Number of operations to execute.
  • bPooling: Use connection pooling or not.
  • bMars: Use MARS or not.
• The main purpose of this function is to call multiple times (iterations) the bReaderExecutor function. We have here an exception, that if MARS is disabled we are going to close the connection in every iteration.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Collections;
using System.Data;
using C = System.Data.SqlClient;
using System.Diagnostics;
using System.Threading;
using System.Configuration;
using System.Transactions;

public void StartWithRetry(int iIterations, bool bPooling, bool bMars = true )
        {
            try
            {
                Stopwatch stopWatch = new Stopwatch();
                stopWatch.Start();
                C.SqlConnection oConn = new C.SqlConnection();
                ClsRetryLogic oClsRetry = new ClsRetryLogic();

                for (int tries = 1; tries <= iIterations; tries++)
                {
                    Console.WriteLine("Execution Nr.: " + tries.ToString());
                    Console.WriteLine();
                    if (bReaderExecutor(oClsRetry, "SELECT *, Replicate('xxxxxxx',1000000) as x FROM PerformanceVarcharNVarchar", oConn, 1200, 3, bPooling, bMars))
                    {
                        Console.WriteLine("Success");
                        Console.WriteLine();
                    }
                    else
                    {
                        Console.WriteLine("Error");
                        Console.WriteLine();
                    }
                    if (bMars == false && oConn.State == ConnectionState.Open) { oConn.Close(); }
                }

                oConn.Close();
                stopWatch.Stop();
                TimeSpan ts = stopWatch.Elapsed;
                string elapsedTime = String.Format("{0:00}:{1:00}:{2:00}.{3:00}",
                    ts.Hours, ts.Minutes, ts.Seconds,
                    ts.Milliseconds / 10);
                Console.WriteLine("Object type:{0}. Time: {1}", bPooling ? "Pooling" : "without Pooling", elapsedTime);
                Console.ReadLine();
            }
            catch (Exception e)
            {
                Console.WriteLine("Ups!! " + e.Message);
                Console.ReadLine();
            }

        }

• The second function will be bReaderExecutor that has the following parameters:
  • oClsRetry that is the object to the Retry-Logic connection policy that I have.
  • oConn will be the connection object.
  • iTimeout will be the execution timeout 
  • iTryTimes will be how many attempts to execute the query in case that gives any error. 

If this function detects if the connection is closed, try to open a new one if not use the same connection. 

       public bool bReaderExecutor(ClsRetryLogic oClsRetry, string sSQL, C.SqlConnection oConn, Int32 iTimeout, Int32 iRetryTimes =0, bool bPooling = true,  bool bMars = true)
        {
            try
            {
                Int32 iTimes = 0;
                while (iTimes < iRetryTimes)
                {
                    iTimes++;
                    if (oConn.State == ConnectionState.Closed)
                   {
                    if (!oClsRetry.ConnRetryLogic(GetConnectionString(bPooling, bMars), oConn, false)) 
                    {
                      Console.WriteLine("Ups!! Imposible to connect..");
                      return false;
                    }
                  }
                   if (bExecuteReader(sSQL, oConn, 1200))  { return true;}
                }
               return false;
           
            }
            catch (Exception e)
            {
                Console.WriteLine("Ups!! " + e.Message);
                Console.ReadLine();
                return false;
            }
        }

Finally, we have the function bExecuteReader that will execute and obtain the values.

      public bool bExecuteReader(string sSQL, C.SqlConnection oConn, Int32 iTimeout)
        {
           try
             {
                C.SqlCommand command = new C.SqlCommand(sSQL, oConn);
                command.CommandTimeout = iTimeout;
                C.SqlDataReader SqlReaderC = command.ExecuteReader();
                while (SqlReaderC.Read())
                {
                    Console.WriteLine("Value {0}", SqlReaderC.GetValue(0));
                }
                SqlReaderC.Close();
                return true;
            }
             catch (Exception e)
            {
                Console.WriteLine("Ups!! " + e.Message);
                return false;
            }
        }

With this simple modification, our source code is able to use the same connection when MARS is enabled and adapt the connection retry-logic policy in both scenariosn with/without MARS.

Enjoy!