SQL 如何检查表是否在sql server中被锁定
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1511675/
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 if a table is locked in sql server
提问by Byron Whitlock
I have a large report I am running on sql server. It takes several minutes to run. I don't want users clicking run twice. Since i wrap the whole procedure in a transaction, how do I check to see if the table is locked by a transaction? If so I would want to return an error message saying "report generating, please try again in a few minutes".
我有一个在 sql server 上运行的大报告。运行需要几分钟。我不希望用户点击运行两次。由于我将整个过程包装在一个事务中，我如何检查表是否被事务锁定？如果是这样，我想返回一条错误消息，说“正在生成报告，请在几分钟后重试”。
How can this be accomplished?
如何做到这一点？
采纳答案by gbn
Better yet, consider sp_getapplockwhich is designed for this. Or use SET LOCK_TIMEOUT
更好的是，考虑sp_getapplock哪个是为此而设计的。或使用SET LOCK_TIMEOUT
Otherwise, you'd have to do something with sys.dm_tran_lockswhich I'd use only for DBA stuff: not for user defined concurrency.
否则，您将不得不做一些sys.dm_tran_locks我只用于 DBA 的事情：而不是用于用户定义的并发。
回答by RRUZ
You can use the sys.dm_tran_locksview, which returns information about the currently active lock manager resources.
您可以使用该sys.dm_tran_locks视图，该视图返回有关当前活动的锁管理器资源的信息。
Try this
尝试这个
SELECT
SessionID = s.Session_id,
resource_type,
DatabaseName = DB_NAME(resource_database_id),
request_mode,
request_type,
login_time,
host_name,
program_name,
client_interface_name,
login_name,
nt_domain,
nt_user_name,
s.status,
last_request_start_time,
last_request_end_time,
s.logical_reads,
s.reads,
request_status,
request_owner_type,
objectid,
dbid,
a.number,
a.encrypted ,
a.blocking_session_id,
a.text
FROM
sys.dm_tran_locks l
JOIN sys.dm_exec_sessions s ON l.request_session_id = s.session_id
LEFT JOIN
(
SELECT *
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(sql_handle)
) a ON s.session_id = a.session_id
WHERE
s.session_id > 50
回答by user1897277
If you are verifying if a lock is applied on a table or not, try the below query.
如果您正在验证是否对表应用了锁，请尝试以下查询。
SELECT resource_type, resource_associated_entity_id,
request_status, request_mode,request_session_id,
resource_description, o.object_id, o.name, o.type_desc
FROM sys.dm_tran_locks l, sys.objects o
WHERE l.resource_associated_entity_id = o.object_id
and resource_database_id = DB_ID()
回答by Naruto
sys.dm_tran_lockscontains the locking information of the sessions
sys.dm_tran_locks包含会话的锁定信息
If you want to know a specific table is locked or not, you can use the following query
如果你想知道一个特定的表是否被锁定，你可以使用下面的查询
SELECT
*
from
sys.dm_tran_locks
where
resource_associated_entity_id = object_id('schemaname.tablename')
if you are interested in finding both login name of the user and the query being run
如果您有兴趣同时查找用户的登录名和正在运行的查询
SELECT
DB_NAME(resource_database_id)
, s.original_login_name
, s.status
, s.program_name
, s.host_name
, (select text from sys.dm_exec_sql_text(exrequests.sql_handle))
,*
from
sys.dm_tran_locks dbl
JOIN sys.dm_exec_sessions s ON dbl.request_session_id = s.session_id
INNER JOIN sys.dm_exec_requests exrequests on dbl.request_session_id = exrequests.session_id
where
DB_NAME(dbl.resource_database_id) = 'dbname'
For more infomraton locking query
有关更多信息锁定查询
More infor about sys.dm_tran_locks
有关sys.dm_tran_locks 的更多信息
