.NET で SQL Server/SQL Database のリトライ処理とトランザクションを考える

まずは、標準の SqlConnection で考えます。
SqlConnection に ConnectRetryCount/ConnectRetryInterval が増えたのでそちらから検証します。

日本語情報は安定の SEの雑記 からどうぞ。
ConnectRetryCount/ConnectRetryIntervalを試してみる at SE の雑記

MSDN はこちら
MSDN SqlConnection.ConnectionString プロパティ

さっそく試してみたけど、動かない。。
手元の環境が SQL Server 2012 しか無かったのが失敗。。

さっきの MSDN ドキュメント内のリンクから辿れるページに、
Idle Connection Resiliency
SQL Server 2014 or SQL Database と書いてあるので 2012 では動かないようです。

Azure VM で 2016 入りのを立てて再検証。
※検証コードは NuGet Gallery | Dapper dot net 1.42.0 と NuGet Gallery | Enterprise Library - Transient Fault Handling Application Block - Windows Azure SQL Database integration 6.0.1304.1 を使ってます。

シンプルな例では SEの雑記 の通りに動きました。

using (var conn = new SqlConnection(connstr + "ConnectRetryCount=5;ConnectRetryInterval=10")) {
  conn.Open();
  conn.Query<string>("select [Name] from [TestTable]", transaction:tran).ToList().ForEach(Console.WriteLine);
  Console.WriteLine("Service 止める");
  Console.ReadKey();
  Console.WriteLine("Service止まった！");
  conn.Query<string>("select [Name] from [TestTable]", transaction: tran).ToList().ForEach(Console.WriteLine);
  Console.WriteLine("Service起動したら出力される");
}

では、Transaction を使った場合はどうでしょう？

using (var tran = new TransactionScope()) 
using (var conn = new SqlConnection(connstr + "ConnectRetryCount=5;ConnectRetryInterval=10")) {
  conn.Open();
  conn.Query<string>("select [Name] from [TestTable]", transaction:tran).ToList().ForEach(Console.WriteLine);
  conn.Execute("insert into [TestTable] ([Name]) values (@name)", new { name = "1回目" });
  Console.WriteLine("Service 止める");
  Console.ReadKey();
  Console.WriteLine("Service止まった！");
  conn.Query<string>("select [Name] from [TestTable]", transaction: tran).ToList().ForEach(Console.WriteLine);
  conn.Execute("insert into [TestTable] ([Name]) values (@name)", new { name = "1回目" });
  Console.WriteLine("Service起動したら出力される");
  
  tran.Complete();
}

この場合は、Serviceを止めた後のクエリですぐに例外が出ます。

接続が中断されたため復元できません。接続はサーバーによって復元不可能とマークされました。接続を復元する試行は行われません。

TransactionScope ではなく、DbTransaction を使った場合も同様でした。

接続自体が切断されるケースでは、当然ながら Transaction を使った処理はエラーになりました。
では、エラーの場合はどうでしょう？

リトライ処理が組み込まれている EnterpriseLibrary.TransientFaultHandling.Data の ReliableSqlConnection を使って検証します。
まず、1,2回目は失敗するけど、3回目に成功するストアドを用意します。

create table CountTable ( 
  Id int not null identity(1, 1) primary key, 
  EntryDate date
)
go
create procedure TestProc
as
begin
  set nocount on;
  
  begin tran
  insert into CountTable(EntryDate) values (getdate())
  commit
  
  declare @cnt int = 0
  select @cnt = count(*) from CountTable
  
  if @cnt <= 2
  begin
    raiserror(40540, -1, -1) -- SqlDatabaseTransientErrorDetectionStrategy でリトライ対象のエラー
  end
  else
    truncate table CountTable  
end
go

var connPolicy = new RetryPolicy<SqlDatabaseTransientErrorDetectionStrategy>(3, TimeSpan.FromSeconds(10));
var cmdPolicy = new RetryPolicy<SqlDatabaseTransientErrorDetectionStrategy>(3, TimeSpan.FromSeconds(10));

