改良的 sp_lock 函数 xp_lock-优快云博客

本文介绍了一个用于查询SQL Server中进程锁情况的存储过程。该过程可以检查指定进程ID（SPID）的锁状态，或者在没有参数时显示所有锁信息。通过此存储过程，管理员能够更有效地诊断和解决由于锁竞争导致的数据库性能问题。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

CREATE PROCEDURE dbo.xp_lock --- 2008-04-18 彦程工作室(http://www.greatengine.com) 萧鹏改良自 sp_lock
@spid1 int = NULL, /* server process id to check for locks */
@spid2 int = NULL /* other process id to check for locks */
as
declare @sql nvarchar(4000)
declare @dbid smallint
declare @dbname nvarchar(256)

set nocount on
/*
** Show the locks for both parameters.
*/
if @spid1 is not NULL
begin
select convert (smallint, req_spid) As spid,
p.hostname,
p.program_name,
p.loginame,
d.name As dbname,
rsc_dbid As dbid,
rsc_objid As ObjId,
convert(nvarchar(256),'') as ObjName,
rsc_indid As IndId,
convert(nvarchar(256),'') as IndName,
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
into #xp_lock
from master.dbo.syslockinfo,
master.dbo.spt_values v,
master.dbo.spt_values x,
master.dbo.spt_values u,
master.dbo.sysdatabases d,
master.dbo.sysprocesses p
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)
and d.dbid=rsc_dbid
and req_spid=p.spid

declare cur_get_dbname cursor forward_only read_only for
select distinct dbname,dbid
from #xp_lock

open cur_get_dbname
fetch next from cur_get_dbname into @dbname,@dbid

while (@@fetch_status = 0)
begin
select @sql=N'update #xp_lock set ObjName=b.name from #xp_lock a,'+@dbname+N'.dbo.sysobjects b where a.ObjId=b.id and a.dbid='+Convert(nvarchar(256),@dbid)
exec(@sql)

select @sql=N'update #xp_lock set IndName=b.name from #xp_lock a,'+@dbname+N'.dbo.sysobjects b where a.IndId=b.id and a.dbid='+Convert(nvarchar(256),@dbid)
exec(@sql)

fetch next from cur_get_dbname into @dbname,@dbid
end
close cur_get_dbname
deallocate cur_get_dbname

select * from #xp_lock
end

/*
** No parameters, so show all the locks.
*/
else
begin
select convert (smallint, req_spid) As spid,
p.hostname,
p.program_name,
p.loginame,
d.name As dbname,
rsc_dbid As dbid,
rsc_objid As ObjId,
convert(nvarchar(256),'') as ObjName,
rsc_indid As IndId,
convert(nvarchar(256),'') as IndName,
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

into #xp_lock1
from master.dbo.syslockinfo,
master.dbo.spt_values v,
master.dbo.spt_values x,
master.dbo.spt_values u,
master.dbo.sysdatabases d,
master.dbo.sysprocesses p

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 d.dbid=rsc_dbid
and req_spid=p.spid
order by spid

declare cur_get_dbname1 cursor forward_only read_only for
select distinct dbname,dbid
from #xp_lock1

open cur_get_dbname1
fetch next from cur_get_dbname1 into @dbname,@dbid

while (@@fetch_status = 0)
begin
select @sql=N'update #xp_lock1 set ObjName=b.name from #xp_lock1 a,'+@dbname+N'.dbo.sysobjects b where a.ObjId=b.id and a.dbid='+Convert(nvarchar(256),@dbid)
exec(@sql)

select @sql=N'update #xp_lock1 set IndName=b.name from #xp_lock1 a,'+@dbname+N'.dbo.sysobjects b where a.IndId=b.id and a.dbid='+Convert(nvarchar(256),@dbid)
exec(@sql)

fetch next from cur_get_dbname1 into @dbname,@dbid
end
close cur_get_dbname1
deallocate cur_get_dbname1

select * from #xp_lock1
end

return (0) -- sp_lock
GO