0

John Huang's function for converting lock resources - ConvertedLockResource

Posted on Monday, April 20, 2015 by 醉·醉·鱼 and labeled under , ,
出处： http://www.sqlnotes.info/2011/10/24/locking-and-blocking-5-lock-resources-in-extended-events/#more-588

在用extended events观察SQL SERVER lock行为的时候，resource_0, resource_1, resource_2 比较让人困惑，究竟是什么意思。John Huang的博客写了一个函数，方便我们去理解具体在那个object上面lock的行为。


USE [master]
GO

create function [dbo].[ConvertedLockResource](@ResourceType sysname, @res0 bigint, @res1 bigint, @res2 bigint)
returns varchar(60)
as
begin
        if @ResourceType = 'OBJECT'
                return cast(@res0 as varchar(20));
        else if @ResourceType in ('PAGE', 'EXTENT')
        begin
                return cast(@res1 as varchar(10)) + ':' + cast(@res0 as varchar(20))
        end
        else if @ResourceType = 'RID'
        begin
                return  cast(cast(cast(right(cast(@res1 as binary(8)),2) as binary(2)) as smallint) as varchar(10))+ ':'
                                + cast(@res0 as varchar(20))+':'
                                + cast(cast(cast(left(right(cast(@res1 as binary(8)),4), 2) as binary(2)) as smallint) as varchar(10))
        end
        else if @ResourceType = 'HOBT'
        begin
                return cast(cast(
                                                        cast(right(cast(right(cast(@res1 as binary(8)),4) as binary(4)), 2) as binary(2))
                                                        +cast(0x0000 as binary(2))
                                                        + cast(right(cast(right(cast(@res0 as binary(8)),4) as binary(4)), 2) as binary(2))
                                                        + cast(left(cast(right(cast(@res0 as binary(8)),4) as binary(4)), 2) as binary(2))
                                                as bigint)
                                        as varchar(20))
        end
        else if @ResourceType = 'KEY'
        begin
                return  '('
                                + lower(convert( varchar(20),
                                                        cast(substring(cast(@res1 as binary(8)), 6, 1) as binary(1))
                                                        + cast(substring(cast(@res1 as binary(8)), 5, 1) as binary(1))
                                                        + cast(substring(cast(@res2 as binary(8)),8, 1) as binary(1))
                                                        + cast(substring(cast(@res2 as binary(8)),7, 1) as binary(1))
                                                        + cast(substring(cast(@res2 as binary(8)),6, 1) as binary(1))
                                                        + cast(substring(cast(@res2 as binary(8)),5, 1) as binary(1))
                                                ,2))
                                +')/'
                                + cast(cast(
                                                        cast(right(cast(right(cast(@res1 as binary(8)),4) as binary(4)), 2) as binary(2))
                                                        +cast(0x0000 as binary(2))
                                                        + cast(right(cast(right(cast(@res0 as binary(8)),4) as binary(4)), 2) as binary(2))
                                                        + cast(left(cast(right(cast(@res0 as binary(8)),4) as binary(4)), 2) as binary(2))
                                                as bigint)
                                        as varchar(20))
        end
        return null
end

GO
0
Responses to ... John Huang's function for converting lock resources - ConvertedLockResource

Post a Comment