As we already saw, the reasons why we have blocking issues and deadlocks in the system are PRetty much the same. They occur because of non-optimized queries. So, not surprisingly, troubleshooting techniques are very similar. Let’s have a quick look. We’ll use the same scripts I used last time.

我們現在已經知道，造成系統阻塞以及死鎖的原因往往非常相似，基本都是因為使用了未經過優化的查詢造成的。所以排解這些問題的技術非常相似就不那么意外了。讓我們仔細來看一看實例，還是用我們以前采用過的腳本。

The simplest approach is to use SQL Profiler. There is “Deadlock graph” event in the “Locks” event group you can use. Click on the picture to open it in the different window.

最簡單的方法就是采用SQL自帶的性能監控工具，這里我們可以使用跟蹤屬性下面的事件選擇項卡，展開鎖節點，下面的有一個死鎖圖可供我們選擇，它能幫忙我們分析死鎖原因。

Let’s start the trace and trigger deadlock.

現在我們開始跟蹤以及觸發死鎖

As you can see, it shows you very nice picture. There are 2 sessions (ovals) involved. Those sessions compete for the page locks (squares). You can see what locks each session held and you can even track it down to the resources (but that rarely needed). You can even see the statements when you move the mouse over the session oval and wait for the tool tip.

上面是呈現的是一個非常友好的圖，圖中包含兩個會話(圖中橢圓顯示)。這兩個會話都會競爭數據頁(圖中方塊顯示)上的鎖。你可以看到每個會話已經獲得的鎖而且你還可以向下一直跟蹤到它們各自所引用的資源(這是非常必要的)，甚至你能跟蹤到具體的SQL語句塊當你將鼠標放在會話的上方時系統會給出相應的提示。

In context menu for “deadlock graph” line in the grid above, you have “Extract event data” menu command that can save this information as the file.

在監視窗口中有一個死鎖圖的字樣，你可以點擊右鍵，此時會彈出屬性菜單，你可以將這些死鎖相關的信息保存到文件中。

You can open it as the graph in management studio or, technically, simply look at xml which is extremely familiar:

你可以在SQL管理器中打開它，或者直接以XML形式查看也非常眼熟。

As you can see it’s way more detailed in compare with graphical representation. It’s also extremely familiar with blocking process report – and you can use same technique and query sys.dm_exec_sql_text if you need to obtain sql text from handle. I demonstrated how to do that in post related with blocking troubleshooting.

這是和圖形方式非常詳細的一個比較說明，它和blocking process report非常相似。你可以同樣通過查詢sys.dm_exec_sql_text來顯示你需要知道的SQL語句。我之前已經做了一個演示的實例。[翻譯]:SQL死鎖-阻塞探測

In case, if you don’t want to use SQL Profiler, there are 2 options you can use. The first one is enabling trace flag 1222 with DBCC TRACEON(1222,-1) command. When you have it enabled, SQL Server put deadlock graph XML to SQL Server log.

如果你不想使用SQL自帶的性能監測工具，這里還有兩種辦法。第一就是運行DBCC TRACEON(1222,-1)命令，它將會把死鎖相關的信息存儲為SQL日志。

Another option is using extended events (SQL Server 2008/2008R2). Again, it’s very powerful method although requires some initial work to set it up. As with the blocking, I’m leaving it out of scope for now.

另外一個方法就是使用在SQL SERVER 2008/2008R2中的擴展事件。它是一個非常強的工具，但需要一些初始化的工作。

How to deal with deadlocks? Of course, the best thing is not to have deadlocks at the first place. Again, golden rule is to optimize the queries. Meanwhile, if you need to control (up to degree) what session will be terminated, you can use SET DEADLOCK PRIORITY option. There are 21 priority levels available. When 2 sessions deadlocked, the session with the lower deadlock priority level would be chosen as the victim. In case of the same priority level (default case), SQL Server chooses the session that is less expensive to rollback.

如何解決死鎖？當然，最好的辦法就是避免出現死鎖，解決死鎖的黃金準則就是優化查詢。同時，如果你想監控哪一個會話可能會被終止，你可以使用SET DEADLOCK PRIORITY這個選項。這里有21種有效的優先級別。當有兩個會話出現死鎖后，級別低的會話將會成為競爭中的犧牲品。如果兩個會話擁有相同的級別，那么SQL會選擇一個回滾付出代價比較小的一個做為犧牲品。

If session is chosen as the victim, it would be aborted with error code 1205. In such case client (or T-SQL code) can catch the exception and re-run the query. But again, the best way is avoiding deadlocks at the first place.

如果一個會話被選中為犧牲品，那么它會被取消同時拋出的錯誤代碼為1205。系統客戶端可以捕獲到這個錯誤然后重新嘗試執行查詢。但是最好的方法就是想辦法避免出現死鎖。