关于SQLServer死锁的诊断和定位

原文来自：http://www.itpub.net/thread-1007822-1-1.html

关于SQLServer死锁的诊断和定位

 

在SQLServer中经常会发生死锁情况，必须连接到企业管理器—>管理—>当前活动—>锁/进程 ID去查找相关死锁进程和定位死锁的原因。
通过查询分析器也要经过多个系统表(sysprocesses,sysobjects等)和系统存储过程(sp_who,sp_who2,sp_lock等)，而且不一定能够直接定位到。

本存储过程参考sp_lock_check和sysprocesses系统表，同时利用了DBCC命令，直接将死锁和造成死锁的进程和相关语句列出，以方便分析和定位。

Create procedure sp_check_deadlock

  as
  set nocount on
   
  /*
  select
  spid
被锁进程ID,
  blocked 锁进程ID,
  status
被锁状态,
  SUBSTRING(SUSER_SNAME(sid),1,30) 被锁进程登陆帐号,
  SUBSTRING(hostname,1,12)
被锁进程用户机器名称,
  SUBSTRING(DB_NAME(dbid),1,10)
被锁进程数据名称,
  cmd 被锁进程命令,
  waittype 被锁进程等待类型
  FROM master..sysprocesses
  WHERE blocked>0
   
  --dbcc inputbuffer(66) 输出相关锁进程的语句
  */
   
  --创建锁进程临时表
  CREATE TABLE #templocktracestatus (  
EventType
varchar(100),  
Parameters INT,  
EventInfo
varchar(200)  
)
  --创建被锁进程临时表
  CREATE TABLE #tempbelocktracestatus (  
EventType
varchar(100),  
Parameters INT,
EventInfo
varchar(200)  
)

--创建之间的关联表
CREATE TABLE #locktracestatus (  
belockspid INT,  
belockspidremark varchar(20),  
belockEventType
varchar(100),  
belockEventInfo
varchar(200),  
lockspid INT,  
lockspidremark
varchar(20),  
lockEventType
varchar(100),  
lockEventInfo
varchar(200)  
)
   
  --获取死锁进程
  DECLARE dbcc_inputbuffer CURSOR READ_ONLY
  FOR select spid 被锁进程ID,blocked 锁进程ID  
FROM master..sysprocesses   
WHERE blocked>0
   
  DECLARE @lockedspid int
  DECLARE @belockedspid int
   
  OPEN dbcc_inputbuffer
   
  FETCH NEXT FROM dbcc_inputbuffer INTO  @belockedspid,@lockedspid
  WHILE (@@fetch_status <> -1)
  BEGIN
  
IF (@@fetch_status <> -2)
  
BEGIN
  
--print '被堵塞进程'  
--select @belockedspid  
--dbcc inputbuffer(@belockedspid)  
--print '堵塞进程'  
--select @lockedspid  
--dbcc inputbuffer(@lockedspid)
  
INSERT INTO #tempbelocktracestatus  
EXEC('DBCC INPUTBUFFER('+@belockedspid+')')
  
INSERT INTO #templocktracestatus   
EXEC('DBCC INPUTBUFFER('+@lockedspid+')')
  
INSERT INTO #locktracestatus  
select @belockedspid,'被锁进程',a.EventType,a.EventInfo,@lockedspid,'锁进程',b.EventType,b.EventInfo
  from #tempbelocktracestatus  a,#templocktracestatus b
     
END
  
FETCH NEXT FROM dbcc_inputbuffer INTO @belockedspid,@lockedspid
  END
   
  CLOSE dbcc_inputbuffer
  DEALLOCATE dbcc_inputbuffer
  select * from #locktracestatus
   
  return (0) -- sp_check_deadlock

 

执行该存储过程
exec sp_check_deadlock

 

被锁进程     ID 进程类别 事件类型 被锁语句 锁进程     ID 进程类别 事件类型 锁语句 66 被锁进程 Language  Event SET  LOCK_TIMEOUT -1 SET DEADLOCK_PRIORITY  LOW SET TRANSACTION ISOLATION  LEVEL REPEATABLE READ BEGIN TRAN SELECT * FROM tb WHERE id=1 UPDATE ta SET id=2 WHERE id=1 COMMIT TRAN 65 锁进程 Language  Event SET  LOCK_TIMEOUT -1 SET TRANSACTION  ISOLATION LEVEL REPEATABLE  READ BEGIN TRAN SELECT * FROM ta WHERE id=1 WAITFOR DELAY '00:05:00' UPDATE tb SET id=2 WHERE id=1 COMMIT TRAN