分析死锁并处理_整理贴5
SQL code--死鎖/******************************************************************************************************************************************************死鎖指兩個以上事務相互阻塞相互等待對方釋放它們的鎖，SQL Server會通過回滾其中一個事務並返回一個錯誤來自已解決阻塞問題，讓其他事務完成它們的工作。整理人：中国风(Roy)日期:2008.07.20******************************************************************************************************************************************************/set nocount on ;if object_id('T1') is not null drop table T1gocreate table T1(ID int primary key,Col1 int,Col2 nvarchar(20))insert T1 select 1,101,'A'insert T1 select 2,102,'B'insert T1 select 3,103,'C'goif object_id('T2') is not null drop table T2gocreate table T2(ID int primary key,Col1 int,Col2 nvarchar(20))insert T2 select 1,201,'X'insert T2 select 2,202,'Y'insert T2 select 3,203,'Z'go生成表數據:/*T1:ID Col1 Col2----------- ----------- --------------------1 101 A2 101 B3 101 CT2:ID Col1 Col2----------- ----------- --------------------1 201 X2 201 Y3 201 Z*/防止死鎖：1、 最少化阻塞。阻塞越少，發生死鎖機會越少2、 在事務中按順序訪問表（以上例子：死鎖2）3、 在錯誤處理程式中檢查錯誤1205並在錯誤發生時重新提交事務4、 在錯誤處理程式中加一個過程將錯誤的詳細寫入日誌5、 索引的合理使用(以上例子：死鎖1、死鎖3)當發生死鎖時，事務自動提交，可通過日誌來監視死鎖死鎖1(索引):--連接窗口1--1步:begin tran update t1 set col2=col2+'A' where col1=101--3步: select * from t2 where col1=201commit tran--連接窗口2--2步:begin tran update t2 set col2=col2+'B' where col1=203--4步: select * from t1 where col1=103commit tran--連接窗口1:收到死鎖錯誤,連接窗口2得到結果:/*訊息 1205，層級 13，狀態 51，行 3交易 (處理序識別碼 53) 在 鎖定 資源上被另一個處理序鎖死並已被選擇作為死結的犧牲者。請重新執行該交易。*/--連接窗口2:得到結果/*----------- ----------- --------------------3 103 C*/處理方法:--在t1、t2表的col1條件列建索引create index IX_t1_col1 on t1(col1)create index IX_t2_col1 on t2(col1)go--連接窗口1--1步:begin tran update t1 set col2=col2+'A' where col1=101--3步:select * from t2 with(index=IX_t2_col1)where col1=201 --因表數據少，只能指定索引提示才能確保SQL Server使用索引commit tran--連接窗口2--2步:begin tran update t2 set col2=col2+'B' where col1=203--4步:select * from t1 with(index=IX_t1_col1) where col1=103 --因表數據少，只能指定索引提示才能確保SQL Server使用索引commit tran--連接窗口1:/*ID Col1 Col2----------- ----------- --------------------1 201 X(1 個資料列受到影響)*/--連接窗口2/*ID Col1 Col2----------- ----------- --------------------3 103 C(1 個資料列受到影響)*/死鎖2(訪問表順序):--連接窗口1:--1步:begin tran update t1 set col1=col1+1 where ID=1--3步:select col1 from t2 where ID=1commit tran--連接窗口2:--2步:begin tran update t2 set col1=col1+1 where ID=1--4步select col1 from t1 where ID=1commit tran--連接窗口1:/*col1-----------201(1 個資料列受到影響)*/--連接窗口2:/*col1-----------訊息 1205，層級 13，狀態 51，行 1交易 (處理序識別碼 54) 在 鎖定 資源上被另一個處理序鎖死並已被選擇作為死結的犧牲者。請重新執行該交易。*/處理方法:--改變訪問表的順序--連接窗口1:--1步:begin tran update t1 set col1=col1+1 where ID=1--3步: select col1 from t2 where ID=1commit tran--連接窗口2:--2步:begin tran select col1 from t1 where ID=1--會等待連接窗口1提交--4步 update t2 set col1=col1+1 where ID=1commit tran死鎖3(單表):--連接窗口1:while 1=1 update T1 set col1=203-col1 where ID=2--連接窗口2:declare @i nvarchar(20)while 1=1 set @i=(select col2 from T1 with(index=IX_t1_col1)where Col1=102);--因表數據少，只能指定索引提示才能確保SQL Server使用索引--連接窗口1/*訊息 1205，層級 13，狀態 51，行 4交易 (處理序識別碼 53) 在 鎖定 資源上被另一個處理序鎖死並已被選擇作為死結的犧牲者。請重新執行該交易。*/處理方法:1、刪除col1上的非聚集索引,這樣影響SELECT速度，不可取. drop index IX_t1_col1 on t12、建一個覆蓋索引 A、drop index IX_t1_col1 on t1 B、create index IX_t1_col1_col2 on t1(col1,col2)通過SQL Server Profiler查死鎖信息:啟動SQL Server Profiler——連接實例——事件選取範圍——顯示所有事件選擇項:TSQL——SQL:StmtStartingLocks——Deadlock graph(這是SQL2005新增事件，生成包含死鎖信息的xml值) ——Lock:DeadlockChain 死鎖鏈中的進程產生該事件，可標識死鎖進程的ID並跟蹤操作 ——Lock:Deadlock 該事件發生了死鎖