数据库死锁

USE [LenovoWMS_Main]
GO
/****** Object:  StoredProcedure [dbo].[sp_who_lock]    Script Date: 2017/7/21 13:45:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO




ALTER PROCEDURE [dbo].[sp_who_lock]
(
@lockSql NVARCHAR(MAX ) OUTPUT
)
AS 
    BEGIN   
    SET @lockSql=''  
        DECLARE @spid INT     
        DECLARE @blk INT     
        DECLARE @count INT     
        DECLARE @index INT     
        DECLARE @lock TINYINT   
    
        


        SET @lock = 0      
        CREATE TABLE #temp_who_lock
            (
              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 *
                --                     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     

                    --查询当前阻塞
;
    WITH    CTE_SID ( BSID, SID, sql_handle )
              AS ( 
              SELECT   blocking_session_id ,
                            session_id ,
                            sql_handle
                   FROM     sys.dm_exec_requests
                   WHERE    blocking_session_id <> 0
                   UNION ALL
                   SELECT   A.blocking_session_id ,
                            A.session_id ,
                            A.sql_handle
                   FROM     sys.dm_exec_requests A
                            JOIN CTE_SID B ON A.SESSION_ID = B.BSID
                 )
        SELECT  
                @lockSql=q.text
        FROM    CTE_SID C 
                JOIN sys.dm_exec_sessions S ON C.sid = s.session_id
                CROSS APPLY sys.dm_exec_sql_text(C.sql_handle) Q
        ORDER BY sid

    
          

                IF EXISTS ( SELECT  1
                            FROM    #temp_who_lock a
                            WHERE   id > @index
                                    AND EXISTS ( SELECT 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     
                            SET @lockSql='被阻塞的sql语句:'+@lockSql+ ',引起数据库死锁的是: ' + CAST(@spid AS VARCHAR(10))
                                + '进程号,其执行的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     
                            SET @lockSql='被阻塞的sql语句:'+@lockSql+ ',引起阻塞的是:' + CAST(@blk AS VARCHAR(10))+
                                    + '进程号,其执行的SQL语法如下'    

                        DBCC INPUTBUFFER(@blk)      
                        SET @index = @index + 1      
                    END     
            END     
        DROP TABLE #temp_who_lock      
        RETURN 0      
    END           

查询出来

select   
    request_session_id spid,  
    OBJECT_NAME(resource_associated_entity_id) tableName   
from   
    sys.dm_tran_locks  
where   
    resource_type='OBJECT 

杀死死锁进程

kill spid