sqlserver的死锁及处理方法.docVIP

【转】处理sql server的死锁 --第一篇 --检测死锁 --如果发生死锁了，我们怎么去检测具体发生死锁的是哪条SQL语句或存储过程？ --这时我们可以使用以下存储过程来检测，就可以查出引起死锁的进程和SQL语句。SQL Server自带的系统存储过程sp_who和sp_lock也可以用来查找阻塞和死锁, 但没有这里介绍的方法好用。 ? use mastergocreate procedure sp_who_lockasbegindeclare @spid int,@bl int,?@intTransactionCountOnEntry? int,??????? @intRowcount??? int,??????? @intCountProperties?? int,??????? @intCounter??? int ?create table #tmp_lock_who (?id int identity(1,1),?spid smallint,?bl smallint)??IF @@ERROR0 RETURN @@ERROR??insert into #tmp_lock_who(spid,bl) select? 0 ,blocked?? from (select * from sysprocesses where? blocked0 ) a ?? where not exists(select * from (select * from sysprocesses where? blocked0 ) b ?? where a.blocked=spid)?? union select spid,blocked from sysprocesses where? blocked0 ?IF @@ERROR0 RETURN @@ERROR ? -- 找到临时表的记录数?select? @intCountProperties = Count(*),@intCounter = 1?from #tmp_lock_who??IF @@ERROR0 RETURN @@ERROR ??if @intCountProperties=0? select 现在没有阻塞和死锁信息 as message -- 循环开始while @intCounter = @intCountPropertiesbegin-- 取第一条记录? select? @spid = spid,@bl = bl? from #tmp_lock_who where Id = @intCounter ?begin? if @spid =0 ??????????? select 引起数据库死锁的是: + CAST(@bl AS VARCHAR(10)) + 进程号,其执行的SQL语法如下?else??????????? select 进程号SPID：+ CAST(@spid AS VARCHAR(10))+ 被 + 进程号SPID：+ CAST(@bl AS VARCHAR(10)) +阻塞,其当前进程执行的SQL语法如下?DBCC INPUTBUFFER (@bl )?end -- 循环指针下移?set @intCounter = @intCounter + 1end drop table #tmp_lock_who return 0end ? --杀死锁和进程 --如何去手动的杀死进程和锁？最简单的办法，重新启动服务。但是这里要介绍一个存储过程，通过显式的调用，可以杀死进程和锁。 use mastergo if exists (select * from dbo.sysobjects where id = object_id(N[dbo].[p_killspid]) and OBJECTPROPERTY(id, NIsProcedure) = 1)drop procedure [dbo].[p_killspid]GO create proc p_killspid@dbname varchar(200)??? --要关闭进程的数据库名as? ??? declare @sql? nvarchar(500)? ??? declare @spid nvarchar(20) ??? declare #tb cursor for??????? select spid=cast(spid as varchar(20)) from master..sysprocesses where dbid=db_id(@dbname)??? open #tb??? fetch next fr