Sample Database for NOLOCK in SQL Server

NOLOCK in SQL Server can be one of those bittersweet features that may sound great, but may not always do what you think it may be doing.

Here’s a step-by-step demonstration to create a sample Toys database and demonstrate the WITH (NOLOCK) hint in SQL Server. The NOLOCK hint is commonly used for reading uncommitted data, which can be useful in certain scenarios but comes with the risk of encountering “dirty reads.”

T-SQL Script for Creating the Sample Database and Tables to demo NOLOCK in SQL Server

-- Create the Toys database
CREATE DATABASE Toys;
GO

-- Switch to the Toys database
USE Toys;
GO

-- Create a sample table
CREATE TABLE ToysInventory (
    ToyID INT IDENTITY(1,1) PRIMARY KEY,
    ToyName NVARCHAR(50),
    Quantity INT,
    Price DECIMAL(10,2),
    LastUpdated DATETIME DEFAULT GETDATE()
);
GO

-- Insert sample data
INSERT INTO ToysInventory (ToyName, Quantity, Price)
VALUES 
('Action Figure', 100, 12.99),
('Toy Car', 200, 5.99),
('Building Blocks', 50, 19.99),
('Doll', 75, 14.99),
('Puzzle', 120, 9.99);
GO

Procedure to Simulate Concurrent Transactions

We’ll create a stored procedure that updates the ToysInventory table, and a query that demonstrates the effects of WITH (NOLOCK).

Stored Procedure for Updating Data

-- Create a stored procedure to simulate an update
CREATE PROCEDURE UpdateToyQuantity
    @ToyID INT,
    @Quantity INT
AS
BEGIN
    -- Update the toy's quantity
    UPDATE ToysInventory
    SET Quantity = Quantity + @Quantity,
        LastUpdated = GETDATE()
    WHERE ToyID = @ToyID;

    -- Wait for a few seconds to simulate a long-running transaction
    WAITFOR DELAY '00:00:10';
END;
GO

Queries to Demonstrate `WITH (NOLOCK)`

Step 1: Start a Transaction Without Committing

Run the following query in Session 1 (or Query Window 1):

-- Start a transaction and update the table without committing
BEGIN TRAN;

UPDATE ToysInventory
SET Quantity = Quantity - 10
WHERE ToyID = 1;

-- OR

EXEC UpdateToyQuantity @ToyID  = 2, @Quantity = 50;


-- Check the updated value inside the transaction
SELECT * FROM ToysInventory WHERE ToyID = 1;
-- Do not commit or rollback yet, leave the transaction open

Step 2: Query the Data Using `WITH (NOLOCK)`

In Session 2 (or Query Window 2), execute the following queries to observe the behavior of WITH (NOLOCK):

-- Query without NOLOCK (default behavior)
SELECT * 
FROM ToysInventory
WHERE ToyID = 1;

-- The above query will wait on the previous locks to clear.
-- using NOLOCK in SQL Server gives a way around that with some dangers.am

-- Query with NOLOCK
SELECT * 
FROM ToysInventory WITH (NOLOCK)
WHERE ToyID = 1;

Explanation of Behavior

Without WITH (NOLOCK): The query in Session 2 will block until Session 1 either commits or rolls back the transaction because the default isolation level (READ COMMITTED) prevents dirty reads.
With WITH (NOLOCK): The query in Session 2 will immediately return the uncommitted data (dirty read) from Session 1. This demonstrates how WITH (NOLOCK) allows you to bypass the locking mechanism and read data that might not yet be committed.

Cleanup: Commit or Roll Back the Transaction

In Session 1, execute either:

-- Commit the transaction
COMMIT;

Or:

-- Rollback the transaction
ROLLBACK;

Points to Highlight in the Demo

Dirty Reads: When using WITH (NOLOCK), uncommitted changes in Session 1 will be visible in Session 2.
Performance Tradeoff: WITH (NOLOCK) avoids blocking but risks reading incomplete or invalid data.
Usage Scenarios: Useful for reporting queries where perfect accuracy isn’t critical and you prioritize speed.

Additional Resources

If you’re interested in ongoing monitoring and troubleshooting SQL Server performance, check out the Database Health Monitor, a free tool that helps identify locking and blocking issues effectively. We can also help with our performance tuning with our SQL Consulting Services. We are happy to help, see why Stedman Solutions are the experts.