关于SQLServer死锁的诊断和定位

原创于2008年06月18日，2009年10月18日迁移至此。

关于SQLServer死锁的诊断和定位

 

在SQLServer中经常会发生死锁情况，必须连接到企业管理器—>管理—>当前活动—>锁/进程ID去查找相关死锁进程和定位死锁的原因。ITPUB个人空间4eD!w!`JD&h{X!tH{6517

通过查询分析器也要经过多个系统表(sysprocesses,sysobjects等)和系统存储过程(sp_who,sp_who2,sp_lock等)，而且不一定能够直接定位到。

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

 

Create procedure sp_check_deadlock            

as

set nocount on

 /*

selectITPUB个人空间6lE|*`f4`/0jE
spid   被锁进程ID,
Mao^#Qz9w~6517blocked锁进程ID,
2WO+G;a2KSP6517status 被锁状态,
!j0Qzb
hx6517SUBSTRING(SUSER_SNAME(sid),1,30)被锁进程登陆帐号,
%i'j/Z;w(G7v3fO)Sf2E6517SUBSTRING(hostname,1,12)        被锁进程用户机器名称,ITPUB个人空间h9@u
U2W&e6eT
SUBSTRING(DB_NAME(dbid),1,10)   被锁进程数据名称,
wUX/2o6517cmd被锁进程命令,
U}]fa8x,U?"q{6517waittype被锁进程等待类型
4[P1M[VM@Y$r6517FROM master..sysprocessesITPUB个人空间9O2s"OQ*@
WHERE blocked>0ITPUB个人空间*PD2C,Z%Giq

-ZVf4d4y/bz6517--dbcc inputbuffer(66)输出相关锁进程的语句
*/ITPUB个人空间_R~!kt;TW a

-H4HSV1O6517--创建锁进程临时表
CREATE TABLE #templocktracestatus (ITPUB个人空间&X7X8Ix8z,` @8~
  EventType varchar(100),ITPUB个人空间mn^V `
  Parameters INT,
w'a"rp!G6517  EventInfo varchar(200)
dDM2z&l0A6517  )ITPUB个人空间xPuK^ KF5I&o%Y9d#}g
`_y(j6517

--创建被锁进程临时表
CREATE TABLE #tempbelocktracestatus (ITPUB个人空间Z%Gs-WhVe+J
  EventType varchar(100),ITPUB个人空间*uGw]SQ6Q#C
  Parameters INT,ITPUB个人空间(dYn7GzC0Vm
  EventInfo varchar(200)

HF-D8L4lA2v6517  )b6Rn+x|QG6517

--创建之间的关联表
CREATE TABLE #locktracestatus (ITPUB个人空间O+Q4J,D1W6V
  belockspid INT,ITPUB个人空间lPzl+@a$uc
  belockspidremark varchar(20),ITPUB个人空间)f1C7pk2k�Z7@] ?
  belockEventType varchar(100),ITPUB个人空间&j.hMaj:S
  belockEventInfo varchar(200),
S6Oud%MPJ$Qy6517  lockspid INT,ITPUB个人空间0TY%p/-Vj
  lockspidremark  varchar(20),ITPUB个人空间A.h.o0KZz|
  lockEventType   varchar(100),ITPUB个人空间%O"^#V3qA0E i
  lockEventInfo   varchar(200)ITPUB个人空间'v!ApW4EuL
  )
ywX/mnf/Z6517ITPUB个人空间+Uk-UBQc@5_+s7M
--获取死锁进程ITPUB个人空间Q
A+^g+ZAB

DECLARE dbcc_inputbuffer CURSOR READ_ONLYITPUB个人空间d
o3Z0R%zV
FOR select spid被锁进程ID,blocked锁进程IDITPUB个人空间
hZ#K}7[y T]
     FROM master..sysprocessesITPUB个人空间_D5ma'j+ow!w
    WHERE blocked>0

DECLARE @lockedspid int             ITPUB个人空间.z/b-Yw{U;hjDy
DECLARE @belockedspid intITPUB个人空间5j9ik:l,RJ

V*|J~5T4H.Y.m7K6517OPEN dbcc_inputbufferITPUB个人空间 d*aC[6_

FETCH NEXT FROM dbcc_inputbuffer INTO @belockedspid,@lockedspid

WHILE (@@fetch_status <> -1)ITPUB个人空间1P}`iy;{/U$YZ
BEGIN
WSw[Q6Bun+v6517       IF (@@fetch_status <> -2)
D[d kF wl6517       BEGIN

        --print '被堵塞进程'ITPUB个人空间tvs+^{t(p:Q
        --select @belockedspid
_+B`
P2k+MX/6517        --dbcc inputbuffer(@belockedspid)ITPUB个人空间/Gj[VT
        --print '堵塞进程'ITPUB个人空间Sx$}[7{,P
        --select @lockedspid
w3i+UDzM OtK6517        --dbcc inputbuffer(@lockedspid)

        INSERT INTO #tempbelocktracestatus
Im%B;}l6517          EXEC('DBCC INPUTBUFFER('+@belockedspid+')')

        INSERT INTO #templocktracestatus
5b4Eq*y0GpC6517          EXEC('DBCC INPUTBUFFER('+@lockedspid+')') 

        INSERT INTO #locktracestatus
6ENh5l$v WV1Wh6517          select @belockedspid,'被锁进程',a.EventType,a.EventInfo,@lockedspid,'锁进程',b.EventType,b.EventInfo
5/suz,a/mq6517            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

 
d3lB'XI{7[o0q6517

 

执行该存储过程

exec sp_check_deadlock