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.