前幾天分享的暫時性故障重試利器 - Polly,起源於我要處理一個 Deadlock 失敗自動重試的案例。案件本身有點玄疑,發生在一段 WHERE、JOIN 條件頗複雜的純查詢上(對! 只有 SELECT,不涉及 UPDATE、DELETE),在線上環境尖峰時間偶爾發生 "交易 (處理序識別碼 70) 在 鎖定 資源上被另一個處理序鎖死並已被選擇作為死結的犧牲者。請重新執行該交易。" 的 Deadlock 錯誤訊息,頻率不高,幾萬次才會出現一次吧。調查 Deadlock 形成原因仍需費點工夫,暫時是無法解決了,但誠如上回討論的,這種暫時性故障程式再試一次就好了,何苦讓使用者察覺,還噴出什麼"死結"、"犠牲者"等恐怖字眼,搞資料庫的人明白怎麼一回事,祕書美眉看到,還以為自己生命受危脅嚇到想報警了呢。(並不會)

於是,借助神奇的 Polly,原本的程式可輕鬆改成如下。遇到 SQL 1205 錯誤時,會等 1、2、4 稍後重試一次(其實重試一次就好,連續 Deadlock 三次樂透可以中頭彩了)。這裡順便示範在 WaitAndTry() 傳入第二個參數 Action<Exception, TimeSpan> onRetry,可在重試時留下記錄:

static void Main(string[] args)
{
    TestFunc();
    Console.ReadLine();
}

static void TestFunc()
{
    var answer = Policy
        .Handle<SqlException>(se => se.Number == 1205)
        .WaitAndRetry(new TimeSpan[]
        {
            TimeSpan.FromSeconds(1),
            TimeSpan.FromSeconds(2),
            TimeSpan.FromSeconds(4)
        }, (ex, timeSpan) =>
        {
            //這裡用 Console.WriteLine 簡單記錄重試動作
            Console.WriteLine(
                $"Retry after {timeSpan.TotalSeconds}s - {ex.Message}");

        })
        .Execute<int>(() =>
        {
            return SomeComplexQueryJob();
        });
    Console.WriteLine($"The Answer = {answer}");
}

//某個會觸發 Deadlock 的 SQL 查詢動作
//不要問我為什麼單純 SELECT 動作會引發 Deadlock,拎杯就是有遇到
//發生機率很低,我還沒找出觸發這種茶包的 Pattern
static int SomeComplexQueryJob()
{
    using (var cn = new SqlConnection(cnStr))
    {
        //搞出問題的實際查詢很複雜,我模擬不出來,隨便寫一個
        return cn.Query<int>("SELECT 42 AS AnswerToEverything").Single();
    }
}

問題來了,我要怎麼驗證重試流程有效?最直覺的想法是改寫 SomeComplexQueryJob(),拋出 Deadlock 錯誤。(如果程式有套用 DI / Interface 設計,遇到這種狀況要抽換就很簡單,實作範例可參考 ASP.NET Core 練習 - 依賴注入 DI 裡抽換成模擬資料 IWeatherService 類別 - FakeWeatherService 的例子)

要拋出 SQL 1025 Deadlock 錯誤,第一種做法是故意搞出 Deadlock,手續稍稍複雜些,還要找到適合的 Table 演一場戲,但至少是可行的。我很久沒有這樣玩 SQL,當作伸展實際演練一次。

原理是做兩個資料表(本例為 Emp, Cust),兩個更新動作用相反的順序 INSERT 資料,一個先塞 Emp 再塞 Cust、另一個先塞 Cust 才塞 Emp:

CREATE TABLE Emp
(
	UserId INT NOT NULL PRIMARY KEY,
	UserName NVARCHAR(16)
)
CREATE TABLE Cust
(
	UserId INT NOT NULL PRIMARY KEY,
	UserName NVARCHAR(16)
)

我寫了以下程式,在正常的 INSERT 之外,另開一條 Thread 跑相反順序的 INSERT,INSERT 過程插入 5 秒等待,刻意讓二者強碰以製造 Deadlock。這裡有個眉角,正常 INSERT 或 Task 跑的逆行 INSERT 都可能被選為 Deadlock 犠牲者,若是 Task 被挑中,若單純只用 Wait() 等待執行完成,程式抓到的將會是 AggregationException,要再查 InnerException 才會取得 SqlException,如此將不會觸發 Polly 的 Handle<SqlException> 設定。因此,先前介紹過的GetAwaiter()是讓程式如預期運作的重要關鍵:

