--死鎖
/******************************************************************************************************************************************************
死鎖指兩個(gè)以上事務(wù)相互阻塞相互等待對(duì)方釋放它們的鎖，SQL Server會(huì)通過(guò)回滾其中一個(gè)事務(wù)並返回一個(gè)錯(cuò)誤來(lái)自已解決阻塞問(wèn)題，讓其他事務(wù)完成它們的工作。
整理人：中國(guó)風(fēng)(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
生成表數(shù)據(jù):
/*
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、 最少化阻塞。阻塞越少，發(fā)生死鎖機(jī)會(huì)越少
2、 在事務(wù)中按順序訪問(wèn)表（以上例子：死鎖2）
3、 在錯(cuò)誤處理程式中檢查錯(cuò)誤1205並在錯(cuò)誤發(fā)生時(shí)重新提交事務(wù)
4、 在錯(cuò)誤處理程式中加一個(gè)過(guò)程將錯(cuò)誤的詳細(xì)寫(xiě)入日誌
5、 索引的合理使用(以上例子：死鎖1、死鎖3)
當(dāng)發(fā)生死鎖時(shí)，事務(wù)自動(dòng)提交，可通過(guò)日誌來(lái)監(jiān)視死鎖
死鎖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:收到死鎖錯(cuò)誤,連接窗口2得到結(jié)果:
/*
訊息 1205，層級(jí) 13，狀態(tài) 51，行 3
交易 (處理序識(shí)別碼 53) 在 鎖定 資源上被另一個(gè)處理序鎖死並已被選擇作為死結(jié)的犧牲者。請(qǐng)重新執(zhí)行該交易。
*/
--連接窗口2:得到結(jié)果
/*
----------- ----------- --------------------
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 --因表數(shù)據(jù)少，只能指定索引提示才能確保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 --因表數(shù)據(jù)少，只能指定索引提示才能確保SQL Server使用索引
commit tran
--連接窗口1:
/*
ID Col1 Col2
----------- ----------- --------------------
1 201 X
(1 個(gè)資料列受到影響)
*/
--連接窗口2
/*
ID Col1 Col2
----------- ----------- --------------------
3 103 C
(1 個(gè)資料列受到影響)
*/
死鎖2(訪問(wèn)表順序):
--連接窗口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 個(gè)資料列受到影響)
*/
--連接窗口2:
/*
col1
-----------
訊息 1205，層級(jí) 13，狀態(tài) 51，行 1
交易 (處理序識(shí)別碼 54) 在 鎖定 資源上被另一個(gè)處理序鎖死並已被選擇作為死結(jié)的犧牲者。請(qǐng)重新執(zhí)行該交易。
*/
處理方法:
--改變?cè)L問(wèn)表的順序
--連接窗口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--會(huì)等待連接窗口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);--因表數(shù)據(jù)少，只能指定索引提示才能確保SQL Server使用索引
--連接窗口1
/*
訊息 1205，層級(jí) 13，狀態(tài) 51，行 4
交易 (處理序識(shí)別碼 53) 在 鎖定 資源上被另一個(gè)處理序鎖死並已被選擇作為死結(jié)的犧牲者。請(qǐng)重新執(zhí)行該交易。
*/
處理方法:
1、刪除col1上的非聚集索引,這樣影響SELECT速度，不可取.
drop index IX_t1_col1 on t1
2、建一個(gè)覆蓋索引
A、drop index IX_t1_col1 on t1
B、create index IX_t1_col1_col2 on t1(col1,col2)
通過(guò)SQL Server Profiler查死鎖信息:
啟動(dòng)SQL Server Profiler——連接實(shí)例——事件選取範(fàn)圍——顯示所有事件
選擇項(xiàng):
TSQL——SQL:StmtStarting
Locks——Deadlock graph(這是SQL2005新增事件，生成包含死鎖信息的xml值)
——Lock:DeadlockChain 死鎖鏈中的進(jìn)程產(chǎn)生該事件，可標(biāo)識(shí)死鎖進(jìn)程的ID並跟蹤操作
——Lock:Deadlock 該事件發(fā)生了死鎖