Query a stored procedure sharing of SQL Server database deadlocks. SQL Server Stored Procedure

Query a stored procedure sharing of SQL Server database deadlocks. SQL Server Stored Procedure

When SQL Server is used as the database application system, it cannot avoid deadlocks. When deadlocks occur, maintenance personnel or developers only use sp_who to find deadlocks, then use sp_kill to kill. Using sp_who_lock, you can easily know which process has a deadlock and where the deadlock occurs.

Create sp_who_lock Stored Procedure

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 from (select * from master .. sysprocesses where blocked> 0) a where not exists (select * from master .. sysprocesses where. blocked = spid 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 'no blocking or deadlock information 'Return 0 end while @ index <= @ count begin if exists (select 1 from # temp_who_lock a where id> @ index and exists (select 1 from # temp_who_lock where id <= @ index and. blk = spid) begin set @ lock = 1 select @ spid = spid, @ blk = blk from # temp_who_lock where id = @ index select 'causes a database deadlock: '+ CAST (@ spid as varchar (10) +' process number. The SQL syntax is AS follows: 'select @ spid, @ blk dbcc inputbuffer (@ spid) dbcc inputbuffer (@ 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_lock where id = @ index if @ spid = 0 select 'causes blocking:' + cast (@ blk as varchar (10 )) + 'process No. The SQL syntax it executes is AS follows: 'else select' process no. SPID: '+ CAST (@ spid AS VARCHAR (10 )) + 'blocked by' + 'process number SPID:' + CAST (@ blk as varchar (10) +, the SQL syntax of the current process is as follows: 'dbcc inputbuffer (@ spid) dbcc inputbuffer (@ blk) set @ index = @ index + 1 end drop table # temp_who_lock return 0 end GO

Run the following command in the query Analyzer:

Exec sp_who_lock

Until the final result is :**