SQL Server死鎖總結(jié)

昵稱4253644 2011-01-03

展開全文

1. 死鎖原理

根據(jù)操作系統(tǒng)中的定義：死鎖是指在一組進程中的各個進程均占有不會釋放的資源，但因互相申請被其他進程所站用不會釋放的資源而處于的一種永久等待狀態(tài)。

死鎖的四個必要條件：
互斥條件(Mutual exclusion)：資源不能被共享，只能由一個進程使用。
請求與保持條件(Hold and wait)：已經(jīng)得到資源的進程可以再次申請新的資源。
非剝奪條件(No pre-emption)：已經(jīng)分配的資源不能從相應(yīng)的進程中被強制地剝奪。
循環(huán)等待條件(Circular wait)：系統(tǒng)中若干進程組成環(huán)路，該環(huán)路中每個進程都在等待相鄰進程正占用的資源。

對應(yīng)到SQL Server中，當在兩個或多個任務(wù)中，如果每個任務(wù)鎖定了其他任務(wù)試圖鎖定的資源，此時會造成這些任務(wù)永久阻塞，從而出現(xiàn)死鎖；這些資源可能是：單行(RID，堆中的單行)、索引中的鍵(KEY，行鎖)、頁(PAG，8KB)、區(qū)結(jié)構(gòu)(EXT，連續(xù)的8頁)、堆或B樹(HOBT) 、表(TAB，包括數(shù)據(jù)和索引)、文件(File，數(shù)據(jù)庫文件)、應(yīng)用程序?qū)Ｓ觅Y源(APP)、元數(shù)據(jù)(METADATA)、分配單元(Allocation_Unit)、整個數(shù)據(jù)庫(DB)。一個死鎖示例如下圖所示：

說明：T1、T2表示兩個任務(wù)；R1和R2表示兩個資源；由資源指向任務(wù)的箭頭(如R1->T1，R2->T2)表示該資源被改任務(wù)所持有；由任務(wù)指向資源的箭頭(如T1->S2，T2->S1)表示該任務(wù)正在請求對應(yīng)目標資源；
其滿足上面死鎖的四個必要條件：
(1).互斥：資源S1和S2不能被共享，同一時間只能由一個任務(wù)使用；
(2).請求與保持條件：T1持有S1的同時，請求S2；T2持有S2的同時請求S1；
(3).非剝奪條件：T1無法從T2上剝奪S2，T2也無法從T1上剝奪S1；
(4).循環(huán)等待條件：上圖中的箭頭構(gòu)成環(huán)路，存在循環(huán)等待。

2. 死鎖排查

(1). 使用SQL Server的系統(tǒng)存儲過程sp_who和sp_lock，可以查看當前數(shù)據(jù)庫中的鎖情況；進而根據(jù)objectID(@objID)(SQL Server 2005)/ object_name(@objID)(Sql Server 2000)可以查看哪個資源被鎖，用dbcc ld(@blk)，可以查看最后一條發(fā)生給SQL Server的Sql語句；

CREATE Table #Who(spid int,

ecid int,

status nvarchar(50),

loginname nvarchar(50),

hostname nvarchar(50),

blk int,

dbname nvarchar(50),

cmd nvarchar(50),

request_ID int);

CREATE Table #Lock(spid int,

dpid int,

objid int,

indld int,

[Type] nvarchar(20),

Resource nvarchar(50),

Mode nvarchar(10),

Status nvarchar(10)

);

INSERT INTO #Who

EXEC sp_who active --看哪個引起的阻塞，blk

INSERT INTO #Lock

EXEC sp_lock --看鎖住了那個資源id，objid

DECLARE @DBName nvarchar(20);

SET @DBName='NameOfDataBase'

SELECT #Who.* FROM #Who WHERE dbname=@DBName

SELECT #Lock.* FROM #Lock

JOIN #Who

ON #Who.spid=#Lock.spid

AND dbname=@DBName;

--最后發(fā)送到SQL Server的語句

DECLARE crsr Cursor FOR

