SQL2005中的事务与锁定（三）- 转载
时间：2021-07-01 10:21:17
帮助过：2人阅读
Proc sp_us_lockinfo
---------------------------------------------------------------------
-- Author : HappyFlyStone
-- Date : 2009-10-03 15:30:00
-- BLOG : http://blog.csdn.net/happyflystone
-- 申明 ：请保留作者信息，转载注明出处
--------------------------------------------------------------------
AS
BEGIN
SELECT
DB_NAME(t1.resource_database_id)
AS [数据库名],
t1.resource_type AS [资源类型],
-- t1.request_type AS [请求类型],
t1.request_status
AS [请求状态],
-- t1.resource_description AS [资源说明],
CASE t1.request_owner_type
WHEN ‘TRANSACTION‘ THEN ‘事务所有‘
WHEN ‘CURSOR‘ THEN ‘游标所有‘
WHEN ‘SESSION‘ THEN ‘用户会话所有‘
WHEN ‘SHARED_TRANSACTION_WORKSPACE‘ THEN ‘事务工作区的共享所有‘
WHEN ‘EXCLUSIVE_TRANSACTION_WORKSPACE‘ THEN ‘事务工作区的独占所有‘
ELSE ‘‘
END AS [拥有请求的实体类型],
CASE WHEN T1.resource_type
= ‘OBJECT‘
THEN OBJECT_NAME(T1.resource_ASsociated_entity_id)
ELSE T1.resource_type
+‘:‘+ISNULL(
LTRIM(T1.resource_ASsociated_entity_id),
‘‘)
END AS [锁定的对象],
t4.[name] AS [索引],
t1.request_mode AS [锁定类型],
t1.request_session_id AS [当前spid],
t2.blocking_session_id AS [锁定spid],
-- t3.snapshot_isolation_state AS [快照隔离状态],
t3.snapshot_isolation_state_desc
AS [快照隔离状态描述],
t3.is_read_committed_snapshot_on AS [已提交读快照隔离]
FROM
sys.dm_tran_locks AS t1
left join
sys.dm_os_waiting_tasks AS t2
ON
t1.lock_owner_address = t2.resource_address
left join
sys.databases AS t3
ON t1.resource_database_id
= t3.database_id
left join
(
SELECT rsc_text,rsc_indid,rsc_objid,b.
[name]
FROM
sys.syslockinfo a
JOIN
sys.indexes b
ON a.rsc_indid
= b.index_id
and b.
object_id = a.rsc_objid) t4
ON t1.resource_description
= t4.rsc_text
END
GO
/*
调用示例：exec sp_us_lockinfo
*/
exec sp_us_lockinfo
/*
SQL2005中的事务与锁定（三）- 转载
标签：
