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

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

 

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/6517/viewspace-350618/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/6517/viewspace-350618/