引言

今天在群里看到分享的解决死锁的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 INT 14 CREATE TABLE #tmp_lock_who 15 ( 16 id INT IDENTITY(1, 1) , 17 spid SMALLINT , 18 bl SMALLINT 19 ) 20 IF @@ERROR <> 0 21 RETURN @@ERROR 22 INSERT INTO #tmp_lock_who ( spid, bl ) 23 SELECT 0, blocked 24 FROM ( SELECT * 25 FROM sys.sysprocesses 26 WHERE blocked > 0 27 ) a 28 WHERE NOT EXISTS ( SELECT * 29 FROM ( SELECT * 30 FROM sys.sysprocesses 31 WHERE blocked > 0 32 ) b 33 WHERE a.blocked = spid ) 34 UNION 35 SELECT spid, blocked 36 FROM sys.sysprocesses 37 WHERE blocked > 0 38 IF @@ERROR <> 0 39 RETURN @@ERROR 40 -- 找到临时表的记录数 41 SELECT @intCountProperties = COUNT(*), @intCounter = 1 42 FROM #tmp_lock_who 43 IF @@ERROR <> 0 44 RETURN @@ERROR 45 IF @intCountProperties = 0 46 SELECT N'现在没有阻塞和死锁信息' AS message 47 -- 循环开始 48 WHILE @intCounter <= @intCountProperties 49 BEGIN 50 -- 取第一条记录 51 SELECT @spid = spid, @bl = bl 52 FROM #tmp_lock_who 53 WHERE Id = @intCounter 54 BEGIN 55 IF @spid = 0 56 SELECT N'引起数据库死锁的是: ' + CAST(@bl AS VARCHAR(10)) 57 + N'进程号,其执行的SQL语法如下' 58 ELSE 59 SELECT N'进程号SPID：' + CAST(@spid AS VARCHAR(10)) 60 + N'被进程号SPID：' + CAST(@bl AS VARCHAR(10)) N'阻塞,其当前进程执行的SQL语法如下' 61 DBCC INPUTBUFFER (