<转>sql server查询被锁表的信息

最新推荐文章于 2025-09-25 15:55:25 发布

原创最新推荐文章于 2025-09-25 15:55:25 发布 · 272 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#sql server #锁表

sqlserver 专栏收录该内容

5 篇文章

订阅专栏

本文提供了一个SQL查询示例，用于检查SQL Server中当前被锁定的表及其锁的相关详细信息。通过这个查询，可以了解到正在发生锁冲突的具体位置，包括SPID、对象名称、锁类型等。

想查询当前哪些表被锁了，并且锁的信息可用以下语句

SELECT
request_session_id as Spid,
Coalesce(s.name + '.' + o.name + isnull('.' + i.name,''),
s2.name + '.' + o2.name,
db.name) AS Object,
l.resource_type as Type,
request_mode as Mode,
request_status as Status
FROM sys.dm_tran_locks l
LEFT JOIN sys.partitions p
ON l.resource_associated_entity_id = p.hobt_id
LEFT JOIN sys.indexes i
ON p.object_id = i.object_id
AND p.index_id = i.index_id
LEFT JOIN sys.objects o
ON p.object_id = o.object_id
LEFT JOIN sys.schemas s
ON o.schema_id = s.schema_id
LEFT JOIN sys.objects o2
ON l.resource_associated_entity_id = o2.object_id
LEFT JOIN sys.schemas s2
ON o2.schema_id = s2.schema_id
LEFT JOIN sys.databases db
ON l.resource_database_id = db.database_id
WHERE resource_database_id = DB_ID()
ORDER BY Spid, Object, CASE l.resource_type
When 'database' Then 1
when 'object' then 2
when 'page' then 3
when 'key' then 4
Else 5 end