Release date: 2008.02.01 Source: CCID Author: Alice |
If a deadlock occurs, how can we check which SQL statement or stored procedure is causing the deadlock? In this case, we can use the following stored procedure to detect the process and SQL statements that cause the deadlock. |
Use mastergocreate procedure sp_who_lockasbegindeclare @ spid int, @ BL int, @ inttransactioncountonentry int, @ introwcount int, @ intcountproperties int, @ intcounter intcreate table # tmp_lock_who (ID int identity (1,1 ), spid smallint, BL smallint) If @ error <> 0 return @ errorinsert into # tmp_lock_who (spid, BL) Select 0, blockedfrom (select * From sysprocesses where blocked> 0) A Where not exists (select * from (select * From sysprocesses where blocked> 0) B where. blocked = spid) Union select spid, blocked from sysprocesses where blocked> 0if @ error <> 0 return @ error -- find the number of records in the temporary table select @ intcountproperties = count (*), @ intcounter = 1 from # tmp_lock_whoif @ error <> 0 return @ error if @ intcountproperties = 0 select 'no blocking and deadlock information' as message -- the loop starts while @ intcounter <= @ intcountpropertiesbegin -- Take the first record select @ spid = spid, @ BL = blfrom # tmp_lock_who where id = @ intcounter beginif @ spid = 0 select 'causes a database deadlock:' + Cast (@ BL as varchar (10 )) + 'process No. The SQL syntax it executes is as follows: 'elselect' process no. spid: '+ Cast (@ spid as varchar (10) + 'be' +' process no. spid: '+ Cast (@ BL as varchar (10) +' blocking. The SQL syntax executed by the current process is as follows: 'dbcc inputbuffer