Shared Lock issued on IsolationLevel.ReadUncommitted
What is HOBT lock?
A lock protecting a B-tree (index) or the heap data pages in a table that does not have a clustered index.
Why would I still get a S lock?
This happens on heaps. Example
SET NOCOUNT ON;
DECLARE @Query nvarchar(max) =
N'DECLARE @C INT;
SELECT @C = COUNT(*) FROM master.dbo.MSreplication_options';
/*Run once so compilation out of the way*/
EXEC(@Query);
DBCC TRACEON(-1,3604,1200) WITH NO_INFOMSGS;
PRINT 'READ UNCOMMITTED';
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
EXEC(@Query);
PRINT 'READ COMMITTED';
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
EXEC(@Query);
DBCC TRACEOFF(-1,3604,1200) WITH NO_INFOMSGS;
Output READ UNCOMMITTED
Process 56 acquiring Sch-S lock on OBJECT: 1:1163151189:0 (class bit0 ref1) result: OK
Process 56 acquiring S lock on HOBT: 1:72057594038910976 [BULK_OPERATION] (class bit0 ref1) result: OK
Process 56 releasing lock on OBJECT: 1:1163151189:0
Output READ COMMITTED
Process 56 acquiring IS lock on OBJECT: 1:1163151189:0 (class bit0 ref1) result: OK
Process 56 acquiring IS lock on PAGE: 1:1:169 (class bit0 ref1) result: OK
Process 56 releasing lock on PAGE: 1:1:169
Process 56 releasing lock on OBJECT: 1:1163151189:0
According to this article referencing Paul Randal the reason for taking this BULK_OPERATION
shared HOBT lock is to prevent reading of unformatted pages.
ReadUncommitted isolation level does acquire locks. Schema stability locks prevent the objects being queries from being altered while the query executes. This locks are acquired under all isolation leveles, including snapshot and read_committed_snapshot (RCSI). From Lock Modes:
Schema Locks
The Database Engine uses schema modification (Sch-M) locks during a table data definition language (DDL) operation, such as adding a column or dropping a table. During the time that it is held, the Sch-M lock prevents concurrent access to the table. This means the Sch-M lock blocks all outside operations until the lock is released.
Some data manipulation language (DML) operations, such as table truncation, use Sch-M locks to prevent access to affected tables by concurrent operations.
The Database Engine uses schema stability (Sch-S) locks when compiling and executing queries. Sch-S locks do not block any transactional locks, including exclusive (X) locks. Therefore, other transactions, including those with X locks on a table, continue to run while a query is being compiled. However, concurrent DDL operations, and concurrent DML operations that acquire Sch-M locks, cannot be performed on the table.