[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

1 SETQuoted_identifier on2 GO3 SETAnsi_nulls on4 GO5 CREATE PROCEDURESp_who_lock6  as7     BEGIN8         DECLARE @spid INT ,9             @bl INT ,Ten             @intTransactionCountOnEntry INT , One             @intRowcount INT , A             @intCountProperties INT , -             @intCounter INT -         CREATE TABLE#tmp_lock_who the             ( -IdINT IDENTITY(1,1) , -spidSMALLINT , -BlSMALLINT +             ) -          IF @ @ERROR <> 0 +             RETURN @ @ERROR A         INSERT  into#tmp_lock_who (spid, BL) at                 SELECT 0, blocked -                      from(SELECT * -                              fromsys.sysprocesses -                             WHEREBlocked> 0 - ) A -                     WHERE  not EXISTS(SELECT * in                                          from(SELECT * -                                                  fromsys.sysprocesses to                                                 WHEREBlocked> 0 + ) b -                                         WHEREA.blocked=spid) the                 UNION *                 SELECTspid, blocked $                      fromsys.sysprocessesPanax Notoginseng                     WHEREBlocked> 0 -         IF @ @ERROR <> 0 the             RETURN @ @ERROR +        --number of records found for temporary table A         SELECT @intCountProperties = COUNT(*),@intCounter = 1 the              from#tmp_lock_who +          IF @ @ERROR <> 0 -             RETURN @ @ERROR $         IF @intCountProperties = 0 $             SELECTN'No blocking and deadlock information now'  asmessage -        --Cycle Start -          while @intCounter <= @intCountProperties the             BEGIN -               --take the first recordWuyi                 SELECT @spid =spid@bl =BL the                      from#tmp_lock_who -                     WHEREId= @intCounter Wu                 BEGIN -                     IF @spid = 0 About                         SELECTN'causing the database deadlock is:' + CAST(@bl  as VARCHAR(Ten)) $                                 +N'process number, which executes the following SQL syntax' -                     ELSE -                         SELECTN'process number spid:' + CAST(@spid  as VARCHAR(Ten)) -                                 +N'by the process number spid:' + CAST(@bl  as VARCHAR(Ten)) N'The SQL syntax that the current process executes is blocked as follows' A                     DBCCInputBuffer (@bl ) +                 END the               --The loop pointer moves down -                 SET @intCounter = @intCounter + 1 $             END the         DROP TABLE#tmp_lock_who the         RETURN 0 the     END the Go - EXECSp_who_lock in DROP PROCSp_who_lock the GO the SETQuoted_identifierOFF About GO the  the SETAnsi_nulls on the  + GO