Override READ_COMMITTED_SNAPSHOT with READ_COMMITTED?

  • Article

The scenario… An application is using the READ_COMMITTED_SNAPSHOT (RCSI) database option to minimize blocking of SELECT statements by concurrent data modification operations. The application is successfully using this option to reduce the number of blocked processes. They do notice, however, that their application still hard-codes explicit SET TRANSACTION ISOLATION LEVEL READ COMMITTED commands. So the question I received was – does setting “SET TRANSACTION ISOLATION LEVEL READ COMMITTED” prior to a statement execution override the READ_COMMITTED_SNAPSHOT behavior?

To answer this, I used the AdventureWorksDW2008 database. I ran the following code to enable RCSI:

USE master

GO

 

ALTER DATABASE AdventureWorksDW2008

SET READ_COMMITTED_SNAPSHOT ON

 

In a separate query editor window, I executed the following update:

USE AdventureWorksDW2008

GO

 

BEGIN TRAN

 

UPDATE dbo.DimCustomer

SET LastName = 'Stevens'

WHERE CustomerKey = 11004

 

In a second query editor window, I ran the following SELECT query:

USE AdventureWorksDW2008

GO

 

-- Returns Johnson

SELECT LastName

FROM dbo.DimCustomer

WHERE CustomerKey = 11004

 

As I expected – the query returns the previous value of “Johnson” – since the UPDATE I previously executed has not yet committed the transaction – changing the name to “Stevens”.

So next I’ll execute the same SELECT statement, this time explicitly designating READ COMMITTED:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

 

SELECT LastName

FROM dbo.DimCustomer

WHERE CustomerKey = 11004

 

The result? It still returns “Johnson.” Whether you implicitly or explicitly designate READ COMMITTED, having your database configured with RCSI will result in same version generating behavior.

PS: Don’t forget to ROLLBACK TRAN for that UPDATE to dbo.DimCustomer…