NOLOCK overrides isolation level

Context

Even though Read Committed Snapshot Isolation (RCSI) level is set on the database, if NOLOCK hint is used in the query, the query will read uncommitted data instead of reading versioned rows (as it should under RCSI).
Point: NOLOCK overrides RCSI isolation level.

Demo

1. We will use DBATest database that has RCSI enabled and table dbo.t1 which has 10 records.

   /*Verify RCSI enabled*/
   USE DBATest;
   SELECT snapshot_isolation_state,is_read_committed_snapshot_on, name
   FROM sys.databases
   WHERE name ='DBAtest'
   
   SELECT *
   FROM dbo.t1

   

2. I’m going to run an update on table dbo.t1, with begin tran and not commit yet.

   begin tran
   update t1 set b='b_1'

2. Update is not committed yet. From other window, select records from dbo.t1.
   Result: Reads last committed rows from version store in tempdb.

   

   
   3. Now, run the same select statement with NOLOCK hint.
   Result: This reads the uncommitted data from the update which is not committed yet, instead of reading last committed rows from version store.
   Note column b, it displays uncommitted data from the update.

   

4. Now commit or rollback the update.

What did we see

If NOLOCK hint is used in the query, it overrides RCSI configuration and displays uncommitted data(dirty reads).
I don’t suggest using NOLOCK in your queries unless you don’t care about seeing wrong/missed data. You could be reading data that is uncommitted, rolled back or duplicates and that is why it is called dirty reads.

Resources

If you’re interested to read more about Read Committed Isolation level and how it works, here is the link - https://gohigh.substack.com/p/avoid-read-write-blocking-with-read