关于 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
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 #templocktracestatus (
EventType varchar(100),
Parameters INT,
EventInfo varchar(200)
)
(
-- 创建被锁进程临时表
CREATE TABLE #tempbelocktracestatus (
EventType varchar(100),
Parameters INT, EventInfo varchar(200)
)
CREATE TABLE #tempbelocktracestatus (
EventType varchar(100),
Parameters INT, EventInfo varchar(200)
)
-- 创建之间的关联表
CREATE TABLE #locktracestatus (
CREATE TABLE #locktracestatus (
belockspid INT,
belockspidremark varchar(20),
belockEventType varchar(100),
belockEventInfo varchar(200),
lockspid INT
belockspidremark varchar(20),
belockEventType varchar(100),
belockEventInfo varchar(200),
lockspid INT
lockspidremark varchar(20)
lockEventType varchar(100)
lockEventInfo varchar(200)
)
-- 获取死锁进程
lockEventType varchar(100)
lockEventInfo varchar(200)
)
-- 获取死锁进程
DECLARE dbcc_inputbuffer CURSOR READ_ONLY
FOR select spid 被锁进程 ID,blocked 锁进程 ID
FROM master..sysprocesses
WHERE blocked>0
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
BEGIN
IF (@@fetch_status <> -2)
BEGIN
--print ' 被堵塞进程
--select @belockedspid
--dbcc inputbuffer(@belockedspid
--print ' 堵塞进程 --select @lockedspid
--dbcc inputbuffer(@lockedspid)
--select @belockedspid
--dbcc inputbuffer(@belockedspid
--print ' 堵塞进程 --select @lockedspid
--dbcc inputbuffer(@lockedspid)
INSERT INTO #tempbelocktracestatus
EXEC('DBCC INPUTBUFFER( '+@belockedspid+')')
EXEC('DBCC INPUTBUFFER( '+@belockedspid+')')
INSERT INTO #templocktracestatus
5b4Eq*y0GpC6517 EXEC('DBCC INPUTBUFFER( '+@lockedspid+')')
5b4Eq*y0GpC6517 EXEC('DBCC INPUTBUFFER( '+@lockedspid+')')
INSERT INTO #locktracestatus
select @belockedspid,' 被锁进程 ',a.EventType,a.EventInfo,@lockedspid,' 锁进程
from #tempbelocktracestatus a,#templocktracestatus b
select @belockedspid,' 被锁进程 ',a.EventType,a.EventInfo,@lockedspid,' 锁进程
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
本文转自baoqiangwang51CTO博客，原文链接：http://blog.51cto.com/baoqiangwang/313006
，如需转载请自行联系原作者
