如果发生异常，如何使 SQL 执行命令每 5 分钟重试 10 次

Question

我有一个问题想要解决。我想写入数据库，但我想知道的是，如果失败，我该如何重试写入数据库。就像我希望任务在写入数据库失败时重试一样。

如何实现使写入数据库的 SQL 每 5 分钟重试一次(例如 10 次重试(的逻辑？

public async Task<string> Handler()
{
       DoStuff1,2,3();
}
public async Task<string> Dostuff1,2,3()
{
    try
    {
      using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();
                using (SqlCommand cmd = new SqlCommand())
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Connection = connection;
                    cmd.CommandText = "InsertData";
                    cmd.Parameters.Add(new SqlParameter("Data", "TestData"));
                    cmd.ExecuteNonQuery();
                    return true;
                }
            }
        }
    }
    catch (Exception ex)
    {
        Console.WriteLine("Error");
    }
}

我的新尝试：以下哪一个是正确的？

public async Task<string> Handler()
{
       DatabaseSQLLogic1,2,3();
}

public async Task<string> DatabaseSQLLogic1,2,3()
{
     var success = false;
     var count = 0;
     while(!success && count < 10)
     {
       try
       {
           using (SqlConnection connection = new SqlConnection(connectionString))
                 {
                     connection.Open();
                     using (SqlCommand cmd = new SqlCommand())
                     {
                         cmd.CommandType = CommandType.StoredProcedure;
                         cmd.Connection = connection;
                         cmd.CommandText = "InsertData";
                         cmd.Parameters.Add(new SqlParameter("Data", "TestData"));
                         cmd.ExecuteNonQuery();
                         return true;
                     }
                 }
             }
         success = true;
       }
       catch(Exception ex)
       {
         Console.WriteLine($"An error occurred doing the thing: {ex}");
         count += 1;
       }
     }
     // if success is still false, that means we our loop above failed after 10 attempts
     if(success == false)
     {
       Console.WriteLine("Failed to do the thing after 10 attempts");
     }
}

或

public async Task<string> Handler()
{
     var success = false;
     var count = 0;
     while(!success && count < 10)
     {
       try
       {
          DatabaseSQLLogic1,2,3();
       }
       catch(Exception ex)
       {
         Console.WriteLine($"An error occurred doing the thing: {ex}");
         count += 1;
       }
     }
     // if success is still false, that means we our loop above failed after 10 attempts
     if(success == false)
     {
       Console.WriteLine("Failed to do the thing after 10 attempts");
     }
}



public async Task<string> DatabaseSQLLogic1,2,3()
{
      using (SqlConnection connection = new SqlConnection(connectionString))
       {
           connection.Open();
           using (SqlCommand cmd = new SqlCommand())
           {
               cmd.CommandType = CommandType.StoredProcedure;
               cmd.Connection = connection;
               cmd.CommandText = "InsertData";
               cmd.Parameters.Add(new SqlParameter("Data", "TestData"));
               cmd.ExecuteNonQuery();
               return true;
           }
       }
}

Answer 1

把"数据库"从这个问题中剔除，因为它可以用任何逻辑代替。

var success = false;
var count = 0;
while(!success && count < 10)
{
  try
  {
    DoThing(); // database call, webservice call, etc
    success = true;
  }
  catch(Exception ex)
  {
    Console.WriteLine($"An error occurred doing the thing: {ex}");
    count += 1;
  }
}
// if success is still false, that means we our loop above failed after 10 attempts
if(success == false)
{
  Console.WriteLine("Failed to do the thing after 10 attempts");
}

当然，将Console.WriteLine替换为实际的日志语句。

Answer 2

喜怒无常的遗留系统需要重试逻辑来读取平面文件中的数据。

以下是根据此经验应遵循的一些建议。

1. 检查生成的错误类型，并仅针对适用的错误重试更新。(无法为我们重试连接错误，但可以重试超时错误。
2. 设置重试计数限制常量并将重试限制为该值。(3次罢工，你就为我们工作了。
在
3. 两次重试之间暂停处理一小段时间跨度(~1 秒左右(。这使系统有时间释放资源以供下次重试。
4. 发生这种情况时，生成"重试计数超出限制"错误消息，并报告导致重试尝试失败的最后一个错误。