查询Sqlserver数据库死锁的一个存储过程分享
使用sqlserver作为数据库的应用系统,都避免不了有时候会产生死锁，死锁出现以后，维护人员或者开发人员大多只会通过sp_who来查找死锁的进程，然后用sp_kill杀掉。利用sp_who_lock这个存储过程，可以很方便的知道哪个进程出现了死锁，出现死锁的问题在哪里.
创建sp_who_lock存储过程
CREATEproceduresp_who_lock as begin declare@spidint declare@blkint declare@countint declare@indexint declare@locktinyint set@lock=0 createtable#temp_who_lock ( idintidentity(1,1), spidint, blkint ) if@@error<>0return@@error insertinto#temp_who_lock(spid,blk) select0,blocked from(select*frommaster..sysprocesseswhereblocked>0)a wherenotexists(select*frommaster..sysprocesseswherea.blocked=spidandblocked>0) unionselectspid,blockedfrommaster..sysprocesseswhereblocked>0 if@@error<>0return@@error select@count=count(*),@index=1from#temp_who_lock if@@error<>0return@@error if@count=0 begin select'没有阻塞和死锁信息' return0 end while@index<=@count begin ifexists(select1from#temp_who_lockawhereid>@indexandexists(select1from#temp_who_lockwhereid<=@indexanda.blk=spid)) begin set@lock=1 select@spid=spid,@blk=blkfrom#temp_who_lockwhereid=@index select'引起数据库死锁的是:'+CAST(@spidASVARCHAR(10))+'进程号,其执行的SQL语法如下' select@spid,@blk dbccinputbuffer(@spid) dbccinputbuffer(@blk) end set@index=@index+1 end if@lock=0 begin set@index=1 while@index<=@count begin select@spid=spid,@blk=blkfrom#temp_who_lockwhereid=@index if@spid=0 select'引起阻塞的是:'+cast(@blkasvarchar(10))+'进程号,其执行的SQL语法如下' else select'进程号SPID：'+CAST(@spidASVARCHAR(10))+'被'+'进程号SPID：'+CAST(@blkASVARCHAR(10))+'阻塞,其当前进程执行的SQL语法如下' dbccinputbuffer(@spid) dbccinputbuffer(@blk) set@index=@index+1 end end droptable#temp_who_lock return0 end GO
在查询分析器中执行:
execsp_who_lock
直到最后的结果为：**