如何检查哪些锁定了一张表

Question

我们如何检查对查询批处理应用了哪些数据库锁，以及这些锁作用于哪些行？

是否有任何实时突出显示表格行级锁定的工具？

数据库：SQL Server 2005

- usman shaheen

Answer 1

这并不完全显示哪些行被锁定，但这可能对你有所帮助。

你可以运行以下命令来检查哪些语句被阻塞：

select cmd,* from sys.sysprocesses
where blocked > 0

它还会告诉你每个块正在等待什么。所以你可以一直追溯到查看哪个语句导致了第一个块，然后导致其他块。

编辑添加@MikeBlandford的评论：

阻止列显示了阻塞进程的spid。您可以运行kill {spid}来解决它。

- Brian R. Bondy

---

如果您不是DBA并且没有访问“master”的权限，运行此脚本是一个不错的选择。 - Vlad

这个脚本不起作用。我已经开始了一个事务，在其中更新了表中的一行，但是这个脚本没有返回任何行。 - ChumboChappati

Answer 2

除了其他回答之外，sp_lock 还可以用于在所有运行中的进程上转储完整的锁信息。输出可能会很多，但如果您想确切地知道哪些内容被锁定，这将是一个有价值的操作。我通常会与 sp_who2 一起使用，以快速解决锁定问题。

根据所使用的 SQL Server 版本，在网上可以找到多个不同版本的“友好”的 sp_lock 程序。

在您的情况下，对于 SQL Server 2005，sp_lock 仍然可用，但已弃用，现在建议使用 sys.dm_tran_locks 视图来处理此类事情。您可以在此处找到编写自己的 sp_lock 函数示例。

- mwigdahl

Answer 3

通过以下查询，您可以找到表上当前的锁定。

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');

参见sys.dm_tran_locks

如果同一request_owner_type存在多个实例，则使用request_owner_id列来区分每个实例。对于分布式事务，request_owner_type和request_owner_guid列将显示不同的实体信息。

例如，会话S1拥有Table1上的共享锁; 正在会话S1下运行的事务T1也拥有Table1上的共享锁。在这种情况下，由sys.dm_tran_locks返回的resource_description列将显示同一资源的两个实例。 request_owner_type列将显示一个实例为会话，另一个实例为事务。此外，resource_owner_id列将具有不同的值。

- Somnath Muluk

---

这显示了在进程中不可见的锁！！ - user6788933

Answer 4

我使用动态管理视图(DMV)捕获锁以及被锁定的对象的object_id或partition_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;

- Jon

---

我正在尝试使用这个语句来查找进程正在等待的对象。我可以清楚地看到一个会话正在等待另一个会话，使用sp_who2和sys.dm_os_waiting_task（两者都试图更新同一张表）。但是你的语句没有返回任何行。有什么想法吗？ - user330315

Answer 5

您可以通过以下脚本找到详细信息。

-- 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');

- Metin Atalay

Answer 6

您还可以使用内置的sp_who2存储过程来获取SQL Server实例上当前被阻止和阻塞的进程。通常，您会在运行此过程时与SQL Profiler实例一起运行，以查找阻塞进程并查看 spid 在分析器中发出的最近命令。

- Neil Barnwell

Answer 7

您可以通过运行以下命令来查找阻塞 SQL 和等待 SQL：

SELECT
    t1.resource_type ,
    DB_NAME( resource_database_id) AS dat_name ,
    t1.resource_associated_entity_id,
    t1.request_mode,
    t1.request_session_id,
    t2.wait_duration_ms,
    ( SELECT TEXT FROM sys.dm_exec_requests r CROSS apply sys.dm_exec_sql_text ( r.sql_handle ) WHERE r.session_id = t1.request_session_id ) AS wait_sql,
    t2.blocking_session_id,
    ( SELECT TEXT FROM sys.sysprocesses p CROSS apply sys.dm_exec_sql_text ( p.sql_handle ) WHERE p.spid = t2.blocking_session_id ) AS blocking_sql 
FROM
    sys.dm_tran_locks t1,
    sys.dm_os_waiting_tasks t2 
WHERE
    t1.lock_owner_address = t2.resource_address

- l2m2