存储计划：自动kill掉死锁进程id

最新推荐文章于 2024-06-04 10:16:20 发布

tiegenZ

最新推荐文章于 2024-06-04 10:16:20 发布

阅读量437

点赞数

分类专栏： SQLServer 文章标签：死锁查看死锁原因处理死锁 sqlserver 死锁

本文链接：https://blog.csdn.net/tiegenZ/article/details/124411866

版权

SQLServer 专栏收录该内容

12 篇文章

订阅专栏

CREATE PROCEDURE [dbo].[sp_who_lock1116] AS
--exec  sp_who_lock1116
begin
declare @spid int,@bl int,
@intTransactionCountOnEntry int,
@intRowcount int,
@intCountProperties int,
@intCounter int

declare @sql varchar(1000)

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
		 begin
			select '引起数据库死锁的是：'+cast(@bl as varchar(10))+'进程号，其执行的SQL语法如下'
		 end
         else
		  begin
         select '进程号spid：'+cast(@spid as varchar(10))+'被'+'进程号spid'+cast(@bl as varchar(10))+'阻塞，其执行的SQL语法如下'
		 end
    DBCC inputbuffer(@bl)
	set @sql='kill '+cast(@bl as varchar(10))
	exec(@sql)
    end
    set @intCounter=@intCounter+1
 end
 drop table #tmp_lock_who
 return 0
end

经常性的死锁，如果无法彻底解决的话，可建个计划定时执行以上存储过程