分析死锁并处理_整理贴5

[中国风]

--死鎖
/******************************************************************************************************************************************************
死鎖指兩個以上事務相互阻塞相互等待對方釋放它們的鎖，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

[yijianboy]

mark,绝度好贴

[LQS_DG2007]

学习，收藏

[yijianboy]

mark

[bzpengyukun]

出现了这个问题，郁闷！

[财富实验室]

引用 79 楼 rediscovery 的回复:

引用 78 楼 roy_88 的回复:樓上兄弟要比較oracle10G以上版本和SQL05以上版本的差別時，最好先了解一下機制。。 這個問題以前曾討論過。。 在論壇上找找。。 針對SQL2000時與oracle比較的確存在不足

不是跟10g比较的，是跟9i进行的比较，我曾经写了几个验证代码分别对SQL Server 2000,2005,2008以及Oracle 9i进行比较，结果发现MS SQL Server都发生死锁了，而Oracle 9i不会发生死锁。

而且我们有项目用的是MS SQL Server 2000，频繁发生死锁，后来迁移到Oracle 9i后，死锁问题不再出现，SQL语句的写法以及执行顺序都基本不变（时间很紧迫也没有时间进行太多的优化）。

让马跑得快，马就得吃草！

[小范f-li.cn]

顶

[fix_huang]

我现在出现了数据库死锁了，是2005的，有谁能够帮帮我啊？错误是这样的：com.microsoft.sqlserver.jdbc.SQLServerException: 事务(进程 ID 265)与另一个进程被死锁在 锁 资源上，并且已被选作死锁牺牲品。请重新运行该事务。
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(Unknown Source)
at com.microsoft.sqlserver.jdbc.IOBuffer.processPackets(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.sendExecute(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteUpdate(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeUpdate(Unknown Source)

[Mr_Nice]

路过，学习...

[hjkl0001]

mark

[zhiqiang93]

Up!Up

[lslzld00]

标记。。。mark

[gw6328]

顶个，在你博客看去。

[system1190]

mark

[haa17]

mark 学习了

[wapit]

标记。MARK

[hk_cxy]

Mark......

[xuhong1120]

收藏，谢谢了

[烟波钓]

标记.....

[lgq_liang]

昨天,偶网络慢,居然没有过来.

[光脚丫思考]

收藏了！