SELECT blk FROM #Who WHERE dbname=@DBName AND blk<>0;

DECLARE @blk int;

open crsr;

FETCH NEXT FROM crsr INTO @blk;

WHILE (@@FETCH_STATUS = 0)

BEGIN;

dbcc inputbuffer(@blk);

FETCH NEXT FROM crsr INTO @blk;

END;

close crsr;

DEALLOCATE crsr;

--鎖定的資源

SELECT #Who.spid,hostname,objid,[type],mode,object_name(objid) as objName FROM #Lock

JOIN #Who

ON #Who.spid=#Lock.spid

AND dbname=@DBName

WHERE objid<>0;

DROP Table #Who;

DROP Table #Lock;

(2). 使用 SQL Server Profiler 分析死鎖: 將 Deadlock graph 事件類添加到跟蹤。此事件類使用死鎖涉及到的進程和對象的 XML 數(shù)據(jù)填充跟蹤中的 TextData 數(shù)據(jù)列。SQL Server 事件探查器可以將 XML 文檔提取到死鎖 XML (.xdl) 文件中，以后可在 SQL Server Management Studio 中查看該文件。

3. 避免死鎖

上面1中列出了死鎖的四個必要條件，我們只要想辦法破其中的任意一個或多個條件，就可以避免死鎖發(fā)生，一般有以下幾種方法(FROM Sql Server 2005聯(lián)機叢書)：
(1).按同一順序訪問對象。(注：避免出現(xiàn)循環(huán))
(2).避免事務(wù)中的用戶交互。(注：減少持有資源的時間，較少鎖競爭)
(3).保持事務(wù)簡短并處于一個批處理中。(注：同(2)，減少持有資源的時間)
(4).使用較低的隔離級別。(注：使用較低的隔離級別（例如已提交讀）比使用較高的隔離級別（例如可序列化）持有共享鎖的時間更短，減少鎖競爭)
(5).使用基于行版本控制的隔離級別：2005中支持快照事務(wù)隔離和指定READ_COMMITTED隔離級別的事務(wù)使用行版本控制，可以將讀與寫操作之間發(fā)生的死鎖幾率降至最低：
SET ALLOW_SNAPSHOT_ISOLATION ON --事務(wù)可以指定 SNAPSHOT 事務(wù)隔離級別;
SET READ_COMMITTED_SNAPSHOT ON --指定 READ_COMMITTED 隔離級別的事務(wù)將使用行版本控制而不是鎖定。默認情況下(沒有開啟此選項，沒有加with nolock提示)，SELECT語句會對請求的資源加S鎖(共享鎖)；而開啟了此選項后，SELECT不會對請求的資源加S鎖。
注意：設(shè)置 READ_COMMITTED_SNAPSHOT 選項時，數(shù)據(jù)庫中只允許存在執(zhí)行 ALTER DATABASE 命令的連接。在 ALTER DATABASE 完成之前，數(shù)據(jù)庫中決不能有其他打開的連接。數(shù)據(jù)庫不必一定要處于單用戶模式中。
(6).使用綁定連接。(注：綁定會話有利于在同一臺服務(wù)器上的多個會話之間協(xié)調(diào)操作。綁定會話允許一個或多個會話共享相同的事務(wù)和鎖(但每個回話保留其自己的事務(wù)隔離級別)，并可以使用同一數(shù)據(jù)，而不會有鎖沖突。可以從同一個應(yīng)用程序內(nèi)的多個會話中創(chuàng)建綁定會話，也可以從包含不同會話的多個應(yīng)用程序中創(chuàng)建綁定會話。在一個會話中開啟事務(wù)(begin tran)后，調(diào)用exec sp_getbindtoken @Token out;來取得Token，然后傳入另一個會話并執(zhí)行EXEC sp_bindsession @Token來進行綁定(最后的示例中演示了綁定連接)。

4. 死鎖處理方法：

(1). 根據(jù)2中提供的sql，查看那個spid處于wait狀態(tài)，然后用kill spid來干掉(即破壞死鎖的第四個必要條件:循環(huán)等待)；當然這只是一種臨時解決方案，我們總不能在遇到死鎖就在用戶的生產(chǎn)環(huán)境上排查死鎖、Kill sp，我們應(yīng)該考慮如何去避免死鎖。

(2). 使用SET LOCK_TIMEOUT timeout_period(單位為毫秒)來設(shè)定鎖請求超時。默認情況下，數(shù)據(jù)庫沒有超時期限(timeout_period值為-1，可以用SELECT @@LOCK_TIMEOUT來查看該值，即無限期等待)。當請求鎖超過timeout_period時，將返回錯誤。timeout_period值為0時表示根本不等待，一遇到鎖就返回消息。設(shè)置鎖請求超時，破環(huán)了死鎖的第二個必要條件(請求與保持條件)。

服務(wù)器: 消息 1222，級別 16，狀態(tài) 50，行 1
已超過了鎖請求超時時段。

(3). SQL Server內(nèi)部有一個鎖監(jiān)視器線程執(zhí)行死鎖檢查，鎖監(jiān)視器對特定線程啟動死鎖搜索時，會標識線程正在等待的資源；然后查找特定資源的所有者，并遞歸地繼續(xù)執(zhí)行對那些線程的死鎖搜索，直到找到一個構(gòu)成死鎖條件的循環(huán)。檢測到死鎖后，數(shù)據(jù)庫引擎選擇運行回滾開銷最小的事務(wù)的會話作為死鎖犧牲品，返回1205 錯誤，回滾死鎖犧牲品的事務(wù)并釋放該事務(wù)持有的所有鎖，使其他線程的事務(wù)可以請求資源并繼續(xù)運行。

5. 兩個死鎖示例及解決方法

5.1 SQL死鎖

(1). 測試用的基礎(chǔ)數(shù)據(jù)：

CREATE TABLE Lock1(C1 int default(0));

CREATE TABLE Lock2(C1 int default(0));

INSERT INTO Lock1 VALUES(1);

INSERT INTO Lock2 VALUES(1);

(2). 開兩個查詢窗口，分別執(zhí)行下面兩段sql

--Query 1

Begin Tran

Update Lock1 Set C1=C1+1;

WaitFor Delay '00:01:00';

SELECT * FROM Lock2

Rollback Tran;

--Query 2

Begin Tran

Update Lock2 Set C1=C1+1;

WaitFor Delay '00:01:00';

SELECT * FROM Lock1

Rollback Tran;

上面的SQL中有一句WaitFor Delay '00:01:00'，用于等待1分鐘，以方便查看鎖的情況。

(3). 查看鎖情況

在執(zhí)行上面的WaitFor語句期間，執(zhí)行第二節(jié)中提供的語句來查看鎖信息：

Query1中，持有Lock1中第一行(表中只有一行數(shù)據(jù))的行排他鎖(RID:X)，并持有該行所在頁的意向更新鎖(PAG:IX)、該表的意向更新鎖(TAB:IX)；Query2中，持有Lock2中第一行(表中只有一行數(shù)據(jù))的行排他鎖(RID:X)，并持有該行所在頁的意向更新鎖(PAG:IX)、該表的意向更新鎖(TAB:IX)；

執(zhí)行完Waitfor，Query1查詢Lock2，請求在資源上加S鎖，但該行已經(jīng)被Query2加上了X鎖；Query2查詢Lock1，請求在資源上加S鎖，但該行已經(jīng)被Query1加上了X鎖；于是兩個查詢持有資源并互不相讓，構(gòu)成死鎖。

(4). 解決辦法

a). SQL Server自動選擇一條SQL作死鎖犧牲品：運行完上面的兩個查詢后，我們會發(fā)現(xiàn)有一條SQL能正常執(zhí)行完畢，而另一個SQL則報如下錯誤：

