2010年8月7日 星期六

SQL Deadlock 的處理經驗談

        最近公司在為了即將上線的產品進行壓力測試,產品內有個結帳功能是同事 A 先生的得意代表作,結帳功能在 A 先生信誓旦旦拍胸脯保證三百萬筆資料秒殺的信心下,平行作業卻發生了 Deadlock。


        過去我做過的其他專案也曾有遇過 Deadlock 這種棘手的狀況,之所以棘手,是因為這個錯誤一定要有兩個以上的工作平行進行強碰才會發生,平常單一測試並不容易重現此問題,這也意味著要重現此錯誤跟時機點有關,並非每次都可如願產生。也因此,交易量大出現的機率也就跟著大,常常 SIT / UAT 階段測不出這個錯誤,一上了 PROD 環境就突然崩壞,搞得人仰馬翻。
        過去要解決這種問題,都是在 review 程式邏輯後,用試誤法猜測問題點進行修改,然後放回 PROD 環境觀察一段時間,如果一兩個月沒有再遇到此問題就猜想八九成命中要害,不然就得重新猜測問題點微調又繼續觀察個把月。
        這種拿 PROD 環境當做測試機的行為本來就令人恐懼,是否真正解決問題也沒有科學的證據只能依賴觀察。Deadlock,就像是一個未爆彈的炸彈那樣令人恐懼。
        然而,這次 A 先生的結帳作業卻剛好是個非常容易重現問題的案例,也讓公司的 DBA 幫我們好好上了一課,學到不少寶貴的經驗。
        首先,先理解 Deadlock 是兩個平行作業彼此互相等待所造成的結果, A 作業鎖住了某個 B 作業需要的資料,而 B 作業也鎖住了另一個 A 作業需要的資料,造成 A 和 B 雙方互等對方解鎖造成的無窮等待,這時候資料庫只能依先後順序挑選其中一個作業犧牲掉。
        先看一個範例如何純手工製造出 Deadlock,利用以下的指令建立兩個各含兩筆資料的資料表,這兩個資料表 t1 和 t2 就是之後我們要分別鎖定的資源:
        CREATE TABLE t1(ID BIGINT);
        INSERT t1 VALUES(1);
        INSERT t1 VALUES(2);
        CREATE TABLE t2(ID BIGINT);
        INSERT t2 VALUES(1);
        INSERT t2 VALUES(2);

        接下來開啟兩個 Query analyzer 命令視窗,分別扮演作業 A 和作業 B,各下達以下指令-
        A:BEGIN TRANUPDATE t1 SET id = 3 WHERE id = 1; -- A 已經鎖住 t1
        B:BEGIN TRANUPDATE t2 SET id = 3 WHERE id = 1; -- B 已經鎖住 t2


        由於兩者皆未承諾交易 ( Commit ),所以鎖定不會釋放。接下來在 A 中下達更新的命令:
        A:UPDATE t2 SET id = 4 WHERE id = 2; -- 因為 B 已經鎖住 t2,所以 A 會開始漫長的等待...
        此時再到 B 上面執行類似的命令:
        B:UPDATE t1 SET id = 4 WHERE id = 2;

        BINGO!B 不久應該會看到下列訊息,而 A 則停止等待並完成它的工作:
        訊息 1205,層級 13,狀態 45,行 1交易 (處理序識別碼 52) 在 鎖定 資源上被另一個處理序鎖死並已被選擇作為死結的犧牲者。請重新執行該交易。

        之後只要把 A 下達 Commit / Rollback 結束鎖定即可。
        這個範例我們明確知道造成死結的資料表是 t1 / t2 ,這是我們故意安排的,但是在一般的案例中,程式邏輯牽扯的資料表和命令這麼多,我們該如何找出關鍵呢?答案是依賴 SQL Profiler 這個工具。
        如果選對相關的 Event ,你可以找到一系列有關 Deadlock 偵測的事件進行監測。一旦抓到死結發生,SQL Profiler 甚至會提出一張圖表說明被鎖定的物件和被犧牲的執行程序:




        其中,圖表會在每個作業中顯示相關的 associated objid:xxxxxxxxxxx,然後我們就可以用以下的指令找出被鎖住的物件是誰:
        SELECT * FROM sys.objects WHERE object_id=1234567
        剛剛又從黑大的部落格中發現,甚至可以從圖表直接萃取出發生問題的 SQL statement,深入閱讀可以參考黑大的這篇文章

Google+ Badge