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
TopAnswers
Databases
Top Answer
Martin Smith (imported from SE)
> 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/