识别SQL Server 2008中的锁 [英] Identifying Locks in SQL Server 2008

问题描述

如何获取SQL Server用来执行我的存储过程的锁。



我有一个存储过程，它可以调用超过5个表。

如何在执行我的sql存储过程时知道每个表上应用的锁是什么。



先谢谢你..

How can i get the locks used by the SQL Server to execute my Stored Procedure.

I'm having stored procedure which invloves more than 5 tables.
How can i know what all locks applied on each table while executing my sql Stored procedure.

Thanks in Advance..

推荐答案

你可以使用这个查询检查锁...

u can use this query to check the locks...

SELECT db.name DBName, tl.request_session_id, wt.blocking_session_id,                  
       OBJECT_NAME(p.OBJECT_ID) BlockedObjectName,tl.resource_type,
       h1.TEXT AS RequestingText, h2.TEXT AS BlockingTest, tl.request_mode
FROM sys.dm_tran_locks AS tl
INNER JOIN sys.databases db ON db.database_id = tl.resource_database_id
INNER JOIN sys.dm_os_waiting_tasks AS wt ON tl.lock_owner_address = wt.resource_address
INNER JOIN sys.partitions AS p ON p.hobt_id = tl.resource_associated_entity_id
INNER JOIN sys.dm_exec_connections ec1 ON ec1.session_id = tl.request_session_id
INNER JOIN sys.dm_exec_connections ec2 ON ec2.session_id = wt.blocking_session_id
CROSS APPLY sys.dm_exec_sql_text(ec1.most_recent_sql_handle) AS h1
CROSS APPLY sys.dm_exec_sql_text(ec2.most_recent_sql_handle) AS h2

这篇关于识别SQL Server 2008中的锁的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！