SQL 如何检查表上持有哪些锁
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/694581/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to check which locks are held on a table
提问by usman shaheen
How can we check which database locks are applied on which rows against a query batch?
我们如何根据查询批处理检查哪些数据库锁应用于哪些行？
Any tool that highlights table row level locking in real time?
任何可以实时突出显示表行级锁定的工具？
DB: SQL Server 2005
数据库：SQL Server 2005
采纳答案by mwigdahl
To add to the other responses, sp_lockcan also be used to dump full lock information on all running processes. The output can be overwhelming, but if you want to know exactly what is locked, it's a valuable one to run. I usually use it along with sp_who2to quickly zero in on locking problems.
添加到其他响应中，sp_lock还可用于转储所有正在运行的进程的完整锁定信息。输出可能会让人不知所措，但如果您想确切知道锁定了什么，那么运行它是很有价值的。我通常将它与sp_who2锁定问题一起快速归零。
There are multiple different versions of "friendlier" sp_lockprocedures available online, depending on the version of SQL Server in question.
sp_lock在线提供多个不同版本的“更友好”过程，具体取决于相关 SQL Server 的版本。
In your case, for SQL Server 2005, sp_lockis still available, but deprecated, so it's now recommended to use the sys.dm_tran_locksview for this kind of thing. You can find an example of how to "roll your own" sp_lock function here.
在您的情况下，对于 SQL Server 2005，sp_lock仍然可用，但已弃用，因此现在建议将sys.dm_tran_locks视图用于此类事情。您可以在此处找到有关如何“推出您自己的” sp_lock 函数的示例。
回答by Brian R. Bondy
This is not exactly showing you which rows are locked, but this may helpful to you.
这并没有完全向您显示哪些行被锁定，但这可能对您有所帮助。
You can check which statements are blocked by running this:
您可以通过运行以下命令来检查哪些语句被阻止：
select cmd,* from sys.sysprocesses
where blocked > 0
It will also tell you what each block is waiting on. So you can trace that all the way up to see which statement caused the first block that caused the other blocks.
它还会告诉您每个块在等待什么。所以你可以一直跟踪它，看看哪个语句导致了第一个块，然后又导致了其他块。
Editto add comment from @MikeBlandford:
编辑以添加来自@MikeBlandford 的评论：
The blocked column indicates the spid of the blocking process. You can run kill {spid} to fix it.
阻塞列表示阻塞进程的 spid。您可以运行 kill {spid} 来修复它。
回答by Somnath Muluk
You can find current locks on your tableby following query.
您可以通过以下查询找到表上的当前锁。
USE yourdatabase;
GO
SELECT * FROM sys.dm_tran_locks
WHERE resource_database_id = DB_ID()
AND resource_associated_entity_id = OBJECT_ID(N'dbo.yourtablename');
If multiple instances of the same request_owner_typeexist, the request_owner_idcolumn is used to distinguish each instance. For distributed transactions, the request_owner_typeand the request_owner_guidcolumns will show the different entity information.
如果存在多个相同request_owner_type 的实例，则使用request_owner_id列来区分每个实例。对于分布式事务，request_owner_type和request_owner_guid列将显示不同的实体信息。
For example, Session S1 owns a shared lock on Table1; and transaction T1, which is running under session S1, also owns a shared lock on Table1. In this case, the resource_descriptioncolumn that is returned by sys.dm_tran_lockswill show two instances of the same resource. The request_owner_typecolumn will show one instance as a session and the other as a transaction. Also, the resource_owner_idcolumn will have different values.
例如，会话 S1 在 Table1 上拥有共享锁；在会话 S1 下运行的事务 T1 也拥有 Table1 上的共享锁。在这种情况下，由sys.dm_tran_locks返回的resource_description列将显示同一资源的两个实例。该request_owner_type栏会显示一个实例作为会话和其他的交易。此外，resource_owner_id列将具有不同的值。
回答by Jon
I use a Dynamic Management View (DMV) to capture locks as well as the object_id or partition_id of the item that is locked.
我使用动态管理视图 (DMV) 来捕获锁定以及被锁定项目的 object_id 或 partition_id。
(MUST switch to the Database you want to observe to get object_id)
（必须切换到您要观察的数据库以获取 object_id）
SELECT
TL.resource_type,
TL.resource_database_id,
TL.resource_associated_entity_id,
TL.request_mode,
TL.request_session_id,
WT.blocking_session_id,
O.name AS [object name],
O.type_desc AS [object descr],
P.partition_id AS [partition id],
P.rows AS [partition/page rows],
AU.type_desc AS [index descr],
AU.container_id AS [index/page container_id]
FROM sys.dm_tran_locks AS TL
INNER JOIN sys.dm_os_waiting_tasks AS WT
ON TL.lock_owner_address = WT.resource_address
LEFT OUTER JOIN sys.objects AS O
ON O.object_id = TL.resource_associated_entity_id
LEFT OUTER JOIN sys.partitions AS P
ON P.hobt_id = TL.resource_associated_entity_id
LEFT OUTER JOIN sys.allocation_units AS AU
ON AU.allocation_unit_id = TL.resource_associated_entity_id;
回答by Neil Barnwell
You can also use the built-in sp_who2stored procedure to get current blocked and blocking processes on a SQL Server instance. Typically you'd run this alongside a SQL Profiler instance to find a blocking process and look at the most recent command that spid issued in profiler.
您还可以使用内置sp_who2存储过程获取 SQL Server 实例上的当前阻塞和阻塞进程。通常，您会与 SQL Profiler 实例一起运行它以查找阻塞进程并查看 spid 在探查器中发出的最新命令。
回答by Metin Atalay
You can find details via the below script.
您可以通过以下脚本找到详细信息。
-- List all Locks of the Current Database
SELECT TL.resource_type AS ResType
,TL.resource_description AS ResDescr
,TL.request_mode AS ReqMode
,TL.request_type AS ReqType
,TL.request_status AS ReqStatus
,TL.request_owner_type AS ReqOwnerType
,TAT.[name] AS TransName
,TAT.transaction_begin_time AS TransBegin
,DATEDIFF(ss, TAT.transaction_begin_time, GETDATE()) AS TransDura
,ES.session_id AS S_Id
,ES.login_name AS LoginName
,COALESCE(OBJ.name, PAROBJ.name) AS ObjectName
,PARIDX.name AS IndexName
,ES.host_name AS HostName
,ES.program_name AS ProgramName
FROM sys.dm_tran_locks AS TL
INNER JOIN sys.dm_exec_sessions AS ES
ON TL.request_session_id = ES.session_id
LEFT JOIN sys.dm_tran_active_transactions AS TAT
ON TL.request_owner_id = TAT.transaction_id
AND TL.request_owner_type = 'TRANSACTION'
LEFT JOIN sys.objects AS OBJ
ON TL.resource_associated_entity_id = OBJ.object_id
AND TL.resource_type = 'OBJECT'
LEFT JOIN sys.partitions AS PAR
ON TL.resource_associated_entity_id = PAR.hobt_id
AND TL.resource_type IN ('PAGE', 'KEY', 'RID', 'HOBT')
LEFT JOIN sys.objects AS PAROBJ
ON PAR.object_id = PAROBJ.object_id
LEFT JOIN sys.indexes AS PARIDX
ON PAR.object_id = PARIDX.object_id
AND PAR.index_id = PARIDX.index_id
WHERE TL.resource_database_id = DB_ID()
AND ES.session_id <> @@Spid -- Exclude "my" session
-- optional filter
AND TL.request_mode <> 'S' -- Exclude simple shared locks
ORDER BY TL.resource_type
,TL.request_mode
,TL.request_type
,TL.request_status
,ObjectName
,ES.login_name;
--TSQL commands
SELECT
db_name(rsc_dbid) AS 'DATABASE_NAME',
case rsc_type when 1 then 'null'
when 2 then 'DATABASE'
WHEN 3 THEN 'FILE'
WHEN 4 THEN 'INDEX'
WHEN 5 THEN 'TABLE'
WHEN 6 THEN 'PAGE'
WHEN 7 THEN 'KEY'
WHEN 8 THEN 'EXTEND'
WHEN 9 THEN 'RID ( ROW ID)'
WHEN 10 THEN 'APPLICATION' end AS 'REQUEST_TYPE',
CASE req_ownertype WHEN 1 THEN 'TRANSACTION'
WHEN 2 THEN 'CURSOR'
WHEN 3 THEN 'SESSION'
WHEN 4 THEN 'ExSESSION' END AS 'REQUEST_OWNERTYPE',
OBJECT_NAME(rsc_objid ,rsc_dbid) AS 'OBJECT_NAME',
PROCESS.HOSTNAME ,
PROCESS.program_name ,
PROCESS.nt_domain ,
PROCESS.nt_username ,
PROCESS.program_name ,
SQLTEXT.text
FROM sys.syslockinfo LOCK JOIN
sys.sysprocesses PROCESS
ON LOCK.req_spid = PROCESS.spid
CROSS APPLY sys.dm_exec_sql_text(PROCESS.SQL_HANDLE) SQLTEXT
where 1=1
and db_name(rsc_dbid) = db_name()
--Lock on a specific object
SELECT *
FROM sys.dm_tran_locks
WHERE resource_database_id = DB_ID()
AND resource_associated_entity_id = object_id('Specific Table');
