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:
ALTER DATABASE AdventureWorksDW2008
SET READ_COMMITTED_SNAPSHOT ON
In a separate query editor window, I executed the following update:
SET LastName = 'Stevens'
WHERE CustomerKey = 11004
In a second query editor window, I ran the following SELECT query:
-- Returns Johnson
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
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…