Oct 282011
 

Nolock table hint tells SQL Server engine to not place locks to rows and/or pages while reading them. With this hint enabled, query will not be blocked by any row level modification. It’s equivalent to READUNCOMMITTED table hint in which it allows query return uncommitted data. Many people probably already know that schema stability lock will be applied to the table being access with this table hint, but it’s not always the trueth. In some cases, it will apply Shared lock to the HOBT — partition.

Now let’s prepare test environment, ceate test database and also some test data which is organized in clustered index, see highlighted line 22 below. In this experiment, we need supporting function [dbo].[ConvertedLockResource] posted in Locking and Blocking (5) in master database.

use master
set nocount on
if DB_ID('test') is not null
begin
	alter database test set read_only with rollback immediate
	drop database test
end
go
create database test;
go
alter database test set recovery simple
alter database test set auto_create_statistics off
alter database test set auto_update_statistics  off
alter database test set allow_snapshot_isolation off
alter database test set read_committed_snapshot off

alter database tempdb set auto_create_statistics off
alter database tempdb set auto_update_statistics  off
go
use test
create table SimpleTable(id int, text1 char(3000))
create unique clustered index id on SimpleTable(id)
go
insert into SimpleTable
	values	(100, replicate('a',111)),
		(200, replicate('b',111)),
		(300, replicate('c',111)),
		(400, replicate('d',111))

go
exec xp_cmdshell N'del c:\temp\locktest00*.*' ,no_output  
go
CREATE EVENT SESSION locks ON SERVER ADD EVENT sqlserver.lock_acquired, ADD EVENT sqlserver.lock_released ADD TARGET package0.asynchronous_file_target(set filename = 'c:\temp\locktest00') WITH (MAX_MEMORY = 4096KB,EVENT_RETENTION_MODE = NO_EVENT_LOSS ,MAX_DISPATCH_LATENCY = 1 SECONDS,MEMORY_PARTITION_MODE = NONE,TRACK_CAUSALITY = ON,STARTUP_STATE = OFF);
ALTER EVENT SESSION locks ON SERVER STATE = START

select * from SimpleTable with(nolock) where id = 300

ALTER EVENT SESSION locks ON SERVER STATE = STOP
go
DROP EVENT SESSION locks ON SERVER
GO
declare @Temp table (ID int identity(1,1), EventData xml);insert into @Temp(EventData) select  cast(event_data as xml) event_data	from sys.fn_xe_file_target_read_file ('c:\temp\locktest00*', 'c:\temp\locktest00*.xem', null, null) a;with x as (select a.ID, x.value('../@name', 'varchar(128)') EventName, x.value('../@timestamp', 'datetime') EventTime, x.value('@name','varchar(128)') [Col], case when isnull(rtrim(x.value('(./text)[1]', 'varchar(128)')), '') = '' then x.value('(.)[1]', 'varchar(128)') else x.value('(./text)[1]', 'varchar(128)') end [Value] from @Temp a  cross apply EventData.nodes('//data') ed(x)) 
select ID, EventName, ResourceType, Mode, OwnerType, DBID, cast(master.dbo.ConvertedLockResource(ResourceType, Res0, Res1, Res2) as varchar(20)) Resource, Res0, res1, Res2, lockspace_workspace_id, lockspace_sub_id, lockspace_nest_id, TransID, EventTime from( select ID, cast(EventName as varchar(20)) as EventName, cast(resource_type as varchar(10)) as ResourceType,  cast(mode as varchar(10)) Mode, cast(owner_type as varchar(12)) OwnerType, cast(database_id as int) DBID, cast(resource_0 as varchar(16)) Res0 , cast(resource_1 as varchar(16)) Res1,  cast(resource_2 as varchar(16)) Res2, cast(lockspace_workspace_id as varchar(20)) lockspace_workspace_id, cast(lockspace_sub_id as varchar(10)) lockspace_sub_id,  cast(lockspace_nest_id as varchar(10)) lockspace_nest_id, cast(transaction_id as int) TransID, EventTime  from x  pivot( MAX([Value]) for Col in ([resource_type], [mode], [owner_type], [transaction_id], [database_id], [lockspace_workspace_id], [lockspace_sub_id], [lockspace_nest_id], [resource_0], [resource_1], [resource_2])) pvt  ) y where ResourceType not in ('METADATA') and Mode not in ('NL')
go
/*
ID          EventName            ResourceType Mode       OwnerType    DBID        Resource             Res0             res1             Res2             lockspace_workspace_id lockspace_sub_id lockspace_nest_id TransID     EventTime
----------- -------------------- ------------ ---------- ------------ ----------- -------------------- ---------------- ---------------- ---------------- ---------------------- ---------------- ----------------- ----------- -----------------------
2           lock_acquired        OBJECT       SCH_S      Transaction  13          2105058535           2105058535       0                0                0x0000000080000a00     1                1                 455142      2011-10-23 06:34:23.543
3           lock_released        OBJECT       SCH_S      Transaction  13          2105058535           2105058535       0                0                0x0000000080000a00     1                1                 455142      2011-10-23 06:34:23.543

*/

The schema stability lock is granted to object which id = 2105058535. This tells other session that while I am reading the data, any modification to the table schema is prohibited. Now if you comment line 22 out in the table definition in which it puts table in to heap structure, then run the second script again. What you will get is below:

ID          EventName            ResourceType Mode       OwnerType    DBID        Resource             Res0             res1             Res2             lockspace_workspace_id lockspace_sub_id lockspace_nest_id TransID     EventTime
----------- -------------------- ------------ ---------- ------------ ----------- -------------------- ---------------- ---------------- ---------------- ---------------------- ---------------- ----------------- ----------- -----------------------
2           lock_acquired        OBJECT       SCH_S      Transaction  13          2105058535           2105058535       0                0                0x0000000080000a00     1                1                 456775      2011-10-23 06:40:11.170
3           lock_acquired        HOBT         S          Transaction  13          72057594038779904    13               256              2                0x0000000080000a00     1                1                 456775      2011-10-23 06:40:11.170
4           lock_released        HOBT         S          Transaction  13          72057594038779904    13               256              2                0x0000000080000a00     1                1                 456775      2011-10-23 06:40:11.170
5           lock_released        OBJECT       SCH_S      Transaction  13          2105058535           2105058535       0                0                0x0000000080000a00     1                1                 456775      2011-10-23 06:40:11.170

See the differences? in addition to SCH_S lock on the object, a Shared lock is granted to HOBT (Heap-or-BTree, partition) while data reading. The resource of HOBT 72057594038779904 is partition_id which can be found in sys.partitions table.

 Leave a Reply

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre class="">

(required)

(required)

C# | HTML | Plain Text | SQL | XHTML | XML | XSLT |

This site uses Akismet to reduce spam. Learn how your comment data is processed.