关于SQLServer死锁的诊断和定位

bq_wang · 发表于 2008-6-18 11:16

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

[ 本帖最后由 bq_wang 于 2008-6-18 11:18 编辑 ]

bq_wang · 发表于 2008-6-18 14:15

被锁进程 ID	进程类别	事件类型	被锁语句	锁进程 ID	进程类别	事件类型	锁语句
66	被锁进程	Language Event	SET LOCK_TIMEOUT -1 SET DEADLOCK_PRIORITY LOW SET TRANSACTION ISOLATION LEVEL REPEATABLE READ BEGIN TRAN SELECT * FROM tb WHERE id=1 UPDATE ta SET id=2 WHERE id=1 COMMIT TRAN	65	锁进程	Language Event	SET LOCK_TIMEOUT -1 SET TRANSACTION ISOLATION LEVEL REPEATABLE READ BEGIN TRAN SELECT * FROM ta WHERE id=1 WAITFOR DELAY '00:05:00' UPDATE tb SET id=2 WHERE id=1 COMMIT TRAN

sqysl · 发表于 2008-6-18 14:59

嗯，高。。。

[原创] 关于SQLServer死锁的诊断和定位