服務(wù)器: 消息 1205，級別 13，狀態(tài) 50，行 1
事務(wù)（進程 ID xx）與另一個進程已被死鎖在 lock 資源上，且該事務(wù)已被選作死鎖犧牲品。請重新運行該事務(wù)。

這就是上面第四節(jié)中介紹的鎖監(jiān)視器干活了。

b). 按同一順序訪問對象：顛倒任意一條SQL中的Update與SELECT語句的順序。例如修改第二條SQL成如下：

--Query2

Begin Tran

SELECT * FROM Lock1--在Lock1上申請S鎖

WaitFor Delay '00:01:00';

Update Lock2 Set C1=C1+1;--Lock2:RID:X

Rollback Tran;

當然這樣修改也是有代價的，這會導(dǎo)致第一條SQL執(zhí)行完畢之前，第二條SQL一直處于阻塞狀態(tài)。單獨執(zhí)行Query1或Query2需要約1分鐘，但如果開始執(zhí)行Query1時，馬上同時執(zhí)行Query2，則Query2需要2分鐘才能執(zhí)行完；這種按順序請求資源從一定程度上降低了并發(fā)性。

c). SELECT語句加With(NoLock)提示：默認情況下SELECT語句會對查詢到的資源加S鎖(共享鎖)，S鎖與X鎖(排他鎖)不兼容；但加上With(NoLock)后，SELECT不對查詢到的資源加鎖(或者加Sch-S鎖，Sch-S鎖可以與任何鎖兼容)；從而可以是這兩條SQL可以并發(fā)地訪問同一資源。當然，此方法適合解決讀與寫并發(fā)死鎖的情況，但加With(NoLock)可能會導(dǎo)致臟讀。

