查询Sqlserver数据库死锁的一个存储过程
1 use master 2 go 3 4 CREATE procedure sp_who_lock 5 as 6 begin 7 declare @spid int 8 declare @blk int 9 declare @count int 10 declare @index int 11 declare @lock tinyint 12 set @lock=0 13 create table #temp_who_lock 14 ( 15 id int identity(1,1), 16 spid int, 17 blk int 18 ) 19 20 if @@error<>0 return @@error 21 insert into #temp_who_lock(spid,blk) 22 select 0 ,blocked 23 from (select * from master..sysprocesses where blocked>0)a 24 where not exists(select * from master..sysprocesses where a.blocked =spid and blocked>0) 25 union 26 select spid,blocked from master..sysprocesses where blocked>0 27 28 if @@error<>0 return @@error 29 select @count=count(*),@index=1 from #temp_who_lock 30 if @@error<>0 return @@error 31 if @count=0 32 begin 33 select '没有阻塞和死锁信息' 34 return 0 35 end 36 while @index<=@count 37 begin 38 if exists(select 1 from #temp_who_lock a where id>@index and exists(select 1 from #temp_who_lock 39 where id<=@index and a.blk=spid)) 40 begin 41 set @lock=1 42 select @spid=spid,@blk=blk from #temp_who_lock where id=@index 43 select '引起数据库死锁的是: '+ CAST(@spid AS VARCHAR(10)) + '进程号,其执行的SQL语法如下' 44 select @spid, @blk 45 dbcc inputbuffer(@spid) 46 dbcc inputbuffer(@blk) 47 end 48 set @index=@index+1 49 end 50 if @lock=0 51 begin 52 set @index=1 53 while @index<=@count 54 begin 55 select @spid=spid,@blk=blk from #temp_who_lock where id=@index 56 if @spid=0 57 select '引起阻塞的是:'+cast(@blk as varchar(10))+ '进程号,其执行的SQL语法如下' 58 else 59 select '进程号SPID：'+ CAST(@spid AS VARCHAR(10))+ '被' + '进程号SPID：'+ CAST(@blk AS VARCHAR(10)) 60 +'阻塞,其当前进程执行的SQL语法如下' 61 dbcc inputbuffer(@spid) 62 dbcc inputbuffer(@blk) 63 set @index=@index+1 64 end 65 end 66 drop table #temp_who_lock 67 return 0 68 end 69 70 71 GO
在查询分析器中执行:
exec sp_who_lock
直到最后的结果为：
