查询Sqlserver数据库死锁的一个存储过程

概述原文： 点击打开链接 /************************************************************ * Code formatted by SoftTree SQL Assistant ?v6.1.35 * Time: 2013/11/12 16:48:00 ********************************************

原文： 点击打开链接

/************************************************************ * Code formatted by SoftTree sql Assistant ?v6.1.35 * Time: 2013/11/12 16:48:00 ************************************************************/IF OBJECT_ID('proc_who_lock') IS NOT NulLBEGIN    DROP PROC proc_who_lockENDGOCREATE PROCEDURE proc_who_lockASBEGIN	DECLARE @spID INT     	DECLARE @blk INT     	DECLARE @count INT     	DECLARE @index INT     	DECLARE @lock tinyint      	SET @lock = 0      	CREATE table #temp_who_lock	(		ID       INT IDENTITY(1,1),spID     INT,blk      INT	)      	IF @@error <> 0	    RETURN @@error		INSERT INTO #temp_who_lock	  (	    spID,blk	  )	SELECT 0,blocked	FROM   (	           SELECT *	           FROM   MASTER..sysprocesses	           WHERE  blocked > 0	       )a	WHERE  NOT EXISTS(	           SELECT *	           FROM   MASTER..sysprocesses	           WHERE  a.blocked = spID	                  AND blocked > 0	       ) 	UNION	SELECT spID,blocked	FROM   MASTER..sysprocesses	WHERE  blocked > 0		IF @@error <> 0	    RETURN @@error		SELECT @count = COUNT(*),@index = 1	FROM   #temp_who_lock		IF @@error <> 0	    RETURN @@error		IF @count = 0	BEGIN	    SELECT '没有阻塞和死锁信息' 	    RETURN 0	END		WHILE @index <= @count	BEGIN	    IF EXISTS(	           SELECT 1	           FROM   #temp_who_lock a	           WHERE  ID > @index	                  AND EXISTS(	                          SELECT 1	                          FROM   #temp_who_lock	                          WHERE  ID <= @index	                                 AND a.blk = spID	                      )	       )	    BEGIN	        SET @lock = 1      	        SELECT @spID = spID,@blk = blk	        FROM   #temp_who_lock	        WHERE  ID = @index	        	        SELECT '引起数据库死锁的是: ' + CAST(@spID AS VARCHAR(10)) + 	               '进程号,其执行的sql语法如下'	        	        SELECT @spID,@blk	        	        DBCC inputBUFFER(@spID) 	        DBCC inputBUFFER(@blk)	    END	    	    SET @index = @index + 1	END     	IF @lock = 0	BEGIN	    SET @index = 1      	    WHILE @index <= @count	    BEGIN	        SELECT @spID = spID,@blk = blk	        FROM   #temp_who_lock	        WHERE  ID = @index	        	        IF @spID = 0	            SELECT '引起阻塞的是:' + CAST(@blk AS VARCHAR(10)) + 	                   '进程号,其执行的sql语法如下'	        ELSE	            SELECT '进程号SPID：' + CAST(@spID AS VARCHAR(10)) + '被' + 	                   '进程号SPID：' + CAST(@blk AS VARCHAR(10)) + 	                   '阻塞,其当前进程执行的sql语法如下'	        	        DBCC inputBUFFER(@spID) 	        DBCC inputBUFFER(@blk)      	        SET @index = @index + 1	    END	END		DROP table #temp_who_lock 	RETURN 0ENDGO EXEC proc_who_lock

总结

以上是内存溢出为你收集整理的查询Sqlserver数据库死锁的一个存储过程全部内容，希望文章能够帮你解决查询Sqlserver数据库死锁的一个存储过程所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。