using (var tran = new TransactionScope()) 
using (var conn = new ReliableSqlConnection(connstr, connPolicy, cmdPolicy)) {
  conn.Open();
  using (var command = conn.CreateCommand()) {
    command.CommandText = @"insert into [TestTable] (Name) values (@name)";
    command.Parameters.AddWithValue("name", "first");
    conn.ExecuteCommand(command);
  }
  using (var command = conn.CreateCommand()) {
    command.CommandText = @"select count(*) [TestTable]";
    Console.WriteLine(conn.ExecuteCommand<int>(command));
  }
  using (var command = conn.CreateCommand()) {
    command.CommandText = @"TestProc";
    command.CommandType = CommandType.StoredProcedure;
    conn.ExecuteCommand(command); // 3回目に成功する
  }
  using (var command = conn.CreateCommand()) {
    command.CommandText = @"insert into [TestTable] (Name) values (@name)";
    command.Parameters.AddWithValue("name", "second");
    conn.ExecuteCommand(command);
  }
  
  tran.Complete();
}

こんなケースだと、Transaction はちゃんと動きます。
もちろん ロールバック をかけたら登録されていません。

但し、ReliableSqlConnection を使っても、Transaction を利用している + 接続が切断されるような場合はリトライ出来ません。

回避方法ですが、RetryPolicy.ExecuteAction/ExecuteAsync を直接利用し、全体の処理をリトライする位しか思いつきませんでした。
まず Connection が切断されてもリトライする Strategy を用意します。

using Microsoft.Practices.EnterpriseLibrary.TransientFaultHandling;
using System;
using System.Data.SqlClient;
using System.Linq;

public class オレオレStrategy : ITransientErrorDetectionStrategy {
  private readonly SqlDatabaseTransientErrorDetectionStrategy _delegate = 
    new SqlDatabaseTransientErrorDetectionStrategy();

  public bool IsTransient(Exception ex) {
    var sqlException = ex as SqlException;
    if (sqlException != null) {
      if (sqlException.Errors.Cast<SqlError>().Where(e => e.Number == 0).Any()) {
        return true;
      }
    }

    return _delegate.IsTransient(ex);
  }
}

これを使って処理を書きます。

var policy = new RetryPolicy<オレオレStrategy>(3, TimeSpan.FromSeconds(10));
policy.ExecuteAction(() => {
  using (var trn = new TransactionScope())
  using (var conn = new SqlConnection(connstr)) {
    conn.Open();
    conn.Execute("insert into [TestTable] ([Name]) values (@name)", new { name = "first" });
    conn.Query<string>("select [Name] from [TestTable]").ToList().ForEach(Console.WriteLine);
    Console.WriteLine("Service 止める");
    Console.ReadKey();
    Console.WriteLine("Service止まった！リトライするので、conn.Open() のタイムアウト待ちまでの間にサービスを起動する事");
    conn.Query<string>("select [Name] from [TestTable]").ToList().ForEach(Console.WriteLine);
    conn.Execute("insert into [TestTable] ([Name]) values (@name)", new { name = "second" });
    trn.Complete();
  }
  Console.WriteLine("TransactinoScope done");
});

これなら接続が切れても頭からやり直します。

ちなみに オレオレStrategy の実装は結構適当です。
そもそも SQL Database で SqlError.Number = 0 で返ってくるのか不明だし、何回実行してもダメなケースでもリトライする様になってます。。
第２回 関西DB勉強会 - 関西DB勉強会 | Doorkeeper の Amazon Aurora Deel Dive で、障害をシュミレートする クエリ が提供されていると紹介されてましたが、SQL Database でもそういうのあると良いのになーと。

Transaction を使った場合の SQL Database のリトライ処理はどうするのが良いのかな～？