查询Sqlserver数据库死锁的一个存储过程

CREATE? procedure? sp_who_lock?? as????? begin????? ???declare? @spid int????? @blk int????? @count? int????? @index? int????? @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? (* master..sysprocesses where? blocked>0)a????? where? not? exists(from?? a.blocked =spid and? blocked>0)????? union? spid,blocked blocked>0????? @@error????? count=(*),@index=1 #temp_who_lock????? @@error????? if @=0????? begin????? ??select? '没有阻塞和死锁信息'????? 0????? end????? while @<=@count?????? begin????? if exists(1 #temp_who_lock a id>@index? #temp_who_lock id<=@a.blk=spid))????? begin????? @lock=1????? @spid=spid,@blk=blk id=@index????? '引起数据库死锁的是: '+ CAST(@spid AS? VARCHAR(10)) + '进程号,其执行的SQL语法如下'????? select?? @spid,@blk??? ???dbcc inputbuffer(@spid)????? dbcc inputbuffer(@blk)????? end????? =@+1????? end????? if @lock=0?????? begin????? =1????? count?????? begin????? index????? if @spid=0????? ????'引起阻塞的是:'+cast(@blk as? varchar(10))+ '被'? + (10)) +'阻塞,其当前进程执行的SQL语法如下'????? dbcc inputbuffer(@spid)??? dbcc inputbuffer(@blk)????? +1????? end????? end????? drop? #temp_who_lock????? 0????? end??????????? ? ? GO

在查询分析器中执行:

exec sp_who_lock

?

直到最后的结果为：

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!

?使用sqlserver作为数据库的应用系统,都避免不了有时候会产生死锁， 死锁出现以后，维护人员或者开发人员大多只会通过sp_who来查找死锁的进程，然后用sp_kill杀掉。利用sp_who_lock这个存储过程，可以很方便的知道哪个进程出现了死锁，出现死锁的问题在哪里. 创建sp_who_lock存储过程