Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question

migrated from stackoverflow.com Dec 1 '11 at 2:30

This question came from our site for professional and enthusiast programmers.

2 Answers 2

up vote 10 down vote accepted

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.

share|improve this answer
    
+1 Interesting, how do you capture the acquire/release output? –  Andomar Dec 1 '11 at 11:10
    
@Andomar - Trace Flag 1200 (in conjunction with TF 3604 to return output to the client). Probably best avoided on production servers. ` –  Martin Smith Dec 1 '11 at 11:15
    
+1 Nice catch.. –  Remus Rusanu Dec 1 '11 at 22:39

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.

share|improve this answer
    
Hi, I can see that Sch-S is required, although SQL Server seems not showing it in my lock query. How come it still issue a Shared Lock? According to : msdn.microsoft.com/en-us/library/ms173763.aspx , it stated that "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 –  dsum Dec 1 '11 at 4:24
1  
Post your code, your measurement methodology, and the actual results observed. –  Remus Rusanu Dec 1 '11 at 4:40

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.