Impact of NOLOCK on READ COMMITTED SNAPSHOT ISOLATION


Heard a similar question today, related to my post on “Override READ_COMMITTED_SNAPSHOT with READ_COMMITTED.”  Question was – what is the impact of NOLOCK when RCSI is enabled for a database.


Here is the demo script I sent, showing the behavior with and without NOLOCK for an RCSI enabled database.  Basically – you’ll see uncommitted data modifications with NOLOCK as you would if RCSI was not enabled:


Session #1:


 


USE AdventureWorksDW2008


GO


 


BEGIN TRAN


 


UPDATE dbo.DimCustomer


SET LastName = ‘Stevens’


WHERE CustomerKey = 11004


 


Session #2:


 


— Returns Johnson


SELECT LastName


FROM dbo.DimCustomer


WHERE CustomerKey = 11004


 


— Returns Stevens


SELECT LastName


FROM dbo.DimCustomer


WITH (NOLOCK)


WHERE CustomerKey = 11004