Sql-server – SQL Server Select Count READ_COMMITTED_SNAPSHOT QUESTION

deadlockisolation-levelsql serversql-server-2008

I seem to be getting a lot of deadlocks when doing select count(*) on a particular table.
I have already changed all the required parameters and made them into row only locking.

I've also changed the database to use READ_COMMITTED_SNAPSHOT isolation,

however, it seems that using a select count(*) where column = ? on the table triggers deadlocks or locks on the table..

Am I correct that the select count(*) should only be accessing intermediate rows?, however, it doesn't seem that way and I'm still encountering deadlocks. Proper indexing would probably help,

The question is: Does SQL server 2008 R2 place shared lock on table during select count(*) even when read_committed_snapshot is set to on?

Thanks

Best Answer

Be careful with READ_COMMITTED_SNAPSHOT: if you set it on, it can cause lots of subtle bugs.

Also READ_COMMITTED_SNAPSHOT is the default isolation level, which may be overridden by something. Run DBCC USEROPTIONS to determine the actual isolation level your select runs under.

I would explicitly SET TRANSACTION ISOLATION LEVEL SNAPSHOT right before your select. That way you will be sure your select never embraces in deadlocks, and you do not break any other code, like READ_COMMITTED_SNAPSHOT might.

Related Solutions

Sql-server – Can foreign keys cause deadlocks and hinder READ COMMITTED SNAPSHOT

If the SQLCAT team says that FK validation is done using read-committed isolation, then they must know what they're talking about. Emphasis on validation. The real question is Why would a report trigger FK validation? Validation occurs on writes, and reports are supposed to be reads. Either your reports are causing writes, in which case snapshot isolation levels will help nothing, either the cause of the deadlock is different.

The only way to make progress is to capture the deadlock graph.

As for the other question, how can you check if you operate under snapshot isolation: look in sys.dm_tran_active_snapshot_database_transactions.

Shared Lock on IsolationLevel.ReadUncommitted in SQL Server

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.

Best Answer

Related Solutions

Sql-server – Can foreign keys cause deadlocks and hinder READ COMMITTED SNAPSHOT

Shared Lock on IsolationLevel.ReadUncommitted in SQL Server

Related Question