MSSQL_死锁处理脚本

死锁处理脚本

--建立日志表
CREATE TABLE dbo.sp_who_lock_unlock_log  
( id INT IDENTITY(1,1),
  EventType	nvarchar(30) NULL,
  parameterss	smallint,
  EventInfo	nvarchar(4000)
)ON [PRIMARY]




/****** Object:  StoredProcedure [dbo].[sp_who_lock_unlock]    Script Date: 2022-08-30 10:53:34 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO




CREATE PROCEDURE [dbo].[sp_who_lock_unlock]   
AS 
BEGIN     
   declare @spid int     
   declare @blk int     
   declare @count int     
   declare @index int  
   declare @index_unlock int  
   DECLARE @exec_kill VARCHAR(1000)   
   SET @index_unlock =0
   declare @lock tinyint      
   set @lock=0      
   declare @Sql_Sql nvarchar(100) 
   SET @Sql_Sql = 'DBCC INPUTBUFFER(@spid)'
   CREATE TABLE #temp_who_lock      
 (      
  id INT IDENTITY(1,1),      
  spid INT,      
  blk INT     
 )      
 IF @@error<>0 RETURN @@error      
 INSERT INTO #temp_who_lock(spid,blk)      
 SELECT 0 ,blocked       
 FROM (SELECT * FROM master..sysprocesses WHERE blocked>0)a      
 WHERE NOT EXISTS(SELECT * FROM  master..sysprocesses WHERE a.blocked =spid AND blocked>0)      
 UNION SELECT spid,blocked FROM  master..sysprocesses WHERE blocked>0      
 IF @@error<>0 RETURN @@error      
 SELECT @count=COUNT(*),@index=1 FROM #temp_who_lock      
 IF @@error<>0 RETURN @@error      
 IF @count=0      
 BEGIN     
  SELECT '没有阻塞和死锁信息'     
  RETURN 0      
 END     
 WHILE @index<=@count      
 BEGIN     
  IF EXISTS(SELECT 1 FROM #temp_who_lock a WHERE id>@index AND EXISTS(SELECT 1 FROM #temp_who_lock WHERE id<=@index AND a.blk=spid))      
  BEGIN     
   SET @lock=1      
   SELECT @spid=spid,@blk=blk FROM #temp_who_lock WHERE id=@index     
   SELECT '引起数据库死锁的是: '+ CAST(@spid AS VARCHAR(10)) + '进程号,其执行的SQL语法如下'  
   IF(@index_unlock = 0)   
   BEGIN
   SET @index_unlock = 1
   INSERT INTO sp_who_lock_unlock_log(EventType,parameterss,EventInfo)
   EXEC sp_executesql @Sql_Sql,N'@spid int output',@spid OUTPUT 
   SET @exec_kill = 'kill ' +CAST(@spid AS VARCHAR(10))
   EXECUTE (@exec_kill);
   END   
   SELECT  @spid, @blk    
   DBCC INPUTBUFFER(@spid)      
   DBCC INPUTBUFFER(@blk)      
  END     
  SET @index=@index+1      
 END     
 IF @lock=0       
 BEGIN     
  SET @index=1      
  WHILE @index<=@count      
  BEGIN     
   SELECT @spid=spid,@blk=blk FROM #temp_who_lock WHERE id=@index     
   IF @spid=0      
    SELECT '引起阻塞的是:'+CAST(@blk AS VARCHAR(10))+ '进程号,其执行的SQL语法如下'     
   ELSE      
    SELECT '进程号SPID：'+ CAST(@spid AS VARCHAR(10))+ '被' + '进程号SPID：'+ CAST(@blk AS VARCHAR(10)) +'阻塞,其当前进程执行的SQL语法如下'     
   DBCC INPUTBUFFER(@spid)    
   DBCC INPUTBUFFER(@blk)      
   SET @index=@index+1      
  END     
 END     
 DROP TABLE #temp_who_lock      
 RETURN 0      
END