sql死锁

use master
select  distinct 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 blocked from sysprocesses where  blocked>0

 

--------------------------------------------------------------------

use master
go
declare @spid int,@bl int
DECLARE s_cur CURSOR FOR
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
OPEN s_cur
FETCH NEXT FROM s_cur INTO @spid,@bl
WHILE @@FETCH_STATUS = 0
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 )
FETCH NEXT FROM s_cur INTO @spid,@bl
end
CLOSE s_cur
DEALLOCATE s_cur

--kill 58
--kill 62
-------------------------------------------------------

CREATE procedure sp_who_lock  

as    

begin    

   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<<A href="mailto:=@count">=@count     

 begin    

  if exists(select 1 from #temp_who_lock a where id>@index and exists(select 1 from #temp_who_lock where id<<A href="mailto:=@index">=@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<<A href="mailto:=@count">=@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语法如下'    

   dbcc inputbuffer(@spid)   

   dbcc inputbuffer(@blk)     

   set @index=@index+1     

  end    

 end    

 drop table #temp_who_lock     

 return 0     

end