Querying a stored procedure for MS SQL Server database deadlock

Querying a stored procedure for SQL Server database deadlock

The use of SQL Server as a database application system will not be able to avoid the occasional deadlock. After the deadlock appears,
Most maintenance personnel or developers will only use sp_who to find the deadlock process and then kill it 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.

--Create or modify Sp_who_lock stored proceduresCREATE procedureSp_who_lock--ALTER 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 (IDint Identity(1,1), spidint, Blkint      )      if @ @error<>0 return @ @error      Insert  into#temp_who_lock (SPID,BLK)Select 0, blocked from(Select *  fromMaster.. sysprocesseswhereBlocked>0) Awhere  not exists(Select *  fromMaster.. sysprocesseswhereA.blocked=spid andBlocked>0)      Union SelectSpid,blocked fromMaster.. sysprocesseswhereBlocked>0      if @ @error<>0 return @ @error      Select @count=Count(*),@index=1  from#temp_who_lockif @ @error<>0 return @ @error      if @count=0      begin       Select 'No blocking and deadlock information'       return 0      End       while @index<=@count       begin       if exists(Select 1  from#temp_who_lock AwhereId>@index  and exists(Select 1  from#temp_who_lockwhereId<=@index  andA.blk=spid)) begin        Set @lock=1        Select @spid=spid@blk=Blk from#temp_who_lockwhereId=@index        Select 'causing the database deadlock is:'+ CAST(@spid  as VARCHAR(Ten))+ 'process number, which executes the following SQL syntax'        Select  @spid,@blk      DBCCInputBuffer (@spid)        DBCCInputBuffer (@blk)       End       Set @index=@index+1      End      if @lock=0       begin       Set @index=1        while @index<=@count        begin        Select @spid=spid@blk=Blk from#temp_who_lockwhereId=@index        if @spid=0         Select 'the blocking is caused by:'+cast(@blk  as varchar(Ten))+ 'process number, which executes the following SQL syntax'        Else          Select 'process number spid:'+ CAST(@spid  as VARCHAR(Ten))+ 'was' + 'process number spid:'+ CAST(@blk  as VARCHAR(Ten))+'The SQL syntax that the current process executes is blocked as follows'        DBCCInputBuffer (@spid)      DBCCInputBuffer (@blk)        Set @index=@index+1       End      End      Drop Table#temp_who_lockreturn 0     End           GO

Execute in Query Analyzer:
EXEC Sp_who_lock

Querying a stored procedure for MS SQL Server database deadlock