Impact of NOLOCK on READ COMMITTED SNAPSHOT ISOLATION

  • Article

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