--
死鎖
/*
*****************************************************************************************************************************************************死鎖指兩個以上事務相互阻塞相互等待對方釋放它們的鎖，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 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
=
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:StmtStartingLocks——Deadlock graph(這是SQL2005新增事件，生成包含死鎖信息的xml值) ——Lock:DeadlockChain 死鎖鏈中的進程產生該事件，可標識死鎖進程的ID並跟蹤操作 ——Lock:Deadlock 該事件發生了死鎖
转载于:https://www.cnblogs.com/liyejun/archive/2009/04/03/1429052.html
