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 ON
GO
SET QUOTED_IDENTIFIER ON
GO


create 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 */
as

set nocount on
/*
**  Show the locks for both parameters.
*/

create table #t
(spid 	varchar(100),
dbid 	varchar(100),
objid	varchar(100),
indid	varchar(100),
type	varchar(100),
resource	varchar(100),
mode	varchar(100),
status	varchar(100)
)

if @spid1 is not NULL
begin
insert 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 Status

from 	master.dbo.syslockinfo,
master.dbo.spt_values v,
master.dbo.spt_values x,
master.dbo.spt_values u

where   master.dbo.syslockinfo.rsc_type = v.number
and v.type = 'LR'
and master.dbo.syslockinfo.req_status = x.number
and x.type = 'LS'
and master.dbo.syslockinfo.req_mode + 1 = u.number
and u.type = 'L'

and req_spid in (@spid1, @spid2, @spid3, @spid4)
end

/*
**  No parameters, so show all the locks.
*/
else
begin
insert 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 Status
from 	master.dbo.syslockinfo,
master.dbo.spt_values v,
master.dbo.spt_values x,
master.dbo.spt_values u

where   master.dbo.syslockinfo.rsc_type = v.number
and v.type = 'LR'
and master.dbo.syslockinfo.req_status = x.number
and x.type = 'LS'
and master.dbo.syslockinfo.req_mode + 1 = u.number
and u.type = 'L'
order by spid
end

select 	spid, 
dbid, 
db_name(dbid) as '数据库', 
Objid, 
object_name(objid,dbid) as '对象名称', 
indid,
object_name(indid,dbid) as '索引名称', 
type, 
case type
when '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 status
when 'GRANT' then '锁定状态'
when 'WAIT' then '等待状态'
when 'CNVRT' then '转换状态'
end as '请求状态'
from #t order by spid,Objid

drop 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 ON
GO
SET QUOTED_IDENTIFIER ON
GO
create PROCEDURE [dbo].[sp_who_lock]
WITH EXEC AS CALLER
AS
begin  
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