Skip to content

Locking and Blocking

October 31, 2011

Reference to page 264, book Microsoft SQL Server 2008 Internals, wirtten by Kalen Delaney.

Lock resource: Object, HOBT, Page, Extent, RID, and KEY. for others, such as DATABASE and FILE

Example:

  • OBJECT: return Object ID
  • PAGE/EXETNT: return Page or Extent address
  • HOBT: return partition id of the object
  • RID: return address of the row, format is file:page:slot
  • KEY: return (key hash)/parition_id, for instance, (992da965bcee)/72057594038779904, it means partition_id = 72057594038779904, key hash = (992da965bcee). You can use key hash value to position a record. for instance select * from SimpleTable where %%lockres%% = '(992da965bcee)'

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

Advertisements

From → DBA

Leave a Comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s

%d bloggers like this: