C# Polly, 1st SqlException retry after 10 seconds then other all retries after 5 seconds until total 3 minutes

Question

I am implementing Polly to retry SqlConnection errors.

This is the logic I need to implement:

• 1st SqlException - retry after 10 seconds
• Total retrying time is 3 minutes
• After 1st retry, all other retry after 5 seconds

// the following should retry with 5 second delays between tries, up to a 3-minute limit:
Policy timeoutAfter3Minutes = Policy.Timeout(TimeSpan.FromMinutes(3));
Policy retryEveryFiveSeconds = Policy.Handle<Exception>()
                               .Or<TimeoutException>()
                               .Or<SqlException>()
                               .WaitAndRetryForever(sleepDurationProvider: i => TimeSpan.FromSeconds(5)
                               ,(exception, retry, timespan) =>
                               {
                               Console.WriteLine($"Retry count is {retry} and timespan is {timespan} " + $"at {DateTime.Now.ToString("hh.mm.ss.ffffff")}");
                               });

// 1st (1 time only): retry after 10 sec
Policy retryFirstTime = Policy
  .Handle<Exception>()
  .Or<TimeoutException>()
  .Or<DivideByZeroException>()
  .WaitAndRetry(1, sleepDurationProvider: i => TimeSpan.FromSeconds(10),
   (Exception exception, TimeSpan timeSpan, int retryCount, Context _) =>
   {
         Console.WriteLine($"1st retrying SqlConnection on attempt : {retryCount} : 
         {exception.InnerException} " +
         $"in {timeSpan.TotalSeconds} seconds");
   });

// Wrap policy with Timeout Exception every 5 sec until 3 minutes
Policy tryEvery5SecondsUpTo3Minutes = timeoutAfter3Minutes.Wrap(retryEveryFiveSeconds);
 
// Wrap policy 1st retry after 10 seconds and other retries at 5 seconds until 3 minutes
Policy combinePolicy = Policy.Wrap(retryFirstTime, tryEvery10SecondsUpTo5Minutes);

 // Usage
     combinePolicy.Execute(() => reTryDB(conn));


public static void reTryDB(SqlConnection conn)
{
     Console.WriteLine($"Open Sql con at {DateTime.Now.ToString("hh.mm.ss.ffffff")}");

     conn.Open(); 

     string selectCommand = "SELECT TOP 1 * FROM [dbo].[TAbleA]";
     var command = new SqlCommand(selectCommand, conn);

     try
     {
         using (SqlDataReader reader = command.ExecuteReader())
         {
             while (reader.Read())
             {
                 Console.WriteLine(String.Format("{0}", reader[0]));
             }
         }
     }
     catch (Exception ex)
     {
         Console.WriteLine(ex.ToString());
     }

     conn.Close();
}

Here it retries every 5 seconds. I can not able to split 1st retry at 10 seconds, and all other retry every 5 seconds until a maximum of 3 minutes.

Can someone give me idea how can I use PollyWrap to combine these 2 policies?

Answer 1

You don't need two retry policies, one can be sufficient. All you need to do is change this line:

WaitAndRetryForever(sleepDurationProvider:
  i => TimeSpan.FromSeconds(5),

To that:

WaitAndRetryForever(sleepDurationProvider:
  i => TimeSpan.FromSeconds(i == 1 ? 10 : 5),

Basically you are saying that you want to retry the action in every 5 seconds unless it is the first retry. In that case 10 seconds sleep penalty should be used.

This single retry policy is enough to achieve your requirements.

---

Policy timeout = Policy.Timeout(TimeSpan.FromMinutes(3));
Policy retry = Policy.Handle<Exception>()
                               .Or<TimeoutException>()
                               .Or<SqlException>()
                               .WaitAndRetryForever(sleepDurationProvider: i => TimeSpan.FromSeconds(i == 1? 10 : 5)
                               ,(exception, retry, timespan) =>
                               {
                               Console.WriteLine($"Retry count is {retry} and timespan is {timespan} " + $"at {DateTime.Now.ToString("hh.mm.ss.ffffff")}");
                               });

Policy combinedPolicy = Policy.Wrap(timeout, retry);