查看mysql process id_检查锁定SQL Server数据库的Process ID

检查锁定SQLServer数据库的ProcessID 无 CREATE PROCEDURE #sp_who_lockASBEGINDECLARE @spid INTDECLARE @blk INTDECLARE @count INTDECLARE @index INTDECLARE @lock TINYINT ??SET @lock = 0 ??DECLARE @temp_who_lock AS TABLE (id INT identity(1, 1),sp

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

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中文网，转载请注明出处，感谢您的尊重！