INF: Identifying SPID Responsible for Lock Chain

• Microsoft SQL Server for OS/2, version 4.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" 

Additional query words: Windows NT Transact-SQL locking blocked

Keywords : kbother SSrvStProc SSrvWinNT
Version : 4.2 | 4.2
Platform : OS/2 WINDOWS
Issue type :