处理死锁

/*--处理死锁

    查看当前进程,或死锁进程,并能自动杀掉死进程

    因为是针对死的,所以如果有死锁进程,只能查看死锁进程
    当然,你可以通过参数控制,不管有没有死锁,都只查看死锁进程

    感谢: caiyunxia,jiangopen 两位提供的参考信息

--邹建 2004.4--*/

/*--调用示例

    exec p_lockinfo
--*/
create   proc  p_lockinfo
@kill_lock_spid  bit = 1 ,         -- 是否杀掉死锁的进程,1 杀掉, 0 仅显示
@show_spid_if_nolock  bit = 1      -- 如果没有死锁的进程,是否显示正常进程信息,1 显示,0 不显示
as
set  nocount  on
declare  @count  int ,@s  nvarchar ( 1000 ),@i  int
select  id = identity ( int , 1 , 1 ),标志,
    进程ID = spid,线程ID = kpid,块进程ID = blocked,数据库ID = dbid,
    数据库名 = db_name (dbid),用户ID = uid,用户名 = loginame,累计CPU时间 = cpu,
    登陆时间 = login_time,打开事务数 = open_tran,    进程状态 = status,
    工作站名 = hostname,应用程序名 = program_name,工作站进程ID = hostprocess,
    域名 = nt_domain,网卡地址 = net_address
into  #t  from (
     select  标志 = ' 死锁的进程 ' ,
        spid,kpid,a.blocked,dbid,uid,loginame,cpu,login_time,open_tran,
        status,hostname,program_name,hostprocess,nt_domain,net_address,
        s1 = a.spid,s2 = 0
     from  master..sysprocesses a  join  (
         select  blocked  from  master..sysprocesses  group   by  blocked
        )b  on  a.spid = b.blocked  where  a.blocked = 0
     union   all
     select   ' |_牺牲品_> ' ,
        spid,kpid,blocked,dbid,uid,loginame,cpu,login_time,open_tran,
        status,hostname,program_name,hostprocess,nt_domain,net_address,
        s1 = blocked,s2 = 1
     from  master..sysprocesses a  where  blocked <> 0
)a  order   by  s1,s2

select  @count = @@rowcount,@i = 1

if  @count = 0   and  @show_spid_if_nolock = 1
begin
     insert  #t
     select  标志 = ' 正常的进程 ' ,
        spid,kpid,blocked,dbid, db_name (dbid),uid,loginame,cpu,login_time,
        open_tran,status,hostname,program_name,hostprocess,nt_domain,net_address
     from  master..sysprocesses
     set  @count = @@rowcount
end

if  @count > 0
begin
     create   table  #t1(id  int   identity ( 1 , 1 ),a  nvarchar ( 30 ),b  Int ,EventInfo  nvarchar ( 255 ))
     if  @kill_lock_spid = 1
     begin
         declare  @spid  varchar ( 10 ),@标志  varchar ( 10 )
         while  @i <= @count
         begin
             select  @spid = 进程ID,@标志 = 标志  from  #t  where  id = @i
             insert  #t1  exec ( ' dbcc inputbuffer( ' + @spid + ' ) ' )
             if  @@rowcount = 0   insert  #t1(a)  values ( null )
             if  @标志 = ' 死锁的进程 '   exec ( ' kill  ' + @spid)
             set  @i = @i + 1
         end
     end
     else
         while  @i <= @count
         begin
             select  @s = ' dbcc inputbuffer( ' + cast (进程ID  as   varchar ) + ' ) '   from  #t  where  id = @i
             insert  #t1  exec (@s)
             if  @@rowcount = 0   insert  #t1(a)  values ( null )
             set  @i = @i + 1
         end
     select  a. * ,进程的SQL语句 = b.EventInfo
     from  #t a  join  #t1 b  on  a.id = b.id
     order   by  进程ID
end
set  nocount  off

GO

if   exists  ( select   *   from  dbo.sysobjects  where  id  =   object_id (N ' [dbo].[sp_who_lock] ' )  and   OBJECTPROPERTY (id, N ' IsProcedure ' )  =   1 )
drop   procedure   [ dbo ] . [ sp_who_lock ]
GO
/***************************************************************************
//  创建 : fengyu  邮件 : maggiefengyu@tom.com  日期 :2004-04-30
//  修改 : 从http://www.youkuaiyun.com/develop/Read_Article.asp?id=26566学习到并改写  
//  说明 : 查看数据库里阻塞和死锁情况
***************************************************************************/
use  master
go
create   procedure  sp_who_lock
as
begin
declare  @spid  int ,@bl  int ,
    @intTransactionCountOnEntry      int ,
        @intRowcount              int ,
        @intCountProperties          int ,
        @intCounter              int

     create   table  #tmp_lock_who (
    id  int   identity ( 1 , 1 ),
    spid  smallint ,
    bl  smallint )
    
     IF  @@ERROR <> 0   RETURN  @@ERROR
    
     insert   into  #tmp_lock_who(spid,bl)  select    0  ,blocked
       from  ( select   *   from  sysprocesses  where   blocked > 0  ) a 
       where   not   exists ( select   *   from  ( select   *   from  sysprocesses  where   blocked > 0  ) b 
       where  a.blocked = spid)
       union   select  spid,blocked  from  sysprocesses  where   blocked > 0

     IF  @@ERROR <> 0   RETURN  @@ERROR    
     
--  找到临时表的记录数
     select      @intCountProperties  =   Count ( * ),@intCounter  =   1
     from  #tmp_lock_who
    
     IF  @@ERROR <> 0   RETURN  @@ERROR    
    
     if     @intCountProperties = 0
         select   ' 现在没有阻塞和死锁信息 '   as  message

--  循环开始
while  @intCounter  <=  @intCountProperties
begin
--  取第一条记录
         select      @spid  =  spid,@bl  =  bl
         from  #tmp_lock_who  where  Id  =  @intCounter 
     begin
      if  @spid  = 0  
             select   ' 引起数据库死锁的是:  ' +   CAST (@bl  AS   VARCHAR ( 10 ))  +   ' 进程号,其执行的SQL语法如下 '
  else
             select   ' 进程号SPID： ' +   CAST (@spid  AS   VARCHAR ( 10 )) +   ' 被 '   +   ' 进程号SPID： ' +   CAST (@bl  AS   VARCHAR ( 10 ))  + ' 阻塞,其当前进程执行的SQL语法如下 '
  DBCC  INPUTBUFFER (@bl )
     end     

--  循环指针下移
     set  @intCounter  =  @intCounter  +   1
end


drop   table  #tmp_lock_who

return   0
end