or
dsum imported from SE
sql-server sql-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)][1]

>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.


  [1]: http://msdn.microsoft.com/en-us/library/ms173763.aspx
Top Answer
Martin Smith
> 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.][1]

> 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`

```none
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`

```none
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][2] referencing Paul Randal the reason for taking this `BULK_OPERATION` shared HOBT lock is to prevent reading of unformatted pages.


  [1]: https://msdn.microsoft.com/en-us/library/ms189849.aspx
  [2]: https://tenbulls.co.uk/2011/10/14/nolock-hits-mythbusters/
Shared Lock issued on IsolationLevel.ReadUncommitted

This is a dedicated room for discussion about this question.

Once logged in you can direct comments to the question poster (or any answer poster) here.