SqlServer死锁检测

本文介绍了一种SQL Server中查找并解决阻塞和死锁的方法,通过创建临时表来收集阻塞信息,并利用DBCC输入缓冲命令获取具体执行的SQL语句。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

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 
  
--select @count,@index 
  
--if @@error<>0 return @@error     
IF @count = 0 
  BEGIN 
      SELECT '没有阻塞和死锁信息'  
  --return 0     
  END 
  
WHILE @index <= @count 
  BEGIN 
      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 
  
            SELECT  '引起数据库死锁的是: ' + 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 
  
            IF @spid = 0 
              SELECT '引起阻塞的是:' + Cast(@blk AS VARCHAR(10)) + '进程号,其执行的SQL语法如下'  
            ELSE 
              SELECT '进程号SPID:' + Cast(@spid AS VARCHAR(10)) + '被' + '进程号SPID:' + Cast(@blk AS VARCHAR(10)) + '阻塞,其当前进程执行的SQL语法如下' 
  
            PRINT ( LTRIM(@spid) + ''+ LTRIM(@blk)); 
            if(@spid <> 0) 
            BEGIN 
               DBCC inputbuffer(@spid)   -- 
             END 
  
            DBCC inputbuffer(@blk)   --引起阻塞语句 
  
            SET @index=@index + 1 
        END 
  END 
  
DROP TABLE #temp_who_lock


 

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值