DEV Community

Sardar Mudassar Ali Khan
Sardar Mudassar Ali Khan

Posted on

5

Why do we NOLOCK in SQL server with an example

In SQL Server, the NOLOCK hint, also known as the READUNCOMMITTED isolation level, allows a SELECT statement to read data from a table without acquiring shared locks on the data. This means it can potentially read uncommitted changes made by other transactions, which can lead to what's called "dirty reads."

Here's an example:

Let's say you have a table named Employee with columns EmployeeID and EmployeeName.

CREATE TABLE Employee (
    EmployeeID INT,
    EmployeeName VARCHAR(100)
);

INSERT INTO Employee (EmployeeID, EmployeeName)
VALUES (1, 'Alice'), (2, 'Bob'), (3, 'Charlie');
Enter fullscreen mode Exit fullscreen mode

Now, if two transactions are happening concurrently:

Transaction 1:

BEGIN TRANSACTION
UPDATE Employee
SET EmployeeName = 'David'
WHERE EmployeeID = 1;
Enter fullscreen mode Exit fullscreen mode

Transaction 2:

SELECT EmployeeName
FROM Employee WITH (NOLOCK)
WHERE EmployeeID = 1;
Enter fullscreen mode Exit fullscreen mode

If Transaction 2 uses WITH (NOLOCK) when reading the Employee table, it might read the uncommitted change made by Transaction 1 and retrieve 'David' as the EmployeeName for EmployeeID 1. However, if Transaction 1 rolled back the update, Transaction 2 would have obtained inaccurate or non-existent data, resulting in a "dirty read."

Using NOLOCK can be helpful in scenarios where you prioritize reading data speed over strict consistency. However, it's essential to be cautious since it can lead to inconsistent or inaccurate results, especially in critical transactional systems.

Other considerations like potential data inconsistencies, increased chance of reading uncommitted data, and potential performance implications should be weighed before using NOLOCK. In many cases, alternative isolation levels or database design improvements might be more suitable to achieve the desired performance without sacrificing data integrity.

AWS Security LIVE!

Join us for AWS Security LIVE!

Discover the future of cloud security. Tune in live for trends, tips, and solutions from AWS and AWS Partners.

Learn More

Top comments (0)

Billboard image

The Next Generation Developer Platform

Coherence is the first Platform-as-a-Service you can control. Unlike "black-box" platforms that are opinionated about the infra you can deploy, Coherence is powered by CNC, the open-source IaC framework, which offers limitless customization.

Learn more