Marios Philippopoulos (4/4/2010)
The database code in our system is littered with NOLOCK hints, and we are considering switching to Read Committed Snapshot Isolation (RCSI) in the near future. Once RCSI is in place, will it override the NOLOCK hints in the code? In other words, will SELECTs read committed data, even with the NOLOCK still in place?
No, READ_COMMITTED_SNAPSHOT only applies to operations performed at the (default) READ_COMMITTED isolation level.
NOLOCK hints explicitly request READ UNCOMMITTED semantics, and will continue to function as before.
Just one more reason that NOLOCK hints are evil, I am afraid. Setting the isolation level required at connection time, or by using the SET TRANSACTION ISOLATION LEVEL statement, makes maintenance much easier, as I am sure you well appreciate! Every developer responsible for adding the NOLOCK hints should be tasked with removing them 😉
When changing to RCSI, be sure to fully test any trigger code you have, and add READCOMMITTEDLOCK hints where required. Also, be aware that certain operations, like checking foreign key values, are always performed using READ_COMMITTED semantics, regardless of the RCSI setting.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi