SQL Server 查看死锁的存储过程

喜欢ヅ旅行 2022-06-17 23:23 167阅读 0赞

if   exists  ( select   \*   from  dbo.sysobjects  where  id  =   object\_id (N ' \[dbo\].\[sp\_who\_lock\] ' )  and   OBJECTPROPERTY (id, N ' IsProcedure ' )  =   **1** )  
![None.gif][] drop   procedure   \[ dbo \] . \[ sp\_who\_lock \]   
![None.gif][] GO   
![None.gif][]  
![None.gif][] use  master  
![None.gif][] go   
![None.gif][] create   procedure  sp\_who\_lock  
![None.gif][] as   
![None.gif][] begin   
![None.gif][] declare   @spid   **int** , @bl   **int** ,  
![None.gif][]         @intTransactionCountOnEntry   **int** ,  
![None.gif][]         @intRowcount   **int** ,  
![None.gif][]         @intCountProperties   **int** ,  
![None.gif][]         @intCounter   **int**   
![None.gif][]  
![None.gif][]         create   table  \#tmp\_lock\_who (  
![None.gif][]        id  **int**   identity ( **1** , **1** ),  
![None.gif][]        spid  **smallint** ,  
![None.gif][]        bl  **smallint** )  
![None.gif][]  
![None.gif][]         IF   **@@ERROR** <> **0**   RETURN   **@@ERROR**   
![None.gif][]  
![None.gif][]         insert   into  \#tmp\_lock\_who(spid,bl)  select   **0**  ,blocked  
![None.gif][]           from  ( select   \*   from  sysprocesses  where  blocked > **0**  ) a   
![None.gif][]           where   not   exists ( select   \*   from  ( select   \*   from  sysprocesses  where  blocked > **0**  ) b   
![None.gif][]           where  a.blocked = spid)  
![None.gif][]           union   select  spid,blocked  from  sysprocesses  where  blocked > **0**   
![None.gif][]  
![None.gif][]         IF   **@@ERROR** <> **0**   RETURN   **@@ERROR**    
![None.gif][]  
![None.gif][] \--  找到临时表的记录数   
![None.gif][]          select   @intCountProperties   =   Count ( \* ), @intCounter   =   **1**   
![None.gif][]         from  \#tmp\_lock\_who  
![None.gif][]  
![None.gif][]         IF   **@@ERROR** <> **0**   RETURN   **@@ERROR**    
![None.gif][]  
![None.gif][]         if   @intCountProperties = **0**   
![None.gif][]             select   ' 现在没有阻塞和死锁信息 '   as  message  
![None.gif][]  
![None.gif][] \--  循环开始   
![None.gif][] while   @intCounter   <=   @intCountProperties   
![None.gif][] begin   
![None.gif][] \--  取第一条记录   
![None.gif][]          select   @spid   =  spid, @bl   =  bl  
![None.gif][]         from  \#tmp\_lock\_who  where  Id  =   @intCounter    
![None.gif][]         begin   
![None.gif][]             if   @spid   = **0**    
![None.gif][]                 select   ' 引起数据库死锁的是:  ' \+   CAST ( @bl   AS   **VARCHAR** ( **10** ))  \+   ' 进程号,其执行的SQL语法如下 '   
![None.gif][]             else   
![None.gif][]                 select   ' 进程号SPID： ' \+   CAST ( @spid   AS   **VARCHAR** ( **10** )) \+   ' 被 '   \+   ' 进程号SPID： ' \+   CAST ( @bl   AS   **VARCHAR** ( **10** ))  \+ ' 阻塞,其当前进程执行的SQL语法如下 '   
![None.gif][]             DBCC  INPUTBUFFER ( @bl  )  
![None.gif][]             end    
![None.gif][]  
![None.gif][] \--  循环指针下移   
![None.gif][]          set   @intCounter   =   @intCounter   \+   **1**   
![None.gif][] end   
![None.gif][]  
![None.gif][]  
![None.gif][] drop   table  \#tmp\_lock\_who  
![None.gif][]  
![None.gif][] return   **0**   
![None.gif][] end

[None.gif]: /images/20220618/9e590c4b908f452ba0d993c09f391701.png