检查锁定SQL Server数据库的Process ID
发布时间:2020-12-11 23:13:51 所属栏目:MySql教程 来源:网络整理
导读:今天PHP站长网 52php.cn把收集自互联网的代码分享给大家,仅供参考。 CREATE PROCEDURE #sp_who_lockASBEGINDECLARE @spid INTDECLARE @blk INTDECLARE @count INTDECLARE @index INTDECLARE @lock TINYINT ??SET @lock =
以下代码由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】收集整理供大家参考研究 如果以上内容对您有帮助,欢迎收藏、点赞、推荐、分享。 (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |