检测发生死锁的sql语句

梦里梦外; 2022-08-14 03:41 153阅读 0赞

[http://www.yxl.cn/Info/20100630,102050,7642.html][http_www.yxl.cn_Info_20100630_102050_7642.html]

sql server

\--检测死锁  
  
  
\--如果发生死锁了，我们怎么去检测具体发生死锁的是哪条SQL语句或存储过程？  
  
  
\--这时我们可以使用以下存储过程来检测，就可以查出引起死锁的进程和SQL语句。SQL Server自带的系统存储过程sp\_who和sp\_lock也可以用来查找阻塞和死锁, 但没有这里介绍的方法好用。  
  
  
  
  
   
  
  
use master  
go  
create procedure sp\_who\_lock  
as  
begin  
declare @spid int,@bl int,  
 @intTransactionCountOnEntry  int,  
        @intRowcount    int,  
        @intCountProperties   int,  
        @intCounter    int  
  
  
 create table \#tmp\_lock\_who (  
 id int identity(1,1),  
 spid smallint,  
 bl smallint)  
   
 IF @@ERROR<>0 RETURN @@ERROR  
   
 insert into \#tmp\_lock\_who(spid,bl) select  0 ,blocked  
   from (select \* from sysprocesses where  blocked>0 ) a   
   where not exists(select \* from (select \* from sysprocesses where  blocked>0 ) b   
   where a.blocked=spid)  
   union select spid,blocked from sysprocesses where  blocked>0  
  
  
 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  
  from \#tmp\_lock\_who where Id = @intCounter   
 begin  
  if @spid =0   
            select '引起数据库死锁的是: '+ CAST(@bl AS VARCHAR(10)) + '进程号,其执行的SQL语法如下'  
 else  
            select '进程号SPID：'+ CAST(@spid AS VARCHAR(10))+ '被' + '进程号SPID：'+ CAST(@bl AS VARCHAR(10)) +'阻塞,其当前进程执行的SQL语法如下'  
 DBCC INPUTBUFFER (@bl )  
 end   
  
  
\-- 循环指针下移  
 set @intCounter = @intCounter + 1  
end  
  
  
drop table \#tmp\_lock\_who  
  
  
return 0  
end  
  
  
  
  
   
  
  
\--杀死锁和进程  
  
  
\--如何去手动的杀死进程和锁？最简单的办法，重新启动服务。但是这里要介绍一个存储过程，通过显式的调用，可以杀死进程和锁。  
  
  
use master  
go  
  
  
if exists (select \* from dbo.sysobjects where id = object\_id(N'\[dbo\].\[p\_killspid\]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)  
drop procedure \[dbo\].\[p\_killspid\]  
GO  
  
  
create proc 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..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 p\_killspid  'newdbpy'  
  
  
  
  
   
  
  
\--查看锁信息  
  
  
\--如何查看系统中所有锁的详细信息？在企业管理管理器中，我们可以看到一些进程和锁的信息，这里介绍另外一种方法。  
  
  
\--查看锁信息  
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 进程id=a.req\_spid  
    ,数据库=db\_name(rsc\_dbid)  
    ,类型=case rsc\_type when 1 then 'NULL 资源（未使用）'  
        when 2 then '数据库'  
        when 3 then '文件'  
        when 4 then '索引'  
        when 5 then '表'  
        when 6 then '页'  
        when 7 then '键'  
        when 8 then '扩展盘区'  
        when 9 then 'RID（行 ID)'  
        when 10 then '应用程序'  
    end  
    ,对象id=rsc\_objid  
    ,对象名=b.obj\_name  
    ,rsc\_indid  
 from master..syslockinfo a left join \#t b on a.req\_spid=b.req\_spid  
  
  
go  
drop table \#t  
  
  
这是论坛上的一个牛人的，忘了是谁的了，你可以研究一下，很不错

[http_www.yxl.cn_Info_20100630_102050_7642.html]: http://www.yxl.cn/Info/20100630,102050,7642.html