title: mssql - 應用案例 - event notification + service broker建構死鎖自動收集系統
這篇文章介紹sql server的一個典型的應用案例，即如何利用event notification與service broker技術相結合來實作死鎖資訊自動收集系統。通過這個系統，我們可以全面把控sql server資料庫環境中所有執行個體上發生的死鎖詳細資訊，供我們後期分析和解決死鎖場景。
當 sql server 中某組資源的兩個或多個線程或程序之間存在循環的依賴關系時，但因互相申請被其他程序所占用，而不會釋放的資源處于的一種永久等待狀态，将會發生死鎖。sql server服務自動死鎖檢查程序預設每5分鐘跑一次，當死鎖發生時，會選擇一個代價較小的程序做為死鎖犧牲品，以此來避免死鎖導緻更大範圍的影響。被選擇做為死鎖犧牲品的程序會報告如下錯誤：
如果程序間發生了死鎖，對于使用者業務系統，乃至整個sql server服務健康狀況影響很大，輕者系統反應緩慢，服務假死；重者服務挂起，拒絕請求。那麼，我們有沒有一種方法可以完全自動、無人工幹預的方式異步收集sql server系統死鎖資訊并遠端保留死鎖相關資訊呢？這些資訊包括但不僅限于：
死鎖發生在哪些程序之間
各個程序執行的語句塊是什麼？死鎖時，各個程序在執行哪條語句？
死鎖的資源是什麼？死鎖發生在哪個資料庫？哪張表？哪個資料頁？哪個索引上？
死鎖發生的具體時間點，包含語句塊開始時間、語句執行時間等
使用者程序使用的登入使用者是什麼？用戶端驅動是什麼？
......
如此的無人值守的自動死鎖收集系統，就是我們今天要介紹的應用案例分享：利用sql server的event notification與service broker建立自動死鎖資訊收集系統。
在死鎖自動收集系統介紹開始之前，先簡要介紹下sql server service broker和event notification技術。
service broker是微軟至sql server 2005開始內建到資料庫引擎中的消息通訊元件，為 sql server提供隊列和可靠的消息傳遞的能力，可以用來建構基于異步消息通訊為基礎的應用程式。service broker既可用于單個 sql server 執行個體的應用程式，也可用于在多個執行個體間進行消息分發工作的應用程式。service broker使用tcp/ip端口在執行個體間交換消息，所包含的功能有助于防止未經授權的網絡通路，并可以對通過網絡發送的消息進行加密，以此來保證資料安全性。多執行個體之間使用service broker進行異步消息通訊的結構圖如下所示（圖檔來自微軟的官方文檔）：
event notification的中文名稱叫事件通知，執行事件通知可對各種transact-sql資料定義語言(ddl)語句和sql跟蹤事件做出響應，采取的響應方式是将這些事件的相關資訊發送到 service broker 服務。事件通知可以用來執行以下操作：
記錄和檢索發生在資料庫上的更改或活動。
執行操作以異步方式而不是同步方式響應事件。
可以将事件通知用作替代ddl 觸發器和sql跟蹤的程式設計方法。事件通知的資訊媒介是以xml資料類型的資訊傳遞給service broker服務，它提供了有關事件的發生時間、受影響的資料庫對象、涉及的 transact-sql 批處理語句等詳細資訊。對于sql server死鎖而言，可以使用event notification來跟蹤死鎖事件，來擷取deadlock_graph xml資訊，然後通過異步消息元件service broker發送到遠端的deadlock center上的service broker隊列，完成死鎖資訊收集到死鎖中央服務。
在介紹完service broker和event notification以後，我們來看看死鎖手機系統的整體架構圖。在這個系統中，存在兩種類型角色：我們定義為死鎖用戶端（deadlock client）和死鎖中央服務（deadlock center）。死鎖用戶端發生死鎖後，首先會将deadlock graph xml通過service broker發送給死鎖中央服務，死鎖中央服務擷取到service broker消息以後，解析這個xml就可以拿到用戶端的死鎖相關資訊，最後存放到本地日志表中，供終端客戶查詢和分析使用。最終的死鎖收集系統架構圖如下所示：
詳細的死鎖資訊收集過程介紹如下：死鎖用戶端通過本地sql server的event notification捕獲發生在該執行個體上的deadlock事件，并在死鎖發生以後将deadlock graph xml資料存放到event notification綁定的隊列中，然後通過綁定在該隊列上的存儲過程自動觸發将deadlock graph xml通過service broker異步消息通訊的方式發送到死鎖中央服務。中央服務在接收到service broker消息以後，首先放入deadlock center service broker隊列中，該隊列綁定了消息自動處理存儲過程，用來解析deadlock graph xml資訊，并将死鎖相關的詳細資訊存入到deadlock center的log table中。最後，終端使用者可以直接對log table來查詢和分析所有deadlock client上發生的死鎖資訊。通過這系列的過程，最終達到了死鎖資訊的自動遠端存儲、收集，以提供後期死鎖場景還原和複盤，達到死鎖資訊可追溯，及時監控，及時發現的目的。
系統架構設計完畢後，接下來是系統的配置和搭建過程，首先看看service broker的配置。這個配置還是相對比較繁瑣的，包含了以下步驟：
建立service broker資料庫（假設資料庫名為ddlcenter）并開啟service broker選項
建立service broker隊列的激活存儲過程和相關表對象
建立master資料庫下的master key
建立傳輸層本地和遠端證書
建立基于證書的使用者登入
建立service broker端口并授權使用者連接配接
建立ddlcenter資料庫下的master key
建立會話層本地及遠端證書
建立service broker元件所需要的對象，包括：message type、contact、queue、service、remote service binding、route
以下的配置請在deadlock client sql server執行個體上操作。
建立ddlcenter資料庫并開啟service broker選項
三個表和兩個存儲過程
表[ddlcollector].[deadlock_traced_records]：從event notification隊裡接收的消息會記錄到該表中。
表[ddlcollector].[send_records]：deadlock client成功發送service broker消息記錄
表[ddlcollector].[error_records]：記錄發生異常情況時的資訊。
存儲過程[ddlcollector].[up_processdeadlockeventmsg]：deadlock client綁定到隊裡的激活存儲過程，一旦隊列中有消息進入，這個存儲過程會被自動調用。
存儲過程[ddlcollector].[up_senddeadlockmsg]：deadlock client發送異步消息給deadlock center，這個存儲過程會被上面的激活存儲過程調用。
建立master庫下master key
建立傳輸層本地證書并備份到本地檔案系統
這裡請注意證書的開始生效時間要略微早于目前時間，并設定合适的證書過期日期，我這裡是設定的過期日期為9999年12月30号。
建立傳輸層遠端證書
這裡的證書是通過證書檔案來建立的，這個證書檔案來自于遠端通訊的另一端deadlock center sql server的證書檔案的一份拷貝。
建立基于證書檔案的使用者登入
這裡也可以建立帶密碼的正常使用者登入，但是為了規避安全風險，這裡最好建立基于證書檔案的使用者登入。
建立service broker tcp/ip通訊端口并授權使用者連接配接權限
這裡需要注意的是，端口授權的證書一定本地執行個體建立的證書，而不是來自于遠端伺服器的那個證書。比如代碼中的authentication = certificate trpcert_clientlocal部分。
建立ddlcenter資料庫master key
建立會話層本地證書
建立ddlcenter使用者，不需要和任何使用者登入比對
建立會話層遠端證書，這個證書檔案來自deadlock center sql server備份
建立service broker元件對象
deadlock client與deadlock center在建立service broker元件對象時存在差異：第一個差異是建立service的時候，需要包含event notification的contract，名稱為
三張表和兩個存儲過程
表[ddlcollector].[collect_records]：deadlock center成功接收到的service broker消息。
表[ddlcollector].[error_records]：記錄發生異常情況的詳細資訊。
表[ddlcollector].[deadlock_info]：記錄所有deadlock client端發生的deadlock詳細資訊。
存儲過程[ddlcollector].[up_processdeadlockgrapheventmsg]：deadlock center上綁定到隊列的激活存儲過程，一旦隊列中有消息進入，這個存儲過程會被自動調用。
存儲過程[ddlcollector].[up_parsedeadlockgrapheventmsg]：deadlock center上解析deadlock graph xml的存儲過程對象，這個存儲過程會被上面的激活存儲過程調用來解析xml，然後放入表[ddlcollector].[deadlock_info]中。
建立傳輸層遠端證書，這個證書檔案來至于deadlock client sql server
event notification隻需要在deadlock client server建立即可，因為隻需要在deadlock client上跟蹤死鎖事件。在為deadlock client 配置service broker章節，我們已經為event notification建立了隊列、服務和路由。是以，在這裡我們隻需要建立event notification對象即可。方法參見如下的代碼：
至此為止，所有對象和準備工作已經準備完成，萬事俱備隻欠東風，讓我們在deadlock client執行個體上模拟死鎖場景。首先，我們在test資料庫下建立兩個測試表，表名分别為：dbo.test_deadlock1和dbo.test_deadlock2，代碼如下：
接下來，我們使用ssms打開一個新的連接配接，我們假設叫session 1，執行如下語句：
緊接着，我們使用ssms打開第二個連接配接，假設叫session 2，執行下面的語句：
等待一會兒功夫以後，死鎖發生，并且session 2做為了死鎖的犧牲品，我們會在session 2的ssms資訊視窗中看到如下的死鎖資訊：
根據上面的模拟死鎖小節，說明死鎖已經真真切切的發生了，那麼，死鎖資訊到底有沒有被捕獲到呢？如果終端使用者想要檢視和分析所有用戶端的死鎖資訊，隻需要連接配接deadlock center sql server，執行下面的語句：
由于結果集寬度太寬，人為将查詢結果分兩段截圖，第一段結果集展示如下：
第二段結果集截圖如下：
從這個結果集，我們可以清楚的看到deadlock client發生死鎖的詳細資訊，包含：
死鎖發生的deadlock client執行個體名稱：cherish-pc
被死鎖程序号60，死鎖程序57号
死鎖相關程序的事務開始時間，最後一個batch開始執行時間和完成時間
死鎖程序執行的代碼和batch語句
死鎖發生時鎖的類型
表和索引名稱
死鎖相關程序的登入使用者
等等。
當deadlock client 上sql server發生兩次或者兩次以上的deadlock事件以後，自建的event notification對象（名為：deadlocknotificationevent）會被sql server系統自動删除，進而導緻整個死鎖收集系統無法工作。
sql server在錯誤日志中會抛出如下4個錯誤資訊：兩個錯誤編号為17004，一個編号為17001的錯誤，最後是一個編号為17005錯誤，其中17005明确說明了，event notification對象被删除了。如下：
錯誤日志截圖如下：
從錯誤提示資訊due to send time service broker errors來看，最開始花了很長時間來排查service broker方面的問題，在長達數小時的問題排查無果後，靜下心來仔細想想：如果是service broker有問題的話，我們不可能完成第一、第二條死鎖資訊的收集，是以問題應該與service broker沒有直接關系。于是，注意到了錯誤提示資訊的後半部分check to ensure the conversation handle, service broker contract, and service specified in the event notification are active，再次以可以成功收集兩條deadlock錯誤資訊為由，排除contact和service的問題可能性，是以最有可能出問題的地方猜測應該是conversation handle，繼續排查與conversation handle相關操作的地方，發現存儲過程[ddlcollector].[up_processdeadlockeventmsg]的中的代碼:
這個邏輯分支不應該有end conversation的操作，因為這裡是與event notification相關的message type操作，而不是service broker相關的message type操作。
問題分析清楚了，解決方法就非常簡單了，注釋掉這條語句end conversation @handle後，重新建立存儲過程。再多次模拟死鎖操作，再也沒有出現event notification被系統自動删除的情況了，說明這個問題已經被徹底解決，坑已經被填上了。
解決問題的代碼修改和注釋如下截圖，以此紀念下踩過的這個坑：
以下是關于sql server死鎖相關的系列文章，可以幫助我們全面了解、分析和解決死鎖問題，其中第一個是這篇文章的視訊示範。
這篇文章是一個完整的sql server死鎖收集系統典型案例介紹，你甚至可以很輕松簡單的将這個方案應用到你的産品環境，來收集産品環境所有sql server執行個體發生死鎖的詳細資訊，并根據該系統收集到的場景來改進和改善死鎖發生的機率，進而降低死應用發生異常錯誤的可能性。是以這篇文章有着非常重要的現實價值和意義。