Diagnosis and identification of SQL Server deadlocks

Diagnosis and identification of SQL Server deadlocks

Deadlock often occurs in sqlserver. You must connect to the Enterprise Manager> Manage> current activity> lock/process ID to find the deadlock process and locate the cause of the deadlock.
The query analyzer must also go through multiple system tables (sysprocesses, sysobjects, etc.) and system stored procedures (sp_who, sp_who2, sp_lock, etc.), and may not be directly located.

This stored procedure can be found in the sp_lock_check and sysprocesses system tables. At the same time, the DBCC command is used to list the deadlock and deadlock-related processes and related statements for ease of analysis and locating.

Create procedure sp_check_deadlock As Set nocount on /* Select Spid Locked process ID, Blocked lock process ID, Status Locked, Substring (suser_sname (SID),) locked process login account, Substring (hostname, 1, 12) Name of the locked process user machine, Substring (db_name (dbid), 1, 10) Lock process data name, Command for the CMD process to be locked, Waittype lock process wait type From master .. sysprocesses Where blocked> 0 -- DBCC inputbuffer (66) Statement for outputting the lock Process */ -- Create a temporary table for the lock Process Create Table # templocktracestatus ( Eventtype Varchar (100 ), Parameters int, Eventinfo Varchar (200) ) -- Create a temporary table of locked Processes Create Table # tempbelocktracestatus ( Eventtype Varchar (100 ), Parameters int, Eventinfo Varchar (200) ) -- Create an association table Create Table # locktracestatus ( Belockspid int, Belockspidremark varchar (20 ), Belockeventtype Varchar (100 ), Belockeventinfo Varchar (200 ), Lockspid int, Lockspidremark Varchar (20 ), Lockeventtype Varchar (100 ), Lockeventinfo Varchar (200) ) -- Obtain the deadlock Process Declare dbcc_inputbuffer cursor read_only For select spid lock process ID and blocked lock process ID From master .. sysprocesses Where blocked> 0 Declare @ lockedspid int Declare @ belockedspid int Open dbcc_inputbuffer Fetch next from dbcc_inputbuffer into @ belockedspid, @ lockedspid While (@ fetch_status <>-1) Begin If (@ fetch_status <>-2) Begin -- Print 'blocked Process' -- Select @ belockedspid -- DBCC inputbuffer (@ belockedspid) -- Print 'blocking Process' -- Select @ lockedspid -- DBCC inputbuffer (@ lockedspid) Insert into # tempbelocktracestatus Exec ('dbcc inputbuffer ('+ @ belockedspid + ')') Insert into # templocktracestatus Exec ('dbcc inputbuffer ('+ @ lockedspid + ')') Insert into # locktracestatus Select @ belockedspid, 'locked process', A. eventtype, A. eventinfo, @ lockedspid, 'locked process', B. eventtype, B. eventinfo From # tempbelocktracestatus A, # templocktracestatus B End Fetch next from dbcc_inputbuffer into @ belockedspid, @ lockedspid End Close dbcc_inputbuffer Deallocate dbcc_inputbuffer Select * from # locktracestatus Return (0) -- sp_check_deadlock

Execute this stored procedure

Exec sp_check_deadlock

 

From: http://www.itpub.net/thread-1007822-1-1.html