INF: Identifying SPID Responsible for Lock Chain

• Microsoft SQL Server version 4.2 for OS/2
• Microsoft SQL Server, version 4.2

SUMMARY

Processes may block on locks held by another process, which in turn, is blocked by another process. If many processes are involved in a long chain of locks, it may be difficult to determine which process is ultimately responsible for the blocking by tracing through the sp_who output.

MORE INFORMATION

The following stored procedure can be used to determine which process is ultimately responsible for blocking a given process:

   create proc chaintrace @spid smallint
   as
   declare @current_spid smallint
   declare @blocker_spid smallint

   select @current_spid = @spid

   select @blocker_spid = blocked from master.dbo.sysprocesses where spid =
    @current_spid

   while @blocker_spid != 0
   begin
      select @current_spid = @blocker_spid
      select @blocker_spid = blocked from master.dbo.sysprocesses where
    spid = @current_spid
   end

   select "process: "+convert( char, @current_spid )+ " at root of lock
   chain"