-
今天碰到客户端软件抛出错误:事务(进程 ID 97)与另一个进程已被死锁在 lock 资源上,且该事务已被选作死锁牺牲品。
-
在网上查了下死锁相关资料,搜集一篇存于此,日后研究:
-
-- 死鎖
/* *****************************************************************************************************************************************************
死鎖指兩個以上事務相互阻塞相互等待對方釋放它們的鎖,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 該事件發生了死鎖
转载于:https://www.cnblogs.com/Handll/archive/2009/10/10/1580313.html