SQLServer查看死锁的表和Kill死锁进程

约定不等于承诺〃 2023-07-10 11:24 15阅读 0赞

### SQLServer查看死锁的表和Kill死锁进程 ###

*  1、查询出现死锁的表
 *  2、杀死某个锁
 *  3、也可以创建查看死锁进程的存储过程
 *  4、也可以穿件杀掉引起死锁进程的存储过程

# 1、查询出现死锁的表 #

查询语句

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

查询结果如下  
![在这里插入图片描述][20200302113851337.png]

# 2、杀死某个锁 #

要想杀死某个锁需要使用kill spid  
语句如下

kill 56
    go
    kill 54

# 3、也可以创建查看死锁进程的存储过程 #

代码如下

CREATE  PROCEDURE [dbo].[sp_who_lock]
    AS
        BEGIN
    
            DECLARE @spid INT ,
                @bl INT ,
                @intTransactionCountOnEntry INT ,
                @intRowcount INT ,
                @intCountProperties INT ,
                @intCounter INT,
                @sql_handle VARBINARY(64)
    
            DECLARE @tmp_lock_who TABLE 
                (
                  id INT IDENTITY(1, 1) ,
                  spid SMALLINT ,
                  bl SMALLINT,
                  sql_handle VARBINARY(64)
                )
     
            IF @@ERROR <> 0
                RETURN @@ERROR
            ;
            WITH tb_blocked AS(
                SELECT spid, blocked, sql_handle FROM master..sysprocesses WHERE blocked > 0
            )
            INSERT  INTO @tmp_lock_who
                    ( spid ,
                      bl, sql_handle
                    )
            SELECT DISTINCT  blocked,0, p_bl.sql_handle
            FROM    tb_blocked
                CROSS APPLY (SELECT p_bl.sql_handle FROM master..sysprocesses p_bl WHERE p_bl.spid = tb_blocked.blocked) p_bl
            WHERE   NOT EXISTS ( SELECT *
                                    FROM   tb_blocked a
                                    WHERE  tb_blocked.blocked = a.spid )
            UNION ALL
            SELECT spid, blocked, sql_handle FROM tb_blocked
    
            IF @@ERROR <> 0
                RETURN @@ERROR
     
    		-- 找到临时表的记录数
            SELECT  @intCountProperties = COUNT(*),
                    @intCounter = 1
            FROM    @tmp_lock_who
     
            IF @@ERROR <> 0
                RETURN @@ERROR
     
            IF @intCountProperties = 0
                SELECT  '现在没有阻塞和死锁信息' AS message
    		-- 循环开始
            WHILE @intCounter <= @intCountProperties
                BEGIN
    				-- 取第一条记录
                    SELECT  @spid = spid, @bl = bl,    @sql_handle = sql_handle
                    FROM    @tmp_lock_who
                    WHERE   id = @intCounter
                    BEGIN
                        IF @bl = 0
                        BEGIN
                            SELECT '阻塞根源' + CAST(@spid AS VARCHAR(10)) AS [description], text AS [sql_text] FROM sys.dm_exec_sql_text(@sql_handle) AS dest
                        END
                        ELSE
                        BEGIN
                            SELECT CAST(@spid AS VARCHAR(10)) + '被' + CAST(@bl AS VARCHAR(10)) + '阻塞' AS [description], text AS [sql_text] FROM sys.dm_exec_sql_text(@sql_handle) AS dest
                        END
                        DBCC INPUTBUFFER(@spid)
                    END
    				-- 循环指针下移
                    SET @intCounter = @intCounter + 1
                END
    
            RETURN 0
        END
    GO

可以通过执行存储锁秤sp\_who\_lock查看当前死锁进程

exec sp_who_lock

# 4、也可以穿件杀掉引起死锁进程的存储过程 #

代码如下

create proc dbo.p_killspid 
    	@dbname varchar(200)    --要关闭进程的数据库名 
    as  
        declare @sql  nvarchar(500)  
        declare @spid nvarchar(20)
        declare #tb cursor for 
            select spid=cast(spid as varchar(20)) from master.dbo.sysprocesses where dbid=db_id(@dbname) 
        open #tb 
        fetch next from #tb into @spid 
        while @@fetch_status=0 
        begin  
            exec('kill '+@spid) 
            fetch next from #tb into @spid 
        end  
        close #tb 
        deallocate #tb
    go

调用存储过程exec dbo.p\_killspid spid杀死进程。

[20200302113851337.png]: https://img-blog.csdnimg.cn/20200302113851337.png