关于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

【原】关于SQLServer死锁的诊断和定位