mssql sqlserver 使用sql脚本查看表死锁的方法分享


摘要:
下文讲述使用sql脚本获取表死锁的方法分享,如下所示:
实验环境:sql server 2008 R2


 
----方法1:使用sys.dm_tran_locks 系统表获取死锁进程Id 及 所对应的表名 
SELECT  request_session_id as [进程Id] ,
        OBJECT_NAME(resource_associated_entity_id)  as [表名]
FROM    sys.dm_tran_locks
WHERE   resource_type = 'OBJECT'
  
----方法2: 使用存储过程获取死锁表的方法分享

create proc   sp_lock_search
AS 
    BEGIN     
        DECLARE @spid INT     
        DECLARE @blk INT     
        DECLARE @count INT     
        DECLARE @index INT     
        DECLARE @lock TINYINT      
        SET @lock = 0      
        CREATE TABLE #tmp_lock_info
            (
              id INT IDENTITY(1, 1) ,
              spid INT ,
              blk INT
            )      
        IF @@error <> 0 
            RETURN @@error      
        INSERT  INTO #tmp_lock_info
                ( 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    #tmp_lock_info a
                            WHERE   id > @index
                                    AND EXISTS ( SELECT 1
                                                 FROM   #tmp_lock_info
                                                 WHERE  id <= @index
                                                        AND a.blk = spid ) ) 
                    BEGIN     
                        SET @lock = 1      
                        SELECT  @spid = spid ,
                                @blk = blk
                        FROM    #tmp_lock_info
                        WHERE   id = @index     
                        SELECT  '死锁进程Id: ' + CAST(@spid AS VARCHAR(88)) 
                       --- DBCC inputbuffer(@spid)      --死锁进程Id
                        ---DBCC inputbuffer(@blk)        --阻塞进程Id
                    END     
                SET @index = @index + 1      
            END     
        IF @lock = 0 
            BEGIN     
                SET @index = 1      
                WHILE @index <= @count 
                    BEGIN     
                        SELECT  @spid = spid ,
                                @blk = blk
                        FROM    #tmp_lock_info
                        WHERE   id = @index     
                        IF @spid = 0 
                            SELECT  '阻塞Id:' + CAST(@blk AS VARCHAR(88)) 
                        ELSE 
                            SELECT  '进程ID:' + CAST(@spid AS VARCHAR(88))
                                    + '被' + '进程号SPID:'
                                    + CAST(@blk AS VARCHAR(10))
                                    + '阻塞'     
                          --- DBCC inputbuffer(@spid)      --死锁进程Id
                        ---DBCC inputbuffer(@blk)        --阻塞进程Id    
                        SET @index = @index + 1      
                    END     
            END     
        DROP TABLE #tmp_lock_info      
        RETURN 0      
    END           
GO


exec sp_lock_search 
go