Querying a stored procedure for SQL Server database deadlock

The use of SQL Server as a database application system, will not be able to sometimes create a deadlock, after the deadlock, the maintenance personnel or developers will mostly only through the sp_who to find the deadlock process, and then killed with Sp_kill. With Sp_who_lock This stored procedure, it is easy to know which process has deadlock, where the problem of deadlock occurs.

Creating Sp_who_lock Stored Procedures

CREATE procedure sp_who_lock   as     begin        declare @spid int        declare @blk int        declare @count int        declare @index int        declare @lock tinyint         set @lock=0         create table #temp_who_lock       (        id int identity(1,1),        spid int,        blk int      )       if @@error<>0 return @@error       insert into #temp_who_lock(spid,blk)       select 0 ,blocked       &NBSP; from   ( select   * from  master: sysprocesses where  blocked>0) a       &NBSP; where  not  exists ( select  * from   master: sysprocesses where  a.blocked =spid Code class= "SQL Color1" >and  blocked>0)        union select spid,blocked from  master..sysprocesses where blocked>0       if @@error<>0 return @@error       select @count=count(*),@index=1 from #temp_who_lock       if @@error<>0 return @@error       if @count=0       begin       select ‘没有阻塞和死锁信息‘       return 0       end      while @index<[email protected]count       begin      &NBSP;&NBSP; if Exists ( select  1 from   #temp_who_lock a where  id>@ Index  and  exists ( select  1 from  < Code class= "SQL plain" > #temp_who_lock where   Id<[email protected] index  and  a.blk=spid)         begin        set @lock=1         select @spid=spid,@blk=blk from #temp_who_lock where [email protected]index        select ‘引起数据库死锁的是: ‘+ CAST(@spid AS VARCHAR(10)) + ‘进程号,其执行的SQL语法如下‘        select  @spid, @blk       dbcc inputbuffer(@spid)         dbcc inputbuffer(@blk)        end       set @index[email protected]index+1       end      if @lock=0        begin       set @index=1        while @index<[email protected]count        begin        select @spid=spid,@blk=blk from #temp_who_lock where [email protected]index        if @spid=0          select ‘引起阻塞的是:‘+cast(@blk as varchar(10))+ ‘进程号,其执行的SQL语法如下‘        else       &NBSP;&NBSP;&NBSP;&NBSP; select   "process number spid: ' + cast (@spid as   VARCHAR ' by '   + ' process number spid: ' + cast (@blk as  varchar           dbcc inputbuffer(@spid)       dbcc inputbuffer(@blk)         set @index[email protected]index+1        end      end      drop table #temp_who_lock       return 0      end             GO

Execute in Query Analyzer:

EXEC Sp_who_lock

Until the final result is:

Article address

Querying a stored procedure for SQL Server database deadlock