Deep Dive into NOLOCK in SQL Server

What is NOLOCK?

The NOLOCK table hint is used in SQL Server to allow reading data without acquiring shared locks, thereby avoiding blocking due to other transactions. It is equivalent to the READ UNCOMMITTED isolation level.

Example:

SELECT * FROM Orders WITH (NOLOCK);

Advantage: Improves query performance by avoiding locks.
Risk: Can lead to dirty reads, phantom reads, and inconsistent data.

How SQL Server Handles Locks

Normally, SQL Server locks data during transactions to ensure consistency:

• Shared Lock (S) → Used for reads (SELECT queries). Prevents modification while reading.
• Exclusive Lock (X) → Used for writes (INSERT, UPDATE, DELETE). Blocks other reads.
• Update Lock (U) → Used before updates to prevent conflicts.

👉 NOLOCK bypasses these locks, meaning it can read data even if another transaction is modifying it.

Potential Issues with NOLOCK

Although NOLOCK improves performance, it can lead to serious data integrity issues. Let's explore 5 major problems:

Dirty Reads (Reading Uncommitted Data)

• If a transaction is updating a row but has not committed it yet, NOLOCK allows another query to read that uncommitted data.
• If the first transaction rolls back, the second query has already read incorrect data.

Example:

-- Transaction 1 (Not yet committed)

BEGIN TRAN

UPDATE Orders SET Amount = 500 WHERE OrderID = 101;

-- Transaction 2 (Using NOLOCK)

SELECT Amount FROM Orders WITH (NOLOCK) WHERE OrderID = 101;

-- Might return 500 (even if rollback occurs)

Problem: If Transaction 1 rolls back, Transaction 2 has already seen incorrect data.

Non-Repeatable Reads (Data Changing During Query)

If a query runs with NOLOCK, another transaction might modify the data midway through execution, causing inconsistent results.

Example:

-- Transaction 1 (Using NOLOCK)

SELECT Amount FROM Orders WITH (NOLOCK) WHERE OrderID = 101;

-- Returns: 300

-- Transaction 2 (Another transaction updates the same row)

UPDATE Orders SET Amount = 500 WHERE OrderID = 101;

-- Transaction 1 (Repeating the query)

SELECT Amount FROM Orders WITH (NOLOCK) WHERE OrderID = 101;

-- Returns: 500 (Changed mid-query)

Problem: The same query gives different results within a single transaction.

Phantom Reads (New Data Appears Unexpectedly)

With NOLOCK, new rows might appear midway through execution.

Example:

-- Transaction 1 (Using NOLOCK)

SELECT COUNT(*) FROM Orders WITH (NOLOCK); 

-- Returns 100 rows

-- Transaction 2 (Another transaction inserts new data)

INSERT INTO Orders VALUES (101, 500, '2024-03-01');

-- Transaction 1 (Repeating the query)

SELECT COUNT(*) FROM Orders WITH (NOLOCK); -- Returns 101 rows

Problem: The row count changes during execution, leading to inconsistent reports.

Missing Rows (Skipping Data Due to Page Splits)

NOLOCK does not respect order consistency, meaning a query may skip rows if data is being moved between pages.

Example:

-- Transaction 1 (Using NOLOCK)

SELECT * FROM Orders WITH (NOLOCK) ORDER BY OrderID;

-- Transaction 2 (A page split happens due to new inserts)

INSERT INTO Orders VALUES (200, 700, '2024-03-01');

-- Transaction 1 (Continuing the scan)

-- Some rows are skipped or appear twice.

Problem: Some rows might be missing or duplicated, leading to incorrect reports.

Index Corruption (Reading Half-Written Data)

If a transaction is updating an index, NOLOCK might read inconsistent index pages.

Example:

SELECT * FROM Orders WITH (NOLOCK) ORDER BY OrderID;

• If an index is being rebuilt midway, the query might read partially updated index pages.
• This can cause duplicate rows or missing rows in results.

Problem: NOLOCK can return corrupted data due to index rebuilds.

When is NOLOCK Safe to Use?

Using NOLOCK is acceptable in certain scenarios: Read-only reports where minor inconsistencies don’t matter.
Large read-heavy systems where blocking causes major slowdowns.
Historical data queries (e.g., last year’s sales, archived data).

Avoid NOLOCK for: Financial transactions (e.g., banking, invoicing).
Critical data reports (e.g., payroll, customer balances).
Data that is frequently updated.

Alternative Approaches Instead of NOLOCK

Instead of using NOLOCK, you can use better alternatives:

Use Read-Committed Snapshot Isolation (RCSI)

Prevents blocking without reading uncommitted data.

ALTER DATABASE YourDatabase SET READ_COMMITTED_SNAPSHOT ON;

Avoids blocking Ensures data consistency.

Use WITH (READPAST) Instead of NOLOCK

READPAST skips locked rows instead of reading uncommitted data.

SELECT * FROM Orders WITH (READPAST);

Ensures correct data while avoiding blocking.

Use SNAPSHOT Isolation

Allows queries to read previously committed data instead of uncommitted rows.

SET TRANSACTION ISOLATION LEVEL SNAPSHOT;

SELECT * FROM Orders;

Avoids dirty reads and ensures data consistency.

Summary: Should You Use NOLOCK?

Feature	Benefit	Risk
Avoids blocking	Faster queries	May read dirty data
Good for reporting	No impact on updates	Data can be inconsistent
Skips or duplicates rows	Faster queries	Wrong results in reports
Reads half-written data	No waiting time	Corrupts reports
Not safe for financial data	No locks required	Causes serious integrity issues

Use NOLOCK only when data accuracy is NOT critical.
For financial or critical reports, avoid it.