Override READ_COMMITTED_SNAPSHOT with READ_COMMITTED?


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…


Comments (2)

  1. Heard a similar question today, related to my post on " Override READ_COMMITTED_SNAPSHOT with READ_COMMITTED