關於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死鎖的診斷和定位

相關文章