查看SQL SERVER LOCK的详细信息。

[w_xxxbbb]

用SP_LOCK执行了一下，就一张表，干巴巴的，但又不知道跟哪些系统表关联，有没有查看LOCK详细信息的查询语句？

[dawugui]

[Quote=引用楼主 w_xxxbbb 的回复:]
用SP_LOCK执行了一下，就一张表，干巴巴的，但又不知道跟哪些系统表关联，有没有查看LOCK详细信息的查询语句？
[/Quote]
SP_LOCK报告有关锁的信息。你需要查询什么?

sp_lock
报告有关锁的信息。

语法
sp_lock [[@spid1 =] 'spid1'] [,[@spid2 =] 'spid2']

参数
[@spid1 =] 'spid1'

是来自 master.dbo.sysprocesses 的 Microsoft® SQL Server™ 进程 ID 号。spid1 的数据类型为 int，默认值为 NULL。执行 sp_who 可获取有关该锁的进程信息。如果没有指定 spid1，则显示所有锁的信息。

[@spid2 =] 'spid2'

是用于检查锁信息的另一个 SQL Server 进程 ID 号。spid2 的数据类型为 int，默认设置为 NULL。spid2 为可以与 spid1 同时拥有锁的另一个 spid，用户还可获取有关它的信息。 



说明  sp_who 可含有 0 个、1 个或 2 个参数。这些参数确定存储过程是显示全部、1 个还是 2 个 spid 进程的锁定信息。


返回代码值
0（成功）

结果集
列名 数据类型 描述 
spid smallint SQL Server 进程 ID 号。 
dbid smallint 请求锁的数据库标识号。 
ObjId int 请求锁的对象的对象标识号。 
IndId smallint 索引标识号。 
type nchar(4) 锁的类型： 
DB：数据库
FIL：文件
IDX：索引
PG：页
KEY：键
TAB：表
EXT：扩展盘区
RID：行标识符
 
Resource nchar(16) 与 syslockinfo.restext 中的值对应的锁资源。 
Mode nvarchar(8) 锁请求者的锁模式。该锁模式代表已授予模式、转换模式或等待模式。 
Status int 锁的请求状态 
GRANT
WAIT
CNVRT
 


注释
用户可以通过向 SELECT 语句的 FROM 子句中添加优化程序提示或设置 SET TRANSACTION ISOLATION LEVEL 选项来控制锁定。有关语法和限制的信息，请参见 SELECT 和 SET TRANSACTION ISOLATION LEVEL。 

通常，读操作获取共享锁，写操作获取排它锁。在更新操作的初始阶段读取数据时，会获取更新锁。更新锁与共享锁兼容。此后，如果更改了数据，更新锁会提升为排它锁。有时在更改数据时，会在获取排它锁之前暂时获取更新锁。此后，该更新锁会自动提升为排它锁。

可以锁定各种级别的数据，包括整个表、表中的一页或多页以及表的一行或多行。粒度级别较高的意向锁表示正在或试图以较低的锁粒度级别获取锁。例如，表意向锁表示获取共享页级锁或排它页级锁的意向。意向锁可以阻止另一个事务获取该表的表锁。 

当分配或释放由 8 个数据库页组成的组时，将持有扩展盘区锁。扩展盘区锁在运行 CREATE 或 DROP 语句时或在运行需要新数据或索引页的 INSERT 或 UPDATE 语句时设置。

在读取 sp_lock 信息时，使用 OBJECT_NAME( ) 函数通过表的 ID 号获取表的名称，例如：

SELECT object_name(16003088)

所有与 SPID 值不关联的分布式事务都是孤立事务。SQL Server 2000 给所有孤立的分布式事务赋予 SPID 值"-2"

，使得用户更容易识别阻塞的分布式事务。有关更多信息，请参见 KILL。 

有关使用 Windows NT 性能监视器查看特定进程 ID 信息的更多信息，请参见 DBCC。 

权限
执行权限默认授予 public 角色。

示例
A. 列出所有锁
下面的示例显示 SQL Server 中当前持有的所有锁的信息。

USE master
EXEC sp_lock

