程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> 其他數據庫知識 >> 更多數據庫知識 >> SQL2008中SQL應用之- 死鎖(Deadlocking)

SQL2008中SQL應用之- 死鎖(Deadlocking)

編輯:更多數據庫知識

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

注意:死鎖犧牲品的會話會被殺死,事務會被回滾。

注意:死鎖與正常的阻塞是兩個經常被混淆的概念。

發生死鎖的一些原因:

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

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

3、在一些情況下,SQL Server發出了一些行鎖,之後它又決定將其升級為表鎖。如果這些行在相同的數據頁面中,並且兩個會話希望同時在相同的頁面升級鎖粒度,就會產生死鎖。

一、使用 SQL Server Profiler 分析死鎖 

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

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

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

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

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

 DBCC TRACESTATUS,檢查跟蹤標志位狀態。用法:DBCC TRACESTATUS ( [ [ trace# [ ,...n ] ] [ , ] [ -1 ] ] ) [ WITH NO_INFOMSGS ]

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

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

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

下面我們模擬一個死鎖:

在第一個SQL查詢窗口執行:
復制代碼 代碼如下:
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

在第二個查詢窗口執行:
復制代碼 代碼如下:
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 日志,發現死鎖事件沒有被記錄。
打開第三個查詢窗口,執行:
復制代碼 代碼如下:
DBCC TRACEON (1222, -1)
GO
DBCC TRACESTATUS

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

在檢查完畢後,關閉跟蹤標志位:
復制代碼 代碼如下:
DBCC TRACEON (1222, -1)
GO
DBCC TRACESTATUS

解析: 

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

三、設置死鎖優先級

我們也可以使用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

例如,上例中,第一個查詢窗口如果使用以下的死鎖優先級命令,幾乎可以肯定會被選為死鎖犧牲品。(正常情況下,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

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

關於死鎖的其他資源,可能會有補充:

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

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved