引言

今天在群里看到分享的解决死锁的sql语句，就想着这东西以后肯定用的着，就下载下来，在这里记录一下，以后查找也方便。

SQL

1 SET QUOTED_IDENTIFIER ON 2 GO 3 SET ANSI_NULLS ON 4 GO 5 CREATE PROCEDURE sp_who_lock 6 AS 7     BEGIN 8         DECLARE @spid INT , 9             @bl INT ,10             @intTransactionCountOnEntry INT ,11             @intRowcount INT ,12             @intCountProperties INT ,13             @intCounter INT14         CREATE TABLE #tmp_lock_who15             (16               id INT IDENTITY(1, 1) ,17               spid SMALLINT ,18               bl SMALLINT19             )20          IF @@ERROR <> 021             RETURN @@ERROR22         INSERT INTO #tmp_lock_who ( spid, bl )23                 SELECT 0, blocked24                     FROM ( SELECT *25                             FROM sys.sysprocesses26                             WHERE blocked > 027                          ) a28                     WHERE NOT EXISTS ( SELECT *29                                         FROM ( SELECT *30                                                 FROM sys.sysprocesses31                                                 WHERE blocked > 032                                              ) b33                                         WHERE a.blocked = spid )34                 UNION35                 SELECT spid, blocked36                     FROM sys.sysprocesses37                     WHERE blocked > 038         IF @@ERROR <> 039             RETURN @@ERROR40        -- 找到临时表的记录数41         SELECT @intCountProperties = COUNT(*), @intCounter = 142             FROM #tmp_lock_who43          IF @@ERROR <> 044             RETURN @@ERROR45         IF @intCountProperties = 046             SELECT N'现在没有阻塞和死锁信息' AS message47        -- 循环开始48         WHILE @intCounter <= @intCountProperties49             BEGIN50               -- 取第一条记录51                 SELECT @spid = spid, @bl = bl52                     FROM #tmp_lock_who53                     WHERE Id = @intCounter54                 BEGIN55                     IF @spid = 056                         SELECT N'引起数据库死锁的是: ' + CAST(@bl AS VARCHAR(10))57                                 + N'进程号,其执行的SQL语法如下'58                     ELSE59                         SELECT N'进程号SPID：' + CAST(@spid AS VARCHAR(10))60                                 + N'被进程号SPID：' + CAST(@bl AS VARCHAR(10)) N'阻塞,其当前进程执行的SQL语法如下'61                     DBCC INPUTBUFFER (@bl )62                 END63               -- 循环指针下移64                 SET @intCounter = @intCounter + 165             END66         DROP TABLE #tmp_lock_who67         RETURN 068     END69 go70 EXEC sp_who_lock71 DROP PROC  sp_who_lock72 GO73 SET QUOTED_IDENTIFIER OFF74 GO75 76 SET ANSI_NULLS ON77 78 GO