B. 列出单个服务器进程的锁
下例显示进程 ID 53 的信息（其中包括锁信息）。

USE master
EXEC sp_lock 53

[dawugui]

你是查看锁信息吗?

--查看锁信息

exec   sp_who

----------------------------------------------
create table #t(req_spid int,obj_name sysname)

declare @s nvarchar(4000)
    ,@rid int,@dbname sysname,@id int,@objname sysname

declare tb cursor for 
    select distinct req_spid,dbname=db_name(rsc_dbid),rsc_objid
    from master..syslockinfo where rsc_type in(4,5)
open tb
fetch next from tb into @rid,@dbname,@id
while @@fetch_status=0
begin
    set @s='select @objname=name from ['+@dbname+']..sysobjects where id=@id'
    exec sp_executesql @s,N'@objname sysname out,@id int',@objname out,@id
    insert into #t values(@rid,@objname)
    fetch next from tb into @rid,@dbname,@id
end
close tb
deallocate tb

select 进程id=a.req_spid
    ,数据库=db_name(rsc_dbid)
    ,类型=case rsc_type when 1 then 'NULL 资源（未使用）'
        when 2 then '数据库'
        when 3 then '文件'
        when 4 then '索引'
        when 5 then '表'
        when 6 then '页'
        when 7 then '键'
        when 8 then '扩展盘区'
        when 9 then 'RID（行 ID)'
        when 10 then '应用程序'
    end
    ,对象id=rsc_objid
    ,对象名=b.obj_name
    ,rsc_indid
 from master..syslockinfo a left join #t b on a.req_spid=b.req_spid

go
drop table #t

[w_xxxbbb]

想查看锁定某个数据库表的信息。最近数据库中一个表A经常被锁定，（应用程序提示TIME OUT，提示无法执行UPDATE的T-SQL）该表A不大，上面有触发器，一旦插入修改该表，则将修改的信息记录下来到另一张LOG表中，这个LOG表已经有3G大小，我怀疑是因为这个触发器的问题，造成其他程序插入或修改表A时，由于触发器插入LOG表时间过长，所以想查看一下该表被锁定的详细信息。比如时间等等等等。

[唐诗三百首]

怎样查清楚锁定关系,方法有很多,不是三两句可以讲清楚D.
建议楼主看一下这方面的书,比提问更有效.以下方法供参考.
1.SQL Server Profiler追踪执行时间长的SQL,一般时间长的是遇到锁或全表扫描.
2.系统表master.dbo.sysprocesses可实时查看进程的状况.该表BLOCKED,STATUS,WAITTYPE,OPEN_TRAN,WAITTIME,LASTWAITTYPE这些字段与锁有关.
3.SP: sp_who2 [SPID] 查看进程详细信息
DBCC INPUTBUFFER([SPID]) 查看进程最后提交的SQL语句
4.注意所使用的事务隔离级别,可能造成SQL已执行完,但锁定还在的情况.
暂时想到这些,其他请楼下补充..

[飘零一叶]

--查看锁信息
SELECT request_session_id
,resource_type
,resource_database_id
,resource_description
,resource_associated_entity_id
,request_mode
,request_status 
FROM sys.dm_tran_locks

--存储过程sp_lock查看锁信息
EXEC sp_lock

SELECT * FROM sys.syslockinfo

--查询sys.dm_exec_connections视图得到发生冲突的连接信息
SELECT * FROM sys.dm_exec_connections WHERE session_id IN(54)

--查询会话视图
SELECT * FROM sys.dm_exec_sessions WHERE session_id IN(52,55)

exec sp_who
exec sp_who2
select * from sys.sysprocesses

--
SELECT * FROM sys.dm_exec_requests where blocking_session_id>0

--sys.dm_exec_connections视图包含一个二进制句柄,你可以把它提供给函数sys.dm_exec_sql_text
select session_id,text
from sys.dm_exec_connections cross apply sys.dm_exec_sql_text(most_recent_sql_handle) as st
where session_id IN(54)


dbcc inputbuffer (52)
dbcc inputbuffer (55)

[abuying]

create  procedure sp_who_lock
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

   

GO

[liweibird]

此文甚好

[admin]

写的不错，好东西。