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

た入场券 2022-01-06 06:09 270阅读 0赞

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

创建sp\_who\_lock存储过程

<table> 
 <tbody> 
  <tr> 
   <td> 
    <div> 
     <div> 
      <code>CREATE</code> 
      <code>procedure</code> 
      <code>sp_who_lock&nbsp;&nbsp; </code> 
     </div> 
     <div> 
      <code>as</code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 
     </div> 
     <div> 
      <code>begin</code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;&nbsp;</code> 
      <code>declare</code> 
      <code>@spid </code> 
      <code>int</code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;&nbsp;</code> 
      <code>declare</code> 
      <code>@blk </code> 
      <code>int</code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;&nbsp;</code> 
      <code>declare</code> 
      <code>@</code> 
      <code>count</code> 
      <code>int</code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;&nbsp;</code> 
      <code>declare</code> 
      <code>@</code> 
      <code>index</code> 
      <code>int</code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;&nbsp;</code> 
      <code>declare</code> 
      <code>@lock tinyint&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;&nbsp;</code> 
      <code>set</code> 
      <code>@lock=0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;&nbsp;</code> 
      <code>create</code> 
      <code>table</code> 
      <code>#temp_who_lock&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </code> 
     </div> 
     <div> 
      <code>&nbsp;</code> 
      <code>(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;</code> 
      <code>id </code> 
      <code>int</code> 
      <code>identity(1,1),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;</code> 
      <code>spid </code> 
      <code>int</code> 
      <code>,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;</code> 
      <code>blk </code> 
      <code>int</code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 
     </div> 
     <div> 
      <code>&nbsp;</code> 
      <code>)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </code> 
     </div> 
     <div> 
      <code>&nbsp;</code> 
      <code>if @@error&lt;&gt;0 </code> 
      <code>return</code> 
      <code>@@error&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </code> 
     </div> 
     <div> 
      <code>&nbsp;</code> 
      <code>insert</code> 
      <code>into</code> 
      <code>#temp_who_lock(spid,blk)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </code> 
     </div> 
     <div> 
      <code>&nbsp;</code> 
      <code>select</code> 
      <code>0 ,blocked&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </code> 
     </div> 
     <div> 
      <code>&nbsp;</code> 
      <code>from</code> 
      <code>(</code> 
      <code>select</code> 
      <code>* </code> 
      <code>from</code> 
      <code>master..sysprocesses </code> 
      <code>where</code> 
      <code>blocked&gt;0)a&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </code> 
     </div> 
     <div> 
      <code>&nbsp;</code> 
      <code>where</code> 
      <code>not</code> 
      <code>exists(</code> 
      <code>select</code> 
      <code>* </code> 
      <code>from</code>&nbsp; 
      <code>master..sysprocesses </code> 
      <code>where</code> 
      <code>a.blocked =spid </code> 
      <code>and</code> 
      <code>blocked&gt;0)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </code> 
     </div> 
     <div> 
      <code>&nbsp;</code> 
      <code>union</code> 
      <code>select</code> 
      <code>spid,blocked </code> 
      <code>from</code>&nbsp; 
      <code>master..sysprocesses </code> 
      <code>where</code> 
      <code>blocked&gt;0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </code> 
     </div> 
     <div> 
      <code>&nbsp;</code> 
      <code>if @@error&lt;&gt;0 </code> 
      <code>return</code> 
      <code>@@error&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </code> 
     </div> 
     <div> 
      <code>&nbsp;</code> 
      <code>select</code> 
      <code>@</code> 
      <code>count</code> 
      <code>=</code> 
      <code>count</code> 
      <code>(*),@</code> 
      <code>index</code> 
      <code>=1 </code> 
      <code>from</code> 
      <code>#temp_who_lock&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </code> 
     </div> 
     <div> 
      <code>&nbsp;</code> 
      <code>if @@error&lt;&gt;0 </code> 
      <code>return</code> 
      <code>@@error&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </code> 
     </div> 
     <div> 
      <code>&nbsp;</code> 
      <code>if @</code> 
      <code>count</code> 
      <code>=0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </code> 
     </div> 
     <div> 
      <code>&nbsp;</code> 
      <code>begin</code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;</code> 
      <code>select</code> 
      <code>'没有阻塞和死锁信息'</code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;</code> 
      <code>return</code> 
      <code>0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </code> 
     </div> 
     <div> 
      <code>&nbsp;</code> 
      <code>end</code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 
     </div> 
     <div> 
      <code>&nbsp;</code> 
      <code>while @</code> 
      <code>index</code> 
      <code>&lt;=@</code> 
      <code>count</code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 
     </div> 
     <div> 
      <code>&nbsp;</code> 
      <code>begin</code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;</code> 
      <code>if exists(</code> 
      <code>select</code> 
      <code>1 </code> 
      <code>from</code> 
      <code>#temp_who_lock a </code> 
      <code>where</code> 
      <code>id&gt;@</code> 
      <code>index</code> 
      <code>and</code> 
      <code>exists(</code> 
      <code>select</code> 
      <code>1 </code> 
      <code>from</code> 
      <code>#temp_who_lock </code> 
      <code>where</code> 
      <code>id&lt;=@</code> 
      <code>index</code> 
      <code>and</code> 
      <code>a.blk=spid))&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;</code> 
      <code>begin</code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;&nbsp;</code> 
      <code>set</code> 
      <code>@lock=1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;&nbsp;</code> 
      <code>select</code> 
      <code>@spid=spid,@blk=blk </code> 
      <code>from</code> 
      <code>#temp_who_lock </code> 
      <code>where</code> 
      <code>id=@</code> 
      <code>index</code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;&nbsp;</code> 
      <code>select</code> 
      <code>'引起数据库死锁的是: '</code> 
      <code>+ </code> 
      <code>CAST</code> 
      <code>(@spid </code> 
      <code>AS</code> 
      <code>VARCHAR</code> 
      <code>(10)) + </code> 
      <code>'进程号,其执行的SQL语法如下'</code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;&nbsp;</code> 
      <code>select</code>&nbsp; 
      <code>@spid, @blk&nbsp;&nbsp;&nbsp; </code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;&nbsp;</code> 
      <code>dbcc inputbuffer(@spid)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;&nbsp;</code> 
      <code>dbcc inputbuffer(@blk)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;</code> 
      <code>end</code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;</code> 
      <code>set</code> 
      <code>@</code> 
      <code>index</code> 
      <code>=@</code> 
      <code>index</code> 
      <code>+1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </code> 
     </div> 
     <div> 
      <code>&nbsp;</code> 
      <code>end</code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 
     </div> 
     <div> 
      <code>&nbsp;</code> 
      <code>if @lock=0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </code> 
     </div> 
     <div> 
      <code>&nbsp;</code> 
      <code>begin</code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;</code> 
      <code>set</code> 
      <code>@</code> 
      <code>index</code> 
      <code>=1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;</code> 
      <code>while @</code> 
      <code>index</code> 
      <code>&lt;=@</code> 
      <code>count</code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;</code> 
      <code>begin</code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;&nbsp;</code> 
      <code>select</code> 
      <code>@spid=spid,@blk=blk </code> 
      <code>from</code> 
      <code>#temp_who_lock </code> 
      <code>where</code> 
      <code>id=@</code> 
      <code>index</code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;&nbsp;</code> 
      <code>if @spid=0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;&nbsp;&nbsp;</code> 
      <code>select</code> 
      <code>'引起阻塞的是:'</code> 
      <code>+</code> 
      <code>cast</code> 
      <code>(@blk </code> 
      <code>as</code> 
      <code>varchar</code> 
      <code>(10))+ </code> 
      <code>'进程号,其执行的SQL语法如下'</code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;&nbsp;</code> 
      <code>else</code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;&nbsp;&nbsp;</code> 
      <code>select</code> 
      <code>'进程号SPID：'</code> 
      <code>+ </code> 
      <code>CAST</code> 
      <code>(@spid </code> 
      <code>AS</code> 
      <code>VARCHAR</code> 
      <code>(10))+ </code> 
      <code>'被'</code> 
      <code>+ </code> 
      <code>'进程号SPID：'</code> 
      <code>+ </code> 
      <code>CAST</code> 
      <code>(@blk </code> 
      <code>AS</code> 
      <code>VARCHAR</code> 
      <code>(10)) +</code> 
      <code>'阻塞,其当前进程执行的SQL语法如下'</code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;&nbsp;</code> 
      <code>dbcc inputbuffer(@spid)&nbsp;&nbsp;&nbsp; </code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;&nbsp;</code> 
      <code>dbcc inputbuffer(@blk)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;&nbsp;</code> 
      <code>set</code> 
      <code>@</code> 
      <code>index</code> 
      <code>=@</code> 
      <code>index</code> 
      <code>+1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;</code> 
      <code>end</code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 
     </div> 
     <div> 
      <code>&nbsp;</code> 
      <code>end</code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 
     </div> 
     <div> 
      <code>&nbsp;</code> 
      <code>drop</code> 
      <code>table</code> 
      <code>#temp_who_lock&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </code> 
     </div> 
     <div> 
      <code>&nbsp;</code> 
      <code>return</code> 
      <code>0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </code> 
     </div> 
     <div> 
      <code>end</code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 
     </div> 
     <div>
       &nbsp; 
     </div> 
     <div>
       &nbsp; 
     </div> 
     <div> 
      <code>GO</code> 
     </div> 
    </div> </td> 
  </tr> 
 </tbody> 
</table>

在查询分析器中执行:

exec sp\_who\_lock

![2011060417321938.jpg][]

直到最后的结果为：

![2011060417325359.jpg][]

REFERENCE FROM : [http://www.cnblogs.com/mzhanker/archive/2011/06/04/2072739.html][http_www.cnblogs.com_mzhanker_archive_2011_06_04_2072739.html]

转载于:https://www.cnblogs.com/zhangchenliang/p/4425197.html

[2011060417321938.jpg]: /images/20211227/6ad04d519be043668f556b50587e10f3.png
[2011060417325359.jpg]: /images/20211227/53c499abbed74d5da44c72dbb10f9589.png
[http_www.cnblogs.com_mzhanker_archive_2011_06_04_2072739.html]: http://www.cnblogs.com/mzhanker/archive/2011/06/04/2072739.html