MSSQL deadlock

第一步，先创建两个表，并插入数据

CREATE TABLE Lock1(C1 int default(0));
CREATE TABLE Lock2(C1 int default(0));
INSERT INTO Lock1 VALUES(1);
INSERT INTO Lock2 VALUES(1);
第二步：执行第一个sql

 Begin Tran
  Update Lock2 Set C1=C1+1;
    WaitFor Delay '00:00:10';
 
  SELECT * FROM Lock1
    WaitFor Delay '00:00:30';
 
Rollback Tran;
第三步：执行第二个sql

Begin Tran
  Update Lock1 Set C1=C1+1;
  WaitFor Delay '00:00:10';
  SELECT * FROM Lock2
Rollback Tran;
 
 
第四步：执行第三个sql

 select *  from  Lock2  
第五步：执行第四个sql(，这个存储过程的代码可以见：https://blog.csdn.net/weixin_46867655/article/details/106519030)

GO
 
DECLARE    @return_value int
 
EXEC    @return_value = [dbo].sp_who_lock
 
SELECT    'Return Value' = @return_value
 
GO
一直执行多次点击第五步，就可以发现首先出现的是阻塞的信息，然后就会出现死锁的信息；

-- select spid,ecid,status,loginame,hostname,blocked,db_name(dbid) as dbname,cmd,request_id from sys.sysprocesses where loginame not in ('sa','NT AUTHORITY\SYSTEM') and blocked !=0;

/****
SELECT wt.blocking_session_id                   AS BlockingSessesionId
      ,'KILL ' + CAST( wt.blocking_session_id   AS NVARCHAR(128)) +';'
                                                AS KillBlockingText
      ,sp.program_name                          AS Blocking_ProgramName
      ,COALESCE(sp.LOGINAME, sp.nt_username)    AS Blocking_HostName    
      ,ec1.session_id                           AS BlockedSessionId
      ,ec1.client_net_address                   AS ClientIpAddress
      ,db.name                                  AS DatabaseName     
      ,wt.wait_type                             AS WaitType                 
      ,ec1.connect_time                         AS BlockingStartTime
      ,wt.WAIT_DURATION_MS/1000                 AS WaitDuration
      ,sp.open_tran                             AS OpenTrans  --此事务是阻塞源头的SQL的事务
      ,sp.status                                AS SessionStatus
      ,h1.TEXT                                  AS BlockedSQLText
      ,h2.TEXT                                  AS BlockingSQLText

FROM sys.dm_tran_locks  AS tl WITH(NOLOCK)
INNER JOIN sys.databases AS db  WITH(NOLOCK)
        ON db.database_id = tl.resource_database_id
INNER JOIN sys.dm_os_waiting_tasks AS wt  WITH(NOLOCK)
        ON tl.lock_owner_address = wt.resource_address
INNER JOIN sys.dm_exec_connections  ec1 WITH(NOLOCK)
        ON ec1.session_id = tl.request_session_id
INNER JOIN sys.dm_exec_connections  ec2 WITH(NOLOCK)
        ON ec2.session_id = wt.blocking_session_id
LEFT OUTER JOIN sys.sysprocesses AS sp WITH(NOLOCK)
        ON SP.spid = wt.blocking_session_id
CROSS APPLY sys.dm_exec_sql_text(ec1.most_recent_sql_handle) AS h1 
CROSS APPLY sys.dm_exec_sql_text(ec2.most_recent_sql_handle) AS h2;
******/

记录两种排查死锁的方法：

方法一：这个存储过程可以很清楚的看到当前死锁跟阻塞的sql；并释放进程

1，创建表来存储释放掉的sql，方便排查问题

 
GO
 
/****** Object:  Table [dbo].[killSpId]    Script Date: 2020/6/16 14:12:40 ******/
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
CREATE TABLE [dbo].[killSpId](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [spId] [int] NULL,
    [createTime] [datetime] NULL,
    [type] [nvarchar](10) NULL,
    [sql] [nvarchar](4000) NULL,
 CONSTRAINT [PK_killSpId] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
 
 
2，创建存储过程。

 
GO
 
/****** Object:  StoredProcedure [dbo].[sp_whoIsLockAndKillSpId]    Script Date: 2020/6/16 14:10:13 ******/
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
 
 
 
CREATE procedure [dbo].[sp_whoIsLockAndKillSpId]  
 
as     
 
begin     
 
   declare @spid int     
 
   declare @blk int     
 
   declare @count int     
 
   declare @index int     
 
   declare @lock tinyint  
   declare @tmpSpId int
 
DECLARE @TSQL nvarchar(max)
DECLARE @IPB TABLE
(EVENTTYPE nvarchar(30),
PARAMETERS int,
EVENTINFO nvarchar(4000))
 
 
   set @lock=0      
 
   create table #temp_who_lock      
 
 (      
 
  id int identity(1,1),      
 
  spid int,      
 
  blk int     
 
 )  
 
 --insert into killSpId(sql,createTime)values('haha',GETDATE())
 
 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语法如下'     
   
   select  @spid, @blk    
 
  if(@spid <> 0)
    begin 
     dbcc inputbuffer(@spid)      
 
   dbcc inputbuffer(@blk)   
    end 
   INSERT INTO @IPB
   EXEC('DBCC INPUTBUFFER('+@spid+')')
   
   insert into killSpId(spId,createTime,type,sql)  
   SELECT @spid,GETDATE(),'lock',EVENTINFO from @IPB
  
   exec('kill '+ @spid) 
  
 
  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      
   begin
    select '引起阻塞的是:'+cast(@blk as varchar(10))+ '进程号,其执行的SQL语法如下'   
     
     INSERT INTO @IPB
     EXEC('DBCC INPUTBUFFER('+@spid+')')
   
     insert into killSpId(spId,createTime,type,sql)  
     SELECT @spid,GETDATE(),'blocked',EVENTINFO from @IPB
     end
   else 
   begin
 
    select '进程号SPID：'+ CAST(@spid AS VARCHAR(10))+ '被' + '进程号SPID：'+ CAST(@blk AS VARCHAR(10)) +'阻塞,其当前进程执行的SQL语法如下'
    -- insert into killSpId(spId,createTime,[type],sql) values(@spid,GETDATE(),'blocked','') 
     if(CAST(@spid AS VARCHAR(10))<>CAST(@blk AS VARCHAR(10)))
         begin
             exec('kill '+ @blk) 
             INSERT INTO @IPB
             EXEC('DBCC INPUTBUFFER('+@spid+')')
   
             insert into killSpId(spId,createTime,type,sql)  
             SELECT @spid,GETDATE(),'blocked',EVENTINFO from @IPB
         end
         
    
     end
  if(@spid <> 0)
    begin 
     dbcc inputbuffer(@spid)      
 
   dbcc inputbuffer(@blk)   
    end     
  -- insert into killSpId(spId,createTime,sql) values(@spid,GETDATE(),'blocked') 
  -- exec('kill '+ @spid) 
   set @index=@index+1      
   
  end     
 
 end     
 
 drop table #temp_who_lock      
 
 return 0      
 
end           
GO