SQL Server Interview Question #160

What is the difference between NOLOCK and READ_COMMITTED_SNAPSHOT?

Transactions, Locking, Blocking & Deadlocks Senior Advanced

Detailed Explanation

NOLOCK is a table hint that allows READ UNCOMMITTED-style reads for the referenced table. It can read uncommitted data and can produce inconsistent results, including effects from rows changing while the scan is occurring.

READ_COMMITTED_SNAPSHOT (RCSI) changes READ COMMITTED behavior at the database level so eligible readers use committed row versions rather than waiting on writers for data locks. This can reduce reader-writer blocking without permitting dirty reads.

RCSI is therefore fundamentally different from NOLOCK. It has version-store and concurrency implications and should be tested before enabling, but it is often a more appropriate concurrency strategy than adding NOLOCK throughout application queries.

Code Example

ALTER DATABASE YourDatabase
SET READ_COMMITTED_SNAPSHOT ON
WITH ROLLBACK IMMEDIATE;