Sql-server – Shared Lock issued on IsolationLevel.ReadUncommitted

isolation-levellockingsql serversql-server-2008

I read that if I use IsolationLevel.ReadUncommitted, the query should not issue any locks. However, when I tested this, I saw the following lock:

Resource_Type: HOBT
Request_Mode: S (Shared)

What is a 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 when querying without turning on the isolation level snapshot option?

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

I can see that Sch-S is required, although SQL Server seems not to be showing it in my lock query. How come it still issues a Shared Lock? According to:

SET TRANSACTION ISOLATION LEVEL (Transact-SQL)

Transactions running at the READ UNCOMMITTED level do not issue shared locks to prevent other transactions from modifying data read by the current transaction.

So I am a little confused.

Best Answer

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.

Related Question