SQL Select vs Select With (NoLock)

Select is a standard SQL query to retrieve data. When a SELECT statement is executed, it acquires a shared lock on the data being read. This prevents other transactions from modifying the data while it is being read, ensuring data consistency.

Select With (NoLock) retrieves data without acquiring shared locks on the data. As Shared Lock is not applied, it can read data that is being modified by other transactions, which might not yet commit. So sometimes this can be termed as Dirty Read.

As this can read uncommitted data, which might lead to reading inconsistent or “dirty” data. This means the query might return data that is currently being modified and not yet finalized.

Using NoLock, reduces locking contention and potential blocking, improving query performance, especially in high-concurrency environments.

We can use NoLock when performance is more important than absolute data accuracy.

Examples:- Consider a scenario where you need to retrieve all orders placed after a certain date:

BEGIN TRANSACTION;

— User 1: Standard SELECT query SELECT OrderID, OrderDate, CustomerID FROM Orders WHERE OrderDate > ‘2024–01–01’;

— User 2: Update query that will cause a lock UPDATE Orders SET OrderDate = GETDATE() WHERE OrderID = 1;

COMMIT TRANSACTION;

Behavior:

• User 1’s SELECT query will acquire a shared lock on the Orders table.
• User 2’s UPDATE query will wait for User 1's SELECT to release the shared lock before it can proceed.
• This ensures data consistency but can lead to blocking if multiple transactions try to access the same data simultaneously.

Now consider the same scenario, but using the WITH (NOLOCK) hint:

BEGIN TRANSACTION;

— User 1: SELECT query with NOLOCK hint SELECT OrderID, OrderDate, CustomerID FROM Orders WITH (NOLOCK) WHERE OrderDate > ‘2024–01–01’;

— User 2: Update query that will not be blocked UPDATE Orders SET OrderDate = GETDATE() WHERE OrderID = 1;

COMMIT TRANSACTION;

Behavior:

• User 1’s SELECT query with WITH (NOLOCK) does not acquire a shared lock.
• User 2’s UPDATE query can proceed without waiting for User 1's SELECT to complete.
• This improves performance and reduces blocking, but User 1 might read uncommitted or inconsistent data (e.g., the order date being updated by User 2).

Using WITH (NOLOCK) can significantly improve performance in high-concurrency environments by reducing lock contention. However, it should be used with caution, as it can lead to reading uncommitted or "dirty" data, which might not reflect the actual state of the database at the time of the query.

Shared lock: Shared does not block reads. Shared lock blocks update. By default, a select (read) takes a shared lock. Shared (S) locks allow concurrent transactions to read (SELECT) a resource. A shared lock as no effect on other selects (1 or a 1000). No other transactions can modify the data while shared (S) locks exist on the resource.

More on SQL Locks.

Hoping you enjoyed reading this Article.

Thank you for reading. you can follow me on LinkedIn and Medium