//用計數器控製只製造兩次 Deadlock ,第三次成功
static int throwError = 2;
static int DeadlockQueryJob()
{
    using (var cn = new SqlConnection(cnStr))
    {
        cn.Execute("DELETE FROM Emp; DELETE FROM Cust;");
    }

    //若開 Thread 跑逆行的 INSERT
    var task = Task.Factory.StartNew(() =>
    {
        if (throwError <= 0) return;
        throwError--;
        using (var cn = new SqlConnection(cnStr))
        {
            Console.WriteLine("Executing INSERT in another thread...");
            cn.Execute(@"
BEGIN TRAN
INSERT INTO Emp VALUES (1,'Jeffrey');
WAITFOR DELAY '00:00:05';
INSERT INTO Cust VALUES (1, 'Jeffrey');
COMMIT TRAN");
        }
    });
    using (var cn = new SqlConnection(cnStr))
    {
        Console.WriteLine("Executing INSERT...");
        cn.Execute(@"
BEGIN TRAN
INSERT INTO Cust VALUES (1,'darkthread');
WAITFOR DELAY '00:00:05';
INSERT INTO Emp VALUES (1, 'darkthread');
COMMIT TRAN
");
    }
    //https://blog.darkthread.net/blog/getawaiter-getresult-vs-result/
    //等待另開 Thread 的執行結果
    //這裡 GetAwaiter() 很重要,確保拋回 SqlException 而非 AggregateException
    task.GetAwaiter().GetResult();
    return 42;
}

我們如願看到 Polly WaitAndRetry 生效的結果:

要找到合適的 Table、設計兩組會強碰的指令、加開 Thread 同時跑更新真的很囉嗦,為何不 throw new SqlException 就好?

終於講到這篇文章的重點了(呼!),SqlException 是 SQL 內部使用的型別,沒提供公開的建構式,你沒法隨便 new 一個出來,更別提指定 SqlException.Number。(這裡得指定 Number = 1205 以模擬 Deadlock 錯誤)

下面是反組譯查到的 SqlException 設計,類別加了 sealed 沒法繼承改寫,只有兩個 private 建構式,設計上要透過內部靜態方法 internal static SqlException CreateException(...) 呼叫 private 建構式建立物件。SqlException 本質上限定 System.Data.SqlClient 內部使用,沒打算對外開放。

所以沒路了嗎?當然不是,有 System.Reflection 在手,private、internal 可擋不住我們。

介紹這次用到的 Reflection 技巧:

  1. 沒有參數的 private 建構式
    object? CreateInstance (Type type, bool nonPublic),nonPublic 傳 true 就好了。例如:(SqlErrorCollection)Activator.CreateInstance(typeof(SqlErrorCollection), true);
  2. 有參數的 private 建構式
    object CreateInstance (Type? type, System.Reflection.BindingFlags bindingAttr, System.Reflection.Binder binder, object[] args, System.Globalization.CultureInfo culture, object[] activationAttributes);,args 傳參數陣列(若有多個建構式,型別要精準才會正確對映),binder、culture、activationAttributes 傳 null 即可
  3. 呼叫內部方法
    MethodInfo? GetMethod ("methodName", BindingFlags.Instance | BindingFlags.NonPublic) 取得內部方法的 MethodInfo,再 MethodInfo.Invoke (object obj, object[] parameters) 執行。

追了 SqlException 邏輯,我找出以下生出 Number = 1205 SqlException 的方法:

static int SqlExceptionJob()
{
    if (throwError == 0) return 42;
    throwError--;
    var error = (SqlError)Activator.CreateInstance(typeof(SqlError),
        BindingFlags.Instance | BindingFlags.NonPublic,
        null, new object[]
        {
            (int)1205, //infoNumber
            (byte)255, //errorState
            (byte)0, //errorClass
            "NullServer", //server
            "Error Message", //errorMessage
            "NullProcedure", //procedure
            (int)255, //lineNumber
            (uint)5
        }, null, null);
    SqlErrorCollection errors =
        (SqlErrorCollection)Activator.CreateInstance(typeof(SqlErrorCollection), true);
    //呼叫 internal Add(SqlError error)
    typeof(SqlErrorCollection).GetMethod("Add", BindingFlags.Instance | BindingFlags.NonPublic)
        .Invoke(errors, new object[] { error });

    SqlException ex = (SqlException)Activator.CreateInstance(typeof(SqlException),
        BindingFlags.Instance | BindingFlags.NonPublic,
        null, new object[]
        {
            "交易 (處理序識別碼 9527) 在 鎖定 資源上被另一個處理序鎖死並已被選擇作為死結的犧牲者(你認命吧!)。請重新執行該交易。",
            errors,
            (Exception) null,
            Guid.Empty
        }, null, null);
    throw ex;
}

實測成功! 管它什麼 internal、private,沒有拎杯 Call 不到的方法,爽!

This article shows how to cause a deadlock error on MSSQL and creating a SqlException instance with Reflection.


Comments

# by oaww

用with onlock會不會比較快? XD

# by 馬克

我比較想知道select為什麼會有deadlock… 因為我也有遇到…

# by Jeffrey

to oaww,如果 Dirty Read 帶來的風險低於 Deadlock 造成的困擾,WITH (NOLOCK) 是特效藥無誤。 to 馬克,我也想知道... Orz

# by Xman

我的經驗,時間較長的長查詢容易發生,例如百萬對百萬的 table join... 再來是不同順序的 join 同時執行也有機會: Thread 1: select ..from A inner join B on .. Thread 1: select ..from B inner join A on ..

# by Xman

最後一行應該是 thread 2 才對,工程師喜歡 c & v 老是造成 bug 無誤。

# by Jeffrey

to Xman, Good Point! 感謝分享。

# by Alan

(SqlError)Activator.CreateInstance(typeof(SqlError) 執行到這裡會...System.MissingMethodException: 'Constructor on type 'System.Data.SqlClient.SqlError' not found.' 請問黑大何解...?

# by Kenny

我改成這樣可以繼續跑下去,你試試看囉 var error = (SqlError)Activator.CreateInstance(typeof(SqlError), BindingFlags.Instance | BindingFlags.NonPublic, null, new object[] { (int)1205, //infoNumber (byte)255, //errorState (byte)0, //errorClass "NullServer", //server "Error Message", //errorMessage "NullProcedure", //procedure null, null }, null, null);

Post a comment


76 + 13 =