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

[sql]代码库

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