检查锁定SQL Server数据库的Process ID

以下代码由PHP站长网 52php.cn收集自互联网

现在PHP站长网小编把它分享给大家，仅供参考

CREATE PROCEDURE #sp_who_lock
AS
BEGIN
	DECLARE @spid INT
	DECLARE @blk INT
	DECLARE @count INT
	DECLARE @index INT
	DECLARE @lock TINYINT ?
?
	SET @lock = 0 ?
?
	DECLARE @temp_who_lock AS TABLE (
		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 TOP 1 1
			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(1),@index = 1
	FROM @temp_who_lock ?
?
	IF @@error <> 0
		RETURN @@error ?
?
	IF @count = 0
	BEGIN
		SELECT N'没有阻塞和死锁信息' ?
?
		RETURN 0
	END ?
?
	WHILE @index <= @count
	BEGIN
		IF EXISTS (
				SELECT TOP 1 1
				FROM @temp_who_lock a
				WHERE id > @index
					AND EXISTS (
						SELECT TOP 1 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 N'引起数据库死锁的是:' + CAST(@spid AS NVARCHAR(10)) + N'进程号,其执行的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 N'引起阻塞的是:' + CAST(@blk AS NVARCHAR(10)) + N'进程号,其执行的SQL语法如下'
			ELSE
				SELECT N'进程号SPID：' + CAST(@spid AS NVARCHAR(10)) + N'被进程号SPID：' + CAST(@blk AS NVARCHAR(10)) + N'阻塞,其当前进程执行的SQL语法如下' ?
?
			DBCC INPUTBUFFER (@spid) ?
?
			DBCC INPUTBUFFER (@blk) ?
?
			SET @index = @index + 1
		END
	END ?
?
	RETURN 0
END
GO
?
EXEC #sp_who_lock

以上内容由PHP站长网【52php.cn】收集整理供大家参考研究

如果以上内容对您有帮助，欢迎收藏、点赞、推荐、分享。

（编辑：辽源站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!