查询死锁与自动删除死锁联接

最新推荐文章于 2022-04-25 19:06:25 发布

原创最新推荐文章于 2022-04-25 19:06:25 发布 · 212 阅读

0 ·

CC 4.0 BY-SA版权

SQL Server 专栏收录该内容

13 篇文章

订阅专栏

本文介绍了一个SQL Server的存储过程，用于检测并解决数据库中的死锁问题。该过程可以显示阻塞进程的详细信息，包括进程ID、阻塞类型、CPU使用情况等，并能自动或手动终止造成死锁的连接。

-- Exec [dbo].[HT_who_lockEx] 1 --- dbc
create procedure [dbo].[HT_who_lockEx]
@isKill as bit -- 是否自动删除死锁联接
as
begin

declare @spid int,@bl int,
   @intTransactionCountOnEntry int,
   @intRowcount int,
   @intCountProperties int,
   @intCounter int

       create table #tmp_lock_sql (
       EventType varchar(200),
       Parameters varchar(200),
       EventInfo varchar(5000))

   create table #tmp_lock_who (
       id int identity(1,1),
       spid smallint,
       bl smallint,
       last_batch datetime)

   IF @@ERROR<>0
       Select @@ERROR

   insert into #tmp_lock_who(spid,bl,last_batch)
       select 0 as spid,blocked as bl,last_batch from (select * from master.dbo.sysprocesses where blocked>0 ) a
           where not exists
               (select * from (select * from master.dbo.sysprocesses   where blocked>0 ) b
                   where a.blocked=spid)
           union select spid,blocked,last_batch from master.dbo.sysprocesses where blocked>0

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

   -- 循环开始
   Declare @LastDate as datetime
   while @intCounter <= @intCountProperties
   begin

-- 取第一条记录
Select * From [Master].[dbo].[SYSPROCESSES] Where spid = @bl

       select @spid = spid,@bl = bl,@LastDate= last_batch
           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 )

           truncate table #tmp_lock_sql

           Insert into #tmp_lock_sql(EventType,Parameters,EventInfo)
           Exec('DBCC INPUTBUFFER ('+@bl+')')
           Select '耗时:'+Cast(DATEDIFF(ss,@LastDate,GETDATE()) as varchar(20))+'秒' + case when DATEDIFF(ss,@LastDate,GETDATE())>20 And @spid=0 then ' 已执行KILL' else '' end as 提示, * From #tmp_lock_sql
           Declare @icSQLSTring as varchar(Max)
           IF @isKill=1 And DATEDIFF(ss,@LastDate,GETDATE())>20
           Begin
               set @icSQLSTring = 'Kill ' + cast(@bl as varchar(12))
               Exec (@icSQLSTring)

           End

       End

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

   SELECT spid as id
       ,lastwaittype as 等待类型
       ,cpu as CPU
       ,login_time as 登入时间
       ,last_batch as 最后处理
       ,status as 状态
       ,cmd as 命令类型
       ,loginame as 登录用户
       ,hostname as 登录终端
       ,program_name as 执行程序
       FROM
   [Master].[dbo].[SYSPROCESSES] WHERE 1=1 And not status IN ('sleeping','background')
   Order by 状态,CPU desc

   drop table #tmp_lock_who
   return 0
end