What is "with (nolock)" in SQL Server?

Understanding 'with (nolock)' in SQL Server

Explore the purpose, implications, and proper use of the WITH (NOLOCK) hint in SQL Server for reading uncommitted data.

In the world of SQL Server, managing concurrent access to data is crucial for performance and data integrity. Transactions ensure that data modifications are atomic, consistent, isolated, and durable (ACID). Isolation levels dictate how transactions interact with each other, particularly concerning locks. One common hint you might encounter, especially in reporting or read-heavy scenarios, is WITH (NOLOCK). This article delves into what NOLOCK does, why it's used, and its significant drawbacks.

What is WITH (NOLOCK)?

The WITH (NOLOCK) table hint, also known as READ UNCOMMITTED, instructs SQL Server to read data without acquiring shared locks. This means that a query using NOLOCK can read data that is currently being modified by another transaction, even if that transaction has not yet been committed. Essentially, it allows a query to bypass the standard locking mechanisms that prevent dirty reads, non-repeatable reads, and phantom reads.

flowchart TD
    A[Client A: UPDATE Transaction] --> B{Acquires Exclusive Lock}
    B --> C[Modifies Data (Uncommitted)]
    D[Client B: SELECT with NOLOCK] --> E{Reads Data}
    E --> F[Bypasses Locks]
    F --> G[Returns Uncommitted Data]
    C --> H{Client A: COMMIT / ROLLBACK}
    H -- COMMIT --> I[Data is Permanent]
    H -- ROLLBACK --> J[Data Reverts]
    G -- If ROLLBACK --> K[Client B read 'Dirty Data']

How WITH (NOLOCK) interacts with an uncommitted transaction

When a transaction modifies data, it typically places an exclusive lock on the affected rows or pages to prevent other transactions from reading or modifying that data until the changes are committed or rolled back. NOLOCK tells SQL Server to ignore these exclusive locks and read the data as it currently exists, regardless of its transactional state.

SELECT column1, column2
FROM YourTable WITH (NOLOCK)
WHERE column3 = 'some_value';

Basic usage of WITH (NOLOCK) in a SELECT statement

Why Use NOLOCK? (And Why You Might Not Want To)

The primary motivation for using NOLOCK is performance. By not acquiring shared locks, a query can avoid waiting for other transactions to release their exclusive locks. This can significantly speed up read operations, especially in systems with high concurrency and long-running write transactions. It's often considered for reporting or analytical queries where slight data inconsistencies are deemed acceptable in exchange for faster results.

The Dangers and Side Effects of NOLOCK

The benefits of NOLOCK are often outweighed by its severe drawbacks. When you use NOLOCK, you expose your queries to several types of data inconsistencies:

• Dirty Reads (Uncommitted Data): You might read data that has been modified by another transaction but not yet committed. If that transaction later rolls back, the data you read never actually existed in the database.
• Non-Repeatable Reads: If you execute the same SELECT statement twice within the same transaction using NOLOCK, you might get different results because another transaction could have committed changes between your two reads.
• Phantom Reads: A SELECT statement might return a different set of rows if executed twice within the same transaction, as other transactions might have inserted or deleted rows that were not locked.
• Missing Rows or Duplicate Rows: This is a less obvious but critical issue. When NOLOCK scans data pages, another transaction might be moving rows between pages (e.g., due to page splits or row updates that change row size). NOLOCK might read the same row twice or miss it entirely if it's moved during the scan. This can lead to incorrect aggregations (SUM, COUNT, AVG) or missing records.
• Reading Partially Updated Data: If a transaction updates multiple columns in a row, NOLOCK might read some columns with their old values and others with their new values, leading to an inconsistent view of a single row.

Visualizing a 'Dirty Read' where uncommitted data is accessed.

Alternatives to NOLOCK

Given the significant risks, it's generally recommended to avoid NOLOCK unless you have a very specific, well-understood use case where data accuracy is secondary to performance, and you can tolerate the inconsistencies. Better alternatives include:

1. READ COMMITTED SNAPSHOT ISOLATION (RCSI): This is often the preferred solution. When enabled at the database level, SELECT statements operate on a snapshot of the data as it was at the beginning of the statement, using row versioning in tempdb. This eliminates blocking for readers without introducing dirty reads.
2. SNAPSHOT ISOLATION: Similar to RCSI but provides a consistent view of the data for the entire transaction, not just a single statement. Requires explicit SET TRANSACTION ISOLATION LEVEL SNAPSHOT.
3. Proper Indexing and Query Tuning: Often, performance issues can be resolved by optimizing queries and ensuring appropriate indexes are in place, reducing the need for hints like NOLOCK.
4. Optimistic Locking: For application-level concurrency control, where conflicts are rare, optimistic locking can be used to detect and resolve conflicts at the time of commit rather than preventing them with locks.

ALTER DATABASE YourDatabase SET READ_COMMITTED_SNAPSHOT ON;

-- Then, your SELECT statements will automatically use RCSI without hints
SELECT column1, column2
FROM YourTable
WHERE column3 = 'some_value';

Enabling Read Committed Snapshot Isolation (RCSI) at the database level

Enabling RCSI changes the default behavior of the READ COMMITTED isolation level to use row versioning instead of locking for read operations. This is a database-wide setting and generally a much safer and more robust approach than scattering WITH (NOLOCK) hints throughout your code.