sqlserver 事务与锁定

发布时间：2020-12-12 14:52:16 所属栏目：MsSql教程来源：网络整理

导读：转载地址：http://www.voidcn.com/article/p-oncuytta-bdr.html ? Create Proc sp_us_lockinfo --------------------------------------------------------------------- -- Author : HappyFlyStone -- Date?? : 2009-10-03 15:30:00 -- BLOG?? : http://blo

转载地址：http://www.voidcn.com/article/p-oncuytta-bdr.html

Create Proc sp_us_lockinfo

---------------------------------------------------------------------

-- Author : HappyFlyStone

-- Date?? : 2009-10-03 15:30:00

-- BLOG?? : http://blog.csdn.net/happyflystone

-- 申明? ??：请保留作者信息，转载注明出处

---------------------------------------------------------------------

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

调用示例：exec sp_us_lockinfo

exec sp_us_lockinfo

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!