SELECT * FROM Lock2 WITH(NOLock)
SELECT * FROM Lock1 WITH(NOLock)

d). 使用較低的隔離級別。SQL Server 2000支持四種事務(wù)處理隔離級別(TIL)，分別為：READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ、SERIALIZABLE；SQL Server 2005中增加了SNAPSHOT TIL。默認情況下，SQL Server使用READ COMMITTED TIL，我們可以在上面的兩條SQL前都加上一句SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED，來降低TIL以避免死鎖；事實上，運行在READ UNCOMMITTED TIL的事務(wù)，其中的SELECT語句不對結(jié)果資源加鎖或加Sch-S鎖，而不會加S鎖；但還有一點需要注意的是：READ UNCOMMITTED TIL允許臟讀，雖然加上了降低TIL的語句后，上面兩條SQL在執(zhí)行過程中不會報錯，但執(zhí)行結(jié)果是一個返回1，一個返回2，即讀到了臟數(shù)據(jù)，也許這并不是我們所期望的。

e). 在SQL前加SET LOCK_TIMEOUT timeout_period，當請求鎖超過設(shè)定的timeout_period時間后，就會終止當前SQL的執(zhí)行，犧牲自己，成全別人。

f). 使用基于行版本控制的隔離級別(SQL Server 2005支持)：開啟下面的選項后，SELECT不會對請求的資源加S鎖，不加鎖或者加Sch-S鎖，從而將讀與寫操作之間發(fā)生的死鎖幾率降至最低；而且不會發(fā)生臟讀。啊

SET ALLOW_SNAPSHOT_ISOLATION ON
SET READ_COMMITTED_SNAPSHOT ON

g). 使用綁定連接(使用方法見下一個示例。)

5.2 程序死鎖(SQL阻塞)

看一個例子：一個典型的數(shù)據(jù)庫操作事務(wù)死鎖分析，按照我自己的理解，我覺得這應(yīng)該算是C#程序中出現(xiàn)死鎖，而不是數(shù)據(jù)庫中的死鎖；下面的代碼模擬了該文中對數(shù)據(jù)庫的操作過程：

//略去的無關(guān)的code

SqlConnection conn = new SqlConnection(connectionString);

conn.Open();

SqlTransaction tran = conn.BeginTransaction();

string sql1 = "Update Lock1 SET C1=C1+1";

string sql2 = "SELECT * FROM Lock1";

