SQLServer查看死锁

SQLServer查看死锁

if exists (
       select *
       from   sys.procedures
       where  name like '%USP_ShowLocks%'
   )
    drop procedure dbo.USP_ShowLocks
go

create procedure dbo.USP_ShowLocks
as
begin
    create table #t
    (
        req_spid     int
       ,obj_name     sysname
    )  
    
    declare @s           nvarchar(4000)
           ,@rid         int
           ,@dbname      sysname
           ,@id          int
           ,@objname     sysname  
    
    declare tb cursor  
    for
        select distinct req_spid
              ,dbname = db_name(rsc_dbid)
              ,rsc_objid
        from   master..syslockinfo
        where  rsc_type   in (4 ,5)
    
    open tb 
    fetch next from tb into @rid,@dbname,@id  
    while @@fetch_status = 0
    begin
        set @s = 'select   @objname=name   from   [' + @dbname + ']..sysobjects   where   id=@id' 
        exec sp_executesql @s
            ,N'@objname   sysname   out,@id   int'
            ,@objname out
            ,@id
        
        insert into #t
        values
          (
            @rid
           ,@objname
          )
        fetch next from tb into @rid,@dbname,@id
    end 
    close tb 
    deallocate tb  
    
    select process_id = a.req_spid
          ,databaseName = db_name(rsc_dbid)
          ,typeName = case rsc_type
                        when 1 then 'NULL   Resource（Not Use）'
                        when 2 then 'DataBase'
                        when 3 then 'File'
                        when 4 then 'Index'
                        when 5 then 'Table'
                        when 6 then 'Page'
                        when 7 then 'Key'
                        when 8 then 'Extend Disk '
                        when 9 then 'RID（Row ID)'
                        when 10 then 'Application Program'
                      end
          ,objectID = rsc_objid
          ,objectName = b.obj_name
          ,rsc_indid
    from   master..syslockinfo a
           left   join #t b on  a.req_spid = b.req_spid
    where  db_name(rsc_dbid) = 'siteweaver'

    drop table #t

    --show all locks
    SELECT request_session_id as spid, 
    ObjectName = case when resource_type='OBJECT' then OBJECT_NAME(resource_associated_entity_id) else cast(resource_associated_entity_id as varchar(200)) end
     ,*
    FROM sys.dm_tran_locks
    WHERE resource_type in('OBJECT')
    --WHERE resource_type in('KEY','PAGE','OBJECT')
    

/*  SELECT request_session_id, resource_type, resource_associated_entity_id,
    request_status, request_mode, resource_description
    FROM sys.dm_tran_locks
    where resource_type in('KEY','PAGE','OBJECT') */

end
go


/*杀掉死锁的进程*/
--kill 75