Shared Lock issued on IsolationLevel.ReadUncommitted

Question

I read that if I use IsolationLevel.ReadUncommitted, the query shouldn't issue any lock. However, when I tested this, I saw the following lock: Resource_Type: HOBT Request_Mode: S (Shared)

What is HOBT lock? Something related to HBT (Heap or Binary Tree lock)

Why would I still get a S lock?

How do I avoid shared locking on querying without turning on isolation level snapshot option?

I am testing this on SQLServer 2008, and the snapshot option is set to off. The query only performs select.

Thanks

Answer 1

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.

Answer 2

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.