如何检查表上持有哪些锁

如何检查对查询批处理的哪些行应用了哪些数据库锁?

有什么工具可以实时突出显示表行级锁吗?

数据库:SQL Server 2005

863562 次浏览
小开

这并没有确切地显示哪些行被锁定，但这可能对您有帮助。

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

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

它还会告诉你每个块正在等待什么。所以你可以一直追踪到哪条语句引起了第一个块，又引起了其他块。

编辑添加来自@MikeBlandford的注释:

blocked列表示阻塞进程的spid。你可以运行kill {spid}来修复它。

小开

你也可以使用内置的sp_who2存储过程来获取SQL Server实例上当前阻塞和阻塞的进程。通常，您将与SQL Profiler实例一起运行该命令，以查找阻塞进程，并查看spid在Profiler中发出的最新命令。

小开
最佳答案

为了添加到其他响应，sp_lock也可以用来转储所有运行进程的完整锁信息。输出可能非常庞大，但是如果您想确切地知道锁定了什么，那么运行它是很有价值的。我通常将它与sp_who2一起使用，以快速解决锁定问题。

根据所讨论的SQL Server版本，在线上有多个不同版本的“更友好”sp_lock过程。

在你的例子中，对于SQL Server 2005， sp_lock仍然可用，但已弃用，所以现在建议使用sys.dm_tran_locks视图来处理这类事情。你可以找到一个如何“滚动你自己的”sp_lock函数在这里的例子。

小开

我使用动态管理视图(Dynamic Management View, 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;
小开

你可以通过下面的查询找到当前的桌子上的锁。

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列将有不同的值。

小开

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

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

你可以通过运行下面的命令找到blocking sql和wait 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

提交答案