[SQL] Deadlock processing statement

Introduction

Today in the group to see the resolution of the deadlock of the SQL statement, just think of this thing will definitely use, download down, here to record, later find also convenient.

Sql

1SET QUOTED_IDENTIFIEROn2GO3SET ANSI_NULLSOn4GO5CREATEPROCEDURESp_who_lock6As7BEGIN8DECLARE@spidInt,9@blInt,10@intTransactionCountOnEntryInt,11@intRowcountInt,12@intCountPropertiesInt,13@intCounterInt14CREATETABLE#tmp_lock_who15(IdIntIDENTITY (1,1) ,spidSMALLINT,BlSMALLINT19)20IF@ @ERROR<>021stRETURN@ @ERROR22INSERTInto#tmp_lock_who (spid, BL)23SELECT0, blocked24From (SELECT*25FromSys.sysprocesses26WHERE blocked>027) A28WHERENotEXISTS (SELECT*29From (SELECT*30FromSys.sysprocesses31WHERE blocked>032) b33WHERE a.blocked=SPID)34UNION35SELECTSPID, blocked36FromSys.sysprocesses37WHERE blocked>038IF@ @ERROR<>039RETURN@ @ERROR40--Number of records found for temporary table41SELECT@intCountProperties=COUNT (*),@intCounter=142From#tmp_lock_who43IF@ @ERROR<>044RETURN@ @ERROR45IF@intCountProperties=046SELECT N‘No blocking and deadlock information now‘AsMessage47--Cycle start48While@intCounter<=@intCountProperties49BEGIN50--Take the first record51SELECT@spid= spid,@bl=Bl52From#tmp_lock_who53WHERE Id=@intCounter54BEGIN55IF@spid=056SELECT N‘Causing the database deadlock is:‘+CAST (@blAsVARCHAR (10))57+ N‘Process number, which executes the following SQL syntax‘58ELSE59SELECT N‘Process number spid:‘+CAST (@spidAsVARCHAR (10))60+ N‘By the process number spid:‘+CAST (@blAsVARCHAR ((ten)) N‘The SQL syntax that the current process executes is blocked as follows‘61DBCC InputBuffer (@bl)62END63--The loop pointer moves down64SET@intCounter=@intCounter+165END66DROPTABLE#tmp_lock_who67RETURN068END sp_who_lock Go, EXEC , DROP PROC sp_who_lock, go,   SET QUOTED_IDENTIFIER OFF ansi_nulls go  SET    to On  

[SQL] Deadlock processing statement