sqlserver 查锁信息

使用sp_who_lock查看锁等待进程spid，使用sp_lock2（可带4个spid参数）查看spid所持有及等待的锁

DBCC TRACEON(3604)

DBCC PAGE('dbname',file_no,page_no,3) WITH TABLERESULTS

GO

 DBCC PAGE
(
['database name'|database id], -- can be the actual name or id of the database
file number, -- the file number where the page is found
page number, -- the page number within the file 
print option = [0|1|2|3] -- display option; each option provides differing levels of information
) 

SP_LOCK2

USE [master]GO/****** Object:  StoredProcedure [dbo].[sp_lock2]    Script Date: 01/13/2015 17:39:07 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOcreate procedure [dbo].[sp_lock2] --- 2002/11/24 00:00@spid1 int = NULL, /* server process id to check for locks */@spid2 int = NULL, /* other process id to check for locks */@spid3 int = NULL, /* other process id to check for locks */@spid4 int = NULL /* other process id to check for locks */asset nocount on/***  Show the locks for both parameters.*/create table #t(spid varchar(100),dbid varchar(100),objidvarchar(100),indidvarchar(100),typevarchar(100),resourcevarchar(100),modevarchar(100),statusvarchar(100))if @spid1 is not NULLbegininsert into #t(spid, dbid, objid, indid, type,resource, mode, status)select convert (smallint, req_spid) As spid,rsc_dbid As dbid,rsc_objid As ObjId,rsc_indid As IndId,substring (v.name, 1, 4) As Type,substring (rsc_text, 1, 16) as Resource,substring (u.name, 1, 8) As Mode,substring (x.name, 1, 5) As Statusfrom master.dbo.syslockinfo,master.dbo.spt_values v,master.dbo.spt_values x,master.dbo.spt_values uwhere   master.dbo.syslockinfo.rsc_type = v.numberand v.type = 'LR'and master.dbo.syslockinfo.req_status = x.numberand x.type = 'LS'and master.dbo.syslockinfo.req_mode + 1 = u.numberand u.type = 'L'and req_spid in (@spid1, @spid2, @spid3, @spid4)end/***  No parameters, so show all the locks.*/elsebegininsert into #t(spid, dbid, objid, indid, type,resource, mode, status)select convert (smallint, req_spid) As spid,rsc_dbid As dbid,rsc_objid As ObjId,rsc_indid As IndId,substring (v.name, 1, 4) As Type,substring (rsc_text, 1, 16) as Resource,substring (u.name, 1, 8) As Mode,substring (x.name, 1, 5) As Statusfrom master.dbo.syslockinfo,master.dbo.spt_values v,master.dbo.spt_values x,master.dbo.spt_values uwhere   master.dbo.syslockinfo.rsc_type = v.numberand v.type = 'LR'and master.dbo.syslockinfo.req_status = x.numberand x.type = 'LS'and master.dbo.syslockinfo.req_mode + 1 = u.numberand u.type = 'L'order by spidendselect spid, dbid, db_name(dbid) as '数据库', Objid, object_name(objid,dbid) as '对象名称', indid,object_name(indid,dbid) as '索引名称', type, case typewhen 'DB' then '数据库' when 'FIL' then '文件'when 'IDX' then '索引'when 'PAG' then '页面'when 'KEY' then '索引键值'when 'TAB' then '表'when 'TEXT' then '区域'when 'RID' then '行标志号'end as '资源类型',resource, mode, case upper(mode)when 'S' then '共享锁'when 'X' then '排它锁'when 'U' then '更新锁'when 'IS' then '意向共享锁'when 'IX' then '意向排它锁'when 'SIX' then '共享意向排它锁'when 'SCH-S' then '调度稳定性锁'when 'SCH-M' then '调度修改锁'when 'BU' then '批量更新锁' end as '锁定模式',status,case statuswhen 'GRANT' then '锁定状态'when 'WAIT' then '等待状态'when 'CNVRT' then '转换状态'end as '请求状态'from #t order by spid,Objiddrop table #t;return (0) -- sp_lock2

SP_WHO_LOCK

USE [master]GO/****** Object:  StoredProcedure [dbo].[sp_who_lock]    Script Date: 01/08/2015 04:01:37 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOcreate PROCEDURE [dbo].[sp_who_lock]WITH EXEC AS CALLERASbegin  declare  @spid int,@bl int,   @intTransactionCountOnEntry  int,   @intRowcount    int,   @intCountProperties   int,   @intCounter    int       create table #tmp_lock_who (   id int identity(1,1),   spid smallint,   bl smallint)      IF @@ERROR<>0 RETURN @@ERROR      insert into #tmp_lock_who(spid,bl) select  0 ,blocked     from (select * from sysprocesses where  blocked>0 ) a      where not exists(select * from (select * from sysprocesses where  blocked>0 ) b      where a.blocked=spid)     union select spid,blocked from sysprocesses where  blocked>0       IF @@ERROR<>0 RETURN @@ERROR       -- 找到临时表的记录数   select  @intCountProperties = Count(*),@intCounter = 1   from #tmp_lock_who      IF @@ERROR<>0 RETURN @@ERROR       if @intCountProperties=0    select '现在没有阻塞和死锁信息' as message      -- 循环开始  while @intCounter <= @intCountProperties  begin  -- 取第一条记录    select  @spid = spid,@bl = bl  from #tmp_lock_who where Id = @intCounter     begin       if @spid =0               select '引起数据库死锁的是: '+ CAST(@bl AS VARCHAR(10)) + '进程号,其执行的SQL语法如下'       else              select '进程号SPID：'+ CAST(@spid AS VARCHAR(10))+ '被' + '进程号SPID：'+ CAST(@bl AS VARCHAR(10)) +'阻塞,其当前进程执行的SQL语法如下'       DBCC INPUTBUFFER (@bl )    end       -- 循环指针下移    set @intCounter = @intCounter + 1  end      drop table #tmp_lock_who      return 0  end