SQL2008中SQL應(yīng)用之－死鎖(Deadlocking)

更新時間：2011年06月16日 22:16:54 作者：

當一個用戶會話（會話1）已經(jīng)落定了一個資源，而另一個會話（會話2）想要修改該資源，并且會話2也鎖定了會話1想要修改的資源時，就會出現(xiàn)“死鎖”(deadlocking)。

在另一方釋放資源前，會話1和會話2都不可能繼續(xù)。所以，SQL Server會選擇死鎖中的一個會話作為“死鎖犧牲品”。

注意：死鎖犧牲品的會話會被殺死，事務(wù)會被回滾。

注意：死鎖與正常的阻塞是兩個經(jīng)常被混淆的概念。

發(fā)生死鎖的一些原因：

1、應(yīng)用程序以不同的次序訪問表。例如會話1先更新了客戶然后更新了訂單，而會話2先更新了訂單然后更新了客戶。這就增加了死鎖的可能性。

2、應(yīng)用程序使用了長時間的事務(wù)，在一個事務(wù)中更新很多行或很多表。這樣增加了行的“表面積”，從而導致死鎖沖突。

3、在一些情況下，SQL Server發(fā)出了一些行鎖，之后它又決定將其升級為表鎖。如果這些行在相同的數(shù)據(jù)頁面中，并且兩個會話希望同時在相同的頁面升級鎖粒度，就會產(chǎn)生死鎖。

一、使用 SQL Server Profiler 分析死鎖

http://msdn.microsoft.com/zh-cn/library/ms188246.aspx

二、使用跟蹤標志位找出死鎖

本文主要介紹使用DBCC TRACEON、DBCC TRACEOFF和DBCC TRACESTATUS命令來確保死鎖被正確記錄到SQL Server Management Studio SQL日志中。這些命令用來啟用、關(guān)閉、和檢查跟蹤標志位的狀態(tài)。

■ DBCC TRACEON，啟用跟蹤標志位。用法：DBCC TRACEON ( trace# [ ,...n ][ , -1 ] ) [ WITH NO_INFOMSGS ]

詳細參看 MSDN:http://msdn.microsoft.com/zh-cn/library/ms187329.aspx

■ DBCC TRACESTATUS，檢查跟蹤標志位狀態(tài)。用法：DBCC TRACESTATUS ( [ [ trace# [ ,...n ] ] [ , ] [ -1 ] ] ) [ WITH NO_INFOMSGS ]

詳細參看 MSDN:http://msdn.microsoft.com/zh-cn/library/ms187809.aspx

■ DBCC TRACEOFF，關(guān)閉跟蹤標志位。用法：DBCC TRACEOFF (trace# [ ,...n ] [ , -1 ] ) [ WITH NO_INFOMSGS ]

詳細參看 MSDN:http://msdn.microsoft.com/en-us/library/ms174401.aspx

下面我們模擬一個死鎖：

在第一個SQL查詢窗口執(zhí)行：

復制代碼代碼如下:

 
use AdventureWorks 
go 
SET NOCOUNT ON 
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE 
WHILE 1=1 
BEGIN 
BEGIN TRAN 
UPDATE Purchasing.Vendor 
SET CreditRating = 1 
WHERE VendorID = 90 
UPDATE Purchasing.Vendor 
SET CreditRating = 2 
WHERE VendorID = 91 
COMMIT TRAN 
END 

在第二個查詢窗口執(zhí)行：

復制代碼代碼如下:

 
use AdventureWorks 
go 

SET NOCOUNT ON 
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE 
WHILE 1=1 
BEGIN 
BEGIN TRAN 
UPDATE Purchasing.Vendor 
SET CreditRating = 2 
WHERE VendorID = 91 
UPDATE Purchasing.Vendor 
SET CreditRating = 1 
WHERE VendorID = 90 
COMMIT TRAN 
END 

等待幾秒后，其中一個查詢窗口會提示：

復制代碼代碼如下:

 
/* 
Msg 1205, Level 13, State 51, Line 9 
Transaction (Process ID 52) was deadlocked on lock resources with another process and 
has been chosen as the deadlock victim. Rerun the transaction. 
*/ 

此時，查看，SQL Server Management Studio的SQL 日志，發(fā)現(xiàn)死鎖事件沒有被記錄。
打開第三個查詢窗口，執(zhí)行：

復制代碼代碼如下:

 
DBCC TRACEON (1222, -1) 
GO 
DBCC TRACESTATUS 

為了模擬另一個死鎖，將重啟動“勝利”的那個連接查詢（沒有被殺死的那個），然后重啟死鎖丟失的會話，幾秒后又出現(xiàn)另一個死鎖了。
死鎖發(fā)生后，停止另一個執(zhí)行的查詢?，F(xiàn)在，SQL Server Management Studio的SQL 日志中包含了死鎖事件的詳細錯誤信息。包括相關(guān)的數(shù)據(jù)庫和對象、鎖定模式以及死鎖中的SQL語句。

在檢查完畢后，關(guān)閉跟蹤標志位：

復制代碼代碼如下:

 
DBCC TRACEON (1222, -1) 
GO 
DBCC TRACESTATUS 

解析：

在本例中，我們使用跟蹤標志位1222。跟蹤標志位1222能把詳細的死鎖信息返回到SQL日志中，標志位－1表示跟蹤標志位1222應(yīng)該對所有SQL Server連接在全局中啟用。

三、設(shè)置死鎖優(yōu)先級

我們也可以使用SET DEADLOCK_PRIORITY命令來增加一個查詢會話被選為死鎖犧牲品的可能性。此命令的語法如下：
SET DEADLOCK_PRIORITY { LOW | NORMAL | HIGH | <numeric-priority> | @deadlock_var | @deadlock_intvar }
<numeric-priority> ::= { -10 | -9 | -8 | … | 0 | … | 8 | 9 | 10 }

http://msdn.microsoft.com/en-us/library/ms186736.aspx

例如，上例中，第一個查詢窗口如果使用以下的死鎖優(yōu)先級命令，幾乎可以肯定會被選為死鎖犧牲品。（正常情況下，SQL Server會把它認為取消或回滾代價最小的連接作為默認的死鎖犧牲品）：

復制代碼代碼如下:

 
SET NOCOUNT ON 
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE 
SET DEADLOCK_PRIORITY LOW 
BEGIN TRAN 

UPDATE Purchasing.Vendor 
SET CreditRating = 1 
WHERE VendorID = 2 
UPDATE Purchasing.Vendor 
SET CreditRating = 2 
WHERE VendorID = 1 
COMMIT TRAN 

解析：可以將優(yōu)先級設(shè)為High或Normal,High表示除非另一個會話有相同的優(yōu)先級，否則它不會被選為犧牲品。Normal是默認行為，如果另一個會話是High,它可能會被選中。如果另一個是Low,則它可以安全地不被選中。如果兩個會話有相同的優(yōu)先級，則回滾代價最小的事務(wù)會被選中。

關(guān)于死鎖的其他資源，可能會有補充：

happyhippy的SQL Server死鎖總結(jié)，也總結(jié)的不錯。 http://www.cnblogs.com/happyhippy/archive/2008/11/14/1333922.html

您可能感興趣的文章: