-
SQL code
-
-- 死鎖 /* ***************************************************************************************************************************************************** 死鎖指兩個以上事務相互阻塞相互等待對方釋放它們的鎖，SQL Server會通過回滾其中一個事務並返回一個錯誤來自已解決阻塞問題，讓其他事務完成它們的工作。 整理人：中国风(Roy) 日期:2008.07.20 ***************************************************************************************************************************************************** */ set nocount on ; if object_id ( ' T1 ' ) is not null drop table T1 go create 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 ' go if object_id ( ' T2 ' ) is not null drop table T2 go create 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 A 2 101 B 3 101 C T2: ID Col1 Col2 ----------- ----------- -------------------- 1 201 X 2 201 Y 3 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 = 201 commit tran -- 連接窗口2 -- 2步: begin tran update t2 set col2 = col2 + ' B ' where col1 = 203 -- 4步: select * from t1 where col1 = 103 commit 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 = 1 commit tran -- 連接窗口2: -- 2步: begin tran update t2 set col1 = col1 + 1 where ID = 1 -- 4步 select col1 from t1 where ID = 1 commit 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 = 1 commit tran -- 連接窗口2: -- 2步: begin tran select col1 from t1 where ID = 1 -- 會等待連接窗口1提交 -- 4步 update t2 set col1 = col1 + 1 where ID = 1 commit 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 t1 2 、建一個覆蓋索引 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:StmtStarting Locks——Deadlock graph(這是SQL2005新增事件，生成包含死鎖信息的xml值) ——Lock:DeadlockChain 死鎖鏈中的進程產生該事件，可標識死鎖進程的ID並跟蹤操作 ——Lock:Deadlock 該事件發生了死鎖
阻塞分析：
http://blog.csdn.net/roy_88/archive/2008/07/21/2682044.aspx
278
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