ExecuteNonQuery(tran, sql1); //使用事務(wù):事務(wù)中Lock了Table

ExecuteNonQuery(null, sql2); //新開一個connection來讀取Table

public static void ExecuteNonQuery(SqlTransaction tran, string sql)

{

SqlCommand cmd = new SqlCommand(sql);

if (tran != null)

{

cmd.Connection = tran.Connection;

cmd.Transaction = tran;

cmd.ExecuteNonQuery();

}

else

{

using (SqlConnection conn = new SqlConnection(connectionString))

{

conn.Open();

cmd.Connection = conn;

cmd.ExecuteNonQuery();

}

執(zhí)行到ExecuteNonQuery(null, sql2)時拋出SQL執(zhí)行超時的異常，下圖從數(shù)據(jù)庫的角度來看該問題：

代碼從上往下執(zhí)行，會話1持有了表Lock1的X鎖，且事務(wù)沒有結(jié)束，回話1就一直持有X鎖不釋放；而會話2執(zhí)行select操作，請求在表Lock1上加S鎖，但S鎖與X鎖是不兼容的，所以回話2的被阻塞等待，不在等待中，就在等待中獲得資源，就在等待中超時。。。從中我們可以看到，里面并沒有出現(xiàn)死鎖，而只是SELECT操作被阻塞了。也正因為不是數(shù)據(jù)庫死鎖，所以SQL Server的鎖監(jiān)視器無法檢測到死鎖。

我們再從C#程序的角度來看該問題：

C#程序持有了表Lock1上的X鎖，同時開了另一個SqlConnection還想在該表上請求一把S鎖，圖中已經(jīng)構(gòu)成了環(huán)路；太貪心了，結(jié)果自己把自己給鎖死了。。。

雖然這不是一個數(shù)據(jù)庫死鎖，但卻是因為數(shù)據(jù)庫資源而導(dǎo)致的死鎖，上例中提到的解決死鎖的方法在這里也基本適用，主要是避免讀操作被阻塞，解決方法如下：

       a). 把SELECT放在Update語句前：SELECT不在事務(wù)中，且執(zhí)行完畢會釋放S鎖；
       b). 把SELECT也放加入到事務(wù)中：ExecuteNonQuery(tran, sql2);
       c). SELECT加With(NOLock)提示：可能產(chǎn)生臟讀；
       d). 降低事務(wù)隔離級別：SELECT語句前加SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED；同上，可能產(chǎn)生臟讀；
       e). 使用基于行版本控制的隔離級別（同上例）。
       g). 使用綁定連接：取得事務(wù)所在會話的token，然后傳入新開的connection中；執(zhí)行EXEC sp_bindsession @Token后綁定了連接，最后執(zhí)行exec sp_bindsession null;來取消綁定；最后需要注意的四點是：
    (1). 使用了綁定連接的多個connection共享同一個事務(wù)和相同的鎖，但各自保留自己的事務(wù)隔離級別；
    (2). 如果在sql3字符串的“exec sp_bindsession null”換成“commit tran”或者“rollback tran”，則會提交整個事務(wù)，最后一行C#代碼tran.Commit()就可以不用執(zhí)行了(執(zhí)行會報錯，因為事務(wù)已經(jīng)結(jié)束了-,-)。
    (3). 開啟事務(wù)(begin tran)后，才可以調(diào)用exec sp_getbindtoken @Token out來取得Token；如果不想再新開的connection中結(jié)束掉原有的事務(wù)，則在這個connection close之前，必須執(zhí)行“exec sp_bindsession null”來取消綁定連接，或者在新開的connectoin close之前先結(jié)束掉事務(wù)(commit/tran)。
    (4). (Sql server 2005 聯(lián)機叢書)后續(xù)版本的 Microsoft SQL Server 將刪除該功能。請避免在新的開發(fā)工作中使用該功能，并著手修改當前還在使用該功能的應(yīng)用程序。請改用多個活動結(jié)果集 (MARS) 或分布式事務(wù)。