关于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个人空间&g5yR P5Bt~ ITPUB个人空间yv?wpH7B

 

Create procedure sp_check_deadlock             as set nocount on  /* selectITPUB个人空间6lE|*`f4`/0jE spid    被锁进程 ID, Mao^#Qz9w~6517 blocked 锁进程 ID, 2WO+G;a2KSP6517 status  被锁状态 , !j0Qzbhx6517 SUBSTRING(SUSER_SNAME(sid),1,30) 被锁进程登陆帐号 , %i'j/Z;w(G7v3fO)Sf2E6517 SUBSTRING(hostname,1,12)         被锁进程用户机器名称 ,ITPUB个人空间 h9@uU2W&e6eT SUBSTRING(DB_NAME(dbid),1,10)    被锁进程数据名称 , wUX /2o6517 cmd 被锁进程命令 , U}]fa8x,U?"q {6517 waittype 被锁进程等待类型 4[P1M[VM@Y$r6517 FROM 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"r p!G6517   EventInfo varchar(200) dDM2z&l0A6517   ) ITPUB个人空间xPuK^ KF5I&o%Y 9d#}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$u c   belockspidremark varchar(20),ITPUB个人空间)f1C7pk2k�Z7@] ?   belockEventType varchar(100),ITPUB个人空间&j.hMa j:S   belockEventInfo varchar(200), S6Oud%M PJ$Qy6517   lockspid INT,ITPUB个人空间0TY%p/-Vj   lockspidremark  varchar(20),ITPUB个人空间A.h.o0KZ z|   lockEventType   varchar(100),ITPUB个人空间%O"^#V3qA0E i   lockEventInfo   varchar(200)ITPUB个人空间'v!ApW4EuL   ) yw X/mnf/Z6517 ITPUB个人空间+Uk-UBQ c @5_+s7M -- 获取死锁进程 ITPUB个人空间QA+^ g+ZAB DECLARE dbcc_inputbuffer CURSOR READ_ONLYITPUB个人空间do3Z0R%z V 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.m7K6517 OPEN 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[V T         --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