根據作業系統中的定義：死鎖是指在一組程序中的各個程序均佔有不會釋放的資源，但因互相申請被其他程序所站用不會釋放的資源而處於的一種永久等待狀態。
死鎖的四個必要條件：互斥條件(Mutual exclusion)：資源不能被共享，只能由一個程序使用。請求與保持條件(Hold and wait)：已經得到資源的程序可以再次申請新的資源。非剝奪條件(No pre-emption)：已經分配的資源不能從相應的程序中被強制地剝奪。迴圈等待條件(Circular wait)：系統中若干程序組成環路，該環路中每個程序都在等待相鄰程序正佔用的資源。
對應到SQL Server中，當在兩個或多個任務中，如果每個任務鎖定了其他任務試圖鎖定的資源，此時會造成這些任務永久阻塞，從而出現死鎖；這些資源可能是：單行(RID，堆中的單行)、索引中的鍵(KEY，行鎖)、頁(PAG，8KB)、區結構(EXT，連續的8頁)、堆或B樹(HOBT) 、表(TAB，包括資料和索引)、檔案(File，資料庫檔案)、應用程式專用資源(APP)、元資料(METADATA)、分配單元(Allocation_Unit)、整個資料庫(DB)。
其滿足上面死鎖的四個必要條件：(1).互斥：資源S1和S2不能被共享，同一時間只能由一個任務使用；(2).請求與保持條件：T1持有S1的同時，請求S2；T2持有S2的同時請求S1；(3).非剝奪條件：T1無法從T2上剝奪S2，T2也無法從T1上剝奪S1；(4).迴圈等待條件：上圖中的箭頭構成環路，存在迴圈等待。
方法/步驟
1. 死鎖排查
(1). 使用SQL Server的系統儲存過程sp_who和sp_lock，可以檢視當前資料庫中的鎖情況；進而根據objectID(@objID)(SQL Server 2005)/ object_name(@objID)(Sql Server 2000)可以檢視哪個資源被鎖，用dbcc ld(@blk)，可以檢視最後一條發生給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 [email protected] #Lock.* FROM #Lock JOIN #Who ON #Who.spid=#Lock.spid AND [email protected];--最後傳送到SQL Server的語句DECLARE crsr Cursor FOR SELECT blk FROM #Who WHERE [email protected] 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 [email protected] WHERE objid<>0;DROP Table #Who;DROP Table #Lock;
(2). 使用 SQL Server Profiler 分析死鎖: 將 Deadlock graph 事件類新增到跟蹤。此事件類使用死鎖涉及到的程序和物件的 XML 資料填充跟蹤中的 TextData 資料列。SQL Server 事件探查器 可以將 XML 文件提取到死鎖 XML (.xdl) 檔案中，以後可在 SQL Server Management Studio 中檢視該檔案。
2. 避免死鎖
上面列出了死鎖的四個必要條件，我們只要想辦法破其中的任意一個或多個條件，就可以避免死鎖發生，一般有以下幾種方法(FROM Sql Server 2005聯機叢書)：(1).按同一順序訪問物件。(注：避免出現迴圈)(2).避免事務中的使用者互動。(注：減少持有資源的時間，較少鎖競爭)(3).保持事務簡短並處於一個批處理中。(注：同(2)，減少持有資源的時間)(4).使用較低的隔離級別。(注：使用較低的隔離級別（例如已提交讀）比使用較高的隔離級別（例如可序列化）持有共享鎖的時間更短，減少鎖競爭)(5).使用基於行版本控制的隔離級別：2005中支援快照事務隔離和指定READ_COMMITTED隔離級別的事務使用行版本控制，可以將讀與寫操作之間發生的死鎖機率降至最低：SET ALLOW_SNAPSHOT_ISOLATION ON --事務可以指定 SNAPSHOT 事務隔離級別;SET READ_COMMITTED_SNAPSHOT ON --指定 READ_COMMITTED 隔離級別的事務將使用行版本控制而不是鎖定。預設情況下(沒有開啟此選項，沒有加with nolock提示)，SELECT語句會對請求的資源加S鎖(共享鎖)；而開啟了此選項後，SELECT不會對請求的資源加S鎖。注意：設定 READ_COMMITTED_SNAPSHOT 選項時，資料庫中只允許存在執行 ALTER DATABASE命令的連線。在 ALTER DATABASE 完成之前，資料庫中決不能有其他開啟的連線。資料庫不必一定要處於單使用者模式中。(6).使用繫結連線。(注：繫結會話有利於在同一臺伺服器上的多個會話之間協調操作。繫結會話允許一個或多個會話共享相同的事務和鎖(但每個回話保留其自己的事務隔離級別)，並可以使用同一資料，而不會有鎖衝突。可以從同一個應用程式內的多個會話中建立繫結會話，也可以從包含不同會話的多個應用程式中建立繫結會話。在一個會話中開啟事務(begin tran)後，呼叫exec sp_getbindtoken @Token out;來取得Token，然後傳入另一個會話並執行EXEC sp_bindsession @Token來進行繫結(最後的示例中演示了繫結連線)。
3.死鎖處理方法：
(1). 根據2中提供的sql，檢視那個spid處於wait狀態，然後用kill spid來幹掉(即破壞死鎖的第四個必要條件:迴圈等待)；當然這只是一種臨時解決方案，我們總不能在遇到死鎖就在使用者的生產環境上排查死鎖、Kill sp，我們應該考慮如何去避免死鎖。
(2). 使用SET LOCK_TIMEOUT timeout_period(單位為毫秒)來設定鎖請求超時。預設情況下，資料庫沒有超時期限(timeout_period值為-1，可以用SELECT @@LOCK_TIMEOUT來檢視該值，即無限期等待)。當請求鎖超過timeout_period時，將返回錯誤。timeout_period值為0時表示根本不等待，一遇到鎖就返回訊息。設定鎖請求超時，破環了死鎖的第二個必要條件(請求與保持條件)。
伺服器: 訊息 1222，級別 16，狀態 50，行 1已超過了鎖請求超時時段。
(3). SQL Server內部有一個鎖監視器執行緒執行死鎖檢查，鎖監視器對特定執行緒啟動死鎖搜尋時，會標識執行緒正在等待的資源；然後查詢特定資源的所有者，並遞迴地繼續執行對那些執行緒的死鎖搜尋，直到找到一個構成死鎖條件的迴圈。檢測到死鎖後，資料庫引擎 選擇執行回滾開銷最小的事務的會話作為死鎖犧牲品，返回1205 錯誤，回滾死鎖犧牲品的事務並釋放該事務持有的所有鎖，使其他執行緒的事務可以請求資源並繼續執行。
4.兩個死鎖示例及解決方法
4.1 SQL死鎖
(1). 測試用的基礎資料：
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). 開兩個查詢視窗，分別執行下面兩段sql
--Query 1Begin Tran Update Lock1 Set C1=C1+1; WaitFor Delay '00:01:00'; SELECT * FROM Lock2Rollback Tran;--Query 2Begin Tran Update Lock2 Set C1=C1+1; WaitFor Delay '00:01:00'; SELECT * FROM Lock1Rollback Tran;
上面的SQL中有一句WaitFor Delay '00:01:00'，用於等待1分鐘，以方便檢視鎖的情況。
(3). 檢視鎖情況
在執行上面的WaitFor語句期間，執行第二節中提供的語句來檢視鎖資訊：
Query1中，持有Lock1中第一行(表中只有一行資料)的行排他鎖(RID:X)，並持有該行所在頁的意向更新鎖(PAG:IX)、該表的意向更新鎖(TAB:IX)；Query2中，持有Lock2中第一行(表中只有一行資料)的行排他鎖(RID:X)，並持有該行所在頁的意向更新鎖(PAG:IX)、該表的意向更新鎖(TAB:IX)；
執行完Waitfor，Query1查詢Lock2，請求在資源上加S鎖，但該行已經被Query2加上了X鎖；Query2查詢Lock1，請求在資源上加S鎖，但該行已經被Query1加上了X鎖；於是兩個查詢持有資源並互不相讓，構成死鎖。
(4). 解決辦法
a). SQL Server自動選擇一條SQL作死鎖犧牲品：執行完上面的兩個查詢後，我們會發現有一條SQL能正常執行完畢，而另一個SQL則報如下錯誤：
伺服器: 訊息 1205，級別 13，狀態 50，行 1事務（程序 ID xx）與另一個程序已被死鎖在 lock 資源上，且該事務已被選作死鎖犧牲品。請重新執行該事務。
這就是上面第四節中介紹的鎖監視器幹活了。
b). 按同一順序訪問物件：顛倒任意一條SQL中的Update與SELECT語句的順序。例如修改第二條SQL成如下：
--Query2Begin Tran SELECT * FROM Lock1--在Lock1上申請S鎖 WaitFor Delay '00:01:00'; Update Lock2 Set C1=C1+1;--Lock2:RID:XRollback Tran;
當然這樣修改也是有代價的，這會導致第一條SQL執行完畢之前，第二條SQL一直處於阻塞狀態。單獨執行Query1或Query2需要約1分鐘，但如果開始執行Query1時，馬上同時執行Query2，則Query2需要2分鐘才能執行完；這種按順序請求資源從一定程度上降低了併發性。
c). SELECT語句加With(NoLock)提示：預設情況下SELECT語句會對查詢到的資源加S鎖(共享鎖)，S鎖與X鎖(排他鎖)不相容；但加上With(NoLock)後，SELECT不對查詢到的資源加鎖(或者加Sch-S鎖，Sch-S鎖可以與任何鎖相容)；從而可以是這兩條SQL可以併發地訪問同一資源。當然，此方法適合解決讀與寫併發死鎖的情況，但加With(NoLock)可能會導致髒讀。
SELECT * FROM Lock2 WITH(NOLock)SELECT * FROM Lock1 WITH(NOLock)
d). 使用較低的隔離級別。SQL Server 2000支援四種事務處理隔離級別(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的事務，其中的SELECT語句不對結果資源加鎖或加Sch-S鎖，而不會加S鎖；但還有一點需要注意的是：READ UNCOMMITTED TIL允許髒讀，雖然加上了降低TIL的語句後，上面兩條SQL在執行過程中不會報錯，但執行結果是一個返回1，一個返回2，即讀到了髒資料，也許這並不是我們所期望的。
e). 在SQL前加SET LOCK_TIMEOUT timeout_period，當請求鎖超過設定的timeout_period時間後，就會終止當前SQL的執行，犧牲自己，成全別人。
f). 使用基於行版本控制的隔離級別(SQL Server 2005支援)：開啟下面的選項後，SELECT不會對請求的資源加S鎖，不加鎖或者加Sch-S鎖，從而將讀與寫操作之間發生的死鎖機率降至最低；而且不會發生髒讀。啊
SET ALLOW_SNAPSHOT_ISOLATION ONSET READ_COMMITTED_SNAPSHOT ON
g). 使用繫結連線(使用方法見下一個示例。)
4.2 程式死鎖(SQL阻塞)
看一個例子：一個典型的資料庫操作事務死鎖分析，按照我自己的理解，我覺得這應該算是C#程式中出現死鎖，而不是資料庫中的死鎖；下面的程式碼模擬了該文中對資料庫的操作過程：
//略去的無關的codeSqlConnection 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); //使用事務:事務中Lock了TableExecuteNonQuery(null, sql2); //新開一個connection來讀取Tablepublic 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(); } }}
執行到ExecuteNonQuery(null, sql2)時丟擲SQL執行超時的異常
程式碼從上往下執行，會話1持有了表Lock1的X鎖，且事務沒有結束，回話1就一直持有X鎖不釋放；而會話2執行select操作，請求在表Lock1上加S鎖，但S鎖與X鎖是不相容的，所以回話2的被阻塞等待，不在等待中，就在等待中獲得資源，就在等待中超時。。。從中我們可以看到，裡面並沒有出現死鎖，而只是SELECT操作被阻塞了。也正因為不是資料庫死鎖，所以SQL Server的鎖監視器無法檢測到死鎖。
我們再從C#程式的角度來看該問題：
C#程式持有了表Lock1上的X鎖，同時開了另一個SqlConnection還想在該表上請求一把S鎖，圖中已經構成了環路；太貪心了，結果自己把自己給鎖死了。。。
雖然這不是一個數據庫死鎖，但卻是因為資料庫資源而導致的死鎖，上例中提到的解決死鎖的方法在這裡也基本適用，主要是避免讀操作被阻塞，解決方法如下：
a). 把SELECT放在Update語句前：SELECT不在事務中，且執行完畢會釋放S鎖； b). 把SELECT也放加入到事務中：ExecuteNonQuery(tran, sql2); c). SELECT加With(NOLock)提示：可能產生髒讀； d). 降低事務隔離級別：SELECT語句前加SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED；同上，可能產生髒讀； e). 使用基於行版本控制的隔離級別（同上例）。 g). 使用繫結連線：取得事務所在會話的token，然後傳入新開的connection中；執行EXEC sp_bindsession @Token後綁定了連線，最後執行exec sp_bindsession null;來取消繫結；最後需要注意的四點是： (1). 使用了繫結連線的多個connection共享同一個事務和相同的鎖，但各自保留自己的事務隔離級別； (2). 如果在sql3字串的“exec sp_bindsession null”換成“commit tran”或者“rollback tran”，則會提交整個事務，最後一行C#程式碼tran.Commit()就可以不用執行了(執行會報錯，因為事務已經結束了-,-)。 (3). 開啟事務(begin tran)後，才可以呼叫exec sp_getbindtoken @Token out來取得Token；如果不想再新開的connection中結束掉原有的事務，則在這個connection close之前，必須執行“exec sp_bindsession null”來取消繫結連線，或者在新開的connectoin close之前先結束掉事務(commit/tran)。 (4). (Sql server 2005 聯機叢書)後續版本的 Microsoft SQL Server 將刪除該功能。請避免在新的開發工作中使用該功能，並著手修改當前還在使用該功能的應用程式。 請改用多個活動結果集 (MARS) 或分散式事務。
tran = connection.BeginTransaction();string sql1 = "Update Lock1 SET C1=C1+1";ExecuteNonQuery(tran, sql1); //使用事務:事務中Lock了測試表Lock1string sql2 = @"DECLARE @Token varchar(255);exec sp_getbindtoken @Token out;SELECT @Token;";string token = ExecuteScalar(tran, sql2).ToString();string sql3 = "EXEC sp_bindsession @Token;Update Lock1 SET C1=C1+1;exec sp_bindsession null;";SqlParameter parameter = new SqlParameter("@Token", SqlDbType.VarChar);parameter.Value = token;ExecuteNonQuery(null, sql3, parameter); //新開一個connection來操作測試表Lock1tran.Commit();