分析死锁并处理_整理贴5 《转帖》

2017-10-26 12:22:44来源:cnblogs.com作者:zlisa人点击

分享


--死锁
/******************************************************************************************************************************************************
死锁指两个以上事务相互阻塞相互等待对方释放它们的锁,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://bbs.csdn.net/topics/250022124

附:阻塞分析:http://blog.csdn.net/roy_88/archive/2008/07/21/2682044.aspx

最新文章

123

最新摄影

闪念基因

微信扫一扫

第七城市微信公众平台