Querying SQL 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 procedureSp_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 

Querying SQL Database deadlock