引言
今天在群里看到分享的解决死锁的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 (@bl ) 62 END 63 -- 循环指针下移 64 SET @intCounter = @intCounter + 1 65 END 66 DROP TABLE #tmp_lock_who 67 RETURN 0 68 END 69 go 70 EXEC sp_who_lock 71 DROP PROC sp_who_lock 72 GO 73 SET QUOTED_IDENTIFIER OFF 74 GO 75 76 SET ANSI_NULLS ON 77 78 GO
随时随地看视频
