sql-server的死锁及处理方法.doc

【转】处理sqlserver的死锁

--第一篇

--检测死锁

--如果发生死锁了，我们怎么去检测具体发生死锁的是哪条SQL语句或存储过程？

--这时我们可以使用以下存储过程来检测，就可以查出引起死锁的进程和SQL语句。SQLServer自带的系统存储过程sp_who和sp_lock也可以用来查找阻塞和死锁,但没有这里介绍的方法好用。

?

usemaster

go

createproceduresp_who_lock

as

begin

declare@spidint,@blint,

?@intTransactionCountOnEntry?int,

???????@intRowcount???int,

???????@intCountProperties??int,

???????@intCounter???int

?createtable#tmp_lock_who(

?idintidentity(1,1),

?spidsmallint,

?blsmallint)

?

?IF@@ERROR0RETURN@@ERROR

?

?insertinto#tmp_lock_who(spid,bl)select?0,blocked

??from(select*fromsysprocesseswhere?blocked0)a

??wherenotexists(select*from(select*fromsysprocesseswhere?blocked0)b

??wherea.blocked=spid)

??unionselectspid,blockedfromsysprocesseswhere?blocked0

?IF@@ERROR0RETURN@@ERROR

?

--找到临时表的记录数

?select?@intCountProperties=Count(*),@intCounter=1

?from#tmp_lock_who

?

?IF@@ERROR0RETURN@@ERROR

?

?if@intCountProperties=0

?select现在没有阻塞和死锁信息asmessage

--循环开始

while@intCounter=@intCountProperties

begin

--取第一条记录

?select?@spid=spid,@bl=bl

?from#tmp_lock_whowhereId=@intCounter

?begin

?if@spid=0

???????????select引起数据库死锁的是:+CAST(@blASVARCHAR(10))+进程号,其执行的SQL语法如下

?else

???????????select进程号SPID：+CAST(@spidASVARCHAR(10))+被+进程号SPID：+CAST(@blASVARCHAR(10))+阻塞,其当前进程执行的SQL语法如下

?DBCCINPUTBUFFER(@bl)

?end

--循环指针下移

?set@intCounter=@intCounter+1

end

droptable#tmp_lock_who

return0

end

?

--杀死锁和进程

--如何去手动的杀死进程和锁？最简单的办法，重新启动服务。但是这里要介绍一个存储过程，通过显式的调用，可以杀死进程和锁。

usemaster

go

ifexists(select*fromdbo.sysobjectswhereid=object_id(N[dbo].[p_killspid])andOBJECTPROPERTY(id,NIsProcedure)=1)

dropprocedure[dbo].[p_killspid]

GO

createprocp_killspid

@dbnamevarchar(200)???--要关闭进程的数据库名

as?

???declare@sql?nvarchar(500)?

???declare@spidnvarchar(20)

???declare#tbcursorfor

???????selectspid=cast(spidasvarchar(20))frommaster..sysprocesseswheredbid=db_id(@dbname)

???open#tb

???fetchnextfr