SQLServer 2008 R2 死結監控及解決辦法

1. 死結原理

    根據作業系統中的定義：死結是指在一組進程中的各個進程均佔有不會釋放的資源，但因互相申請被其他進程所站用不會釋放的資源而處於的一種永久等待狀態。

    死結的四個必要條件：
互斥條件(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)。一個死結樣本如下圖所示：

 

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

 

死結監控一直都很麻煩，我沒有找到很好的方法

如果大家有好的方法，我也很想學習一下

我的方法比較簡單：

1.sp_altermessage 1205 修改1205 錯誤讓他能夠寫入日誌 這樣 代理中的警告才能使用

2.當然是啟動 代理中的警告。開資料庫郵件，會把死結錯誤發送到操作員郵箱裡面。缺點就是沒有詳細的死結資訊。

3.使用sql server 2008 r2 內建的 擴充事件中system_health預設是開啟的，裡面會抓取比較多的值

你可以使用   sys.dm_xe_session_events  聯合 sys.dm_xe_sessions 查看 抓取了那些值 當然其中一個是死結資訊。

當產生死結的時候你何以使用：　

 

代碼如下

複製代碼

SELECT REPLACE( REPLACE(XEventData.XEvent.value('(data/value)[1]', 'varchar(max)'), '<victim-list>', '<deadlock><victim-list>'), '<process-list>','</victim-list><process-list>') FROM (select CAST(target_data as xml) as TargetData from sys.dm_xe_session_targets st join sys.dm_xe_sessions s on s.address = st.event_session_address where name = 'system_health' ) AS Data CROSS APPLY TargetData.nodes ('//RingBufferTarget/event') AS XEventData (XEvent) where XEventData.XEvent.value('@name', 'varchar(4000)') = 'xml_deadlock_report' 查詢所有的死結資訊，當然如果出現記憶體瓶頸的時候能儲存多久我不確定，如果死結太多你無法順利的找到，你想把結果減少一點，可以在每次查詢死結後使用： ALTER EVENT SESSION system_health ON SERVER STATE = stop go ALTER EVENT SESSION system_health ON SERVER STATE = start

關閉並開啟這個擴充事件的session，那麼儲存在記憶體的記錄就會被清空。下次的死結資訊就是最新的

監控執行個體二

下面的SQL語句運行之後，便可以尋找出SQLServer死結和阻塞的源頭。

尋找出SQLServer的死結和阻塞的源頭 --尋找出SQLServer死結和阻塞的源頭

代碼如下

複製代碼

use master go declare @spid int,@bl int DECLARE s_cur CURSOR FOR select  0 ,blocked from (select * from sysprocesses where  blocked>0 ) a where not exists(select * from (select * from sysprocesses where  blocked>0 ) b where a.blocked=spid) union select spid,blocked from sysprocesses where  blocked>0 OPEN s_cur FETCH NEXT FROM s_cur INTO @spid,@bl WHILE @@FETCH_STATUS = 0 begin if @spid =0 select '引起資料庫死結的是: '+ CAST(@bl AS VARCHAR(10)) + '進程號,其執行的SQL文法如下' else select '進程號SPID：'+ CAST(@spid AS VARCHAR(10))+ '被' + ' 進程號SPID：'+ CAST(@bl AS VARCHAR(10)) +'阻塞,其當前進程執行的SQL文法如下' DBCC INPUTBUFFER (@bl ) FETCH NEXT FROM s_cur INTO @spid,@bl end CLOSE s_cur DEALLOCATE s_cur

查看當前進程,或死結進程,並能自動殺掉死進程 --查看當前進程,或死結進程,並能自動殺掉死進程
--因為是針對死的,所以如果有死結進程,只能查看死結進程。當然,你可以通過參數控制,不管有沒有死結,都只查看死結進程。

代碼如下

複製代碼

create proc p_lockinfo @kill_lock_spid bit=1, --是否殺掉死結的進程,1 殺掉, 0 僅顯示 @show_spid_if_nolock bit=1 --如果沒有死結的進程,是否顯示正常進程資訊,1 顯示,0 不顯示 as declare @count int,@s nvarchar(1000),@i int select id=identity(int,1,1),標誌, 進程ID=spid,線程ID=kpid,塊進程ID=blocked,資料庫ID=dbid, 資料庫名=db_name(dbid),使用者ID=uid,使用者名稱=loginame,累計CPU時間=cpu, 登陸時間=login_time,開啟事務數=open_tran, 進程狀態=status, 工作站名=hostname,應用程式名稱=program_name,工作站進程ID=hostprocess, 網域名稱=nt_domain,網卡地址=net_address into #t from( select 標誌='死結的進程', spid,kpid,a.blocked,dbid,uid,loginame,cpu,login_time,open_tran, status,hostname,program_name,hostprocess,nt_domain,net_address, s1=a.spid,s2=0 from master..sysprocesses a join ( select blocked from master..sysprocesses group by blocked )b on a.spid=b.blocked where a.blocked=0 union all select '|_犧牲品_>', spid,kpid,blocked,dbid,uid,loginame,cpu,login_time,open_tran, status,hostname,program_name,hostprocess,nt_domain,net_address, s1=blocked,s2=1 from master..sysprocesses a where blocked<>0 )a order by s1,s2 select @count=@@rowcount,@i=1 if @count=0 and @show_spid_if_nolock=1 begin insert #t select 標誌='正常的進程', spid,kpid,blocked,dbid,db_name(dbid),uid,loginame,cpu,login_time, open_tran,status,hostname,program_name,hostprocess,nt_domain,net_address from master..sysprocesses set @count=@@rowcount end if @count>0 begin create table #t1(id int identity(1,1),a nvarchar(30), b Int,EventInfo nvarchar(255)) if @kill_lock_spid=1 begin declare @spid varchar(10),@標誌 varchar(10) while @i<=@count begin select @spid=進程ID,@標誌=標誌 from #t where id=@i insert #t1 exec('dbcc inputbuffer('+@spid+')') if @標誌='死結的進程' exec('kill '+@spid) set @i=@i+1 end end else while @i<=@count begin select @s='dbcc inputbuffer('+cast(進程ID as varchar)+')' from #t where id=@i insert #t1 exec(@s) set @i=@i+1 end select a.*,進程的SQL語句=b.EventInfo from #t a join #t1 b on a.id=b.id end go exec p_lockinfo

避免死結

    上面1中列出了死結的四個必要條件，我們只要想辦法破其中的任意一個或多個條件，就可以避免死結發生，一般有以下幾種方法(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來進行綁定(最後的樣本中示範了綁定串連)。

兩個死結樣本及解決方案

5.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 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). 查看鎖情況

在執行上面的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成如下：

代碼如下	複製代碼
--Query2 Begin Tran   SELECT * FROM Lock1--在Lock1上申請S鎖   WaitFor Delay '00:01:00';   Update Lock2 Set C1=C1+1;--Lock2:RID:X Rollback 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 ON
SET READ_COMMITTED_SNAPSHOT ON

 

       g). 使用綁定串連(使用方法見下一個樣本。)

 

5.2 程式死結(SQL阻塞)

看一個例子：一個典型的資料庫操作事務死結分析，按照我自己的理解，我覺得這應該算是C#程式中出現死結，而不是資料庫中的死結；下面的代碼類比了該文中對資料庫的操作過程：

 

代碼如下

複製代碼

//略去的無關的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); //使用事務:事務中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();         }     } }

執行到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了測試表Lock1 string 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來操作測試表Lock1 tran.Commit();

附：鎖相容性(FROM SQL Server 2005 聯機叢書)

鎖相容性控制多個事務能否同時擷取同一資源上的鎖。如果資源已被另一事務鎖定，則僅當請求鎖的模式與現有鎖的模式相相容時，才會授予新的鎖請求。如果請求鎖的模式與現有鎖的模式不相容，則請求新鎖的事務將等待釋放現有鎖或等待鎖逾時間隔到期。