Dirty Read with NOLOCK – SQL in Sixty Seconds #110

My primary job is that help various organizations to tune their SQL Server performance Comprehensive Database Performance Health Check. Recently I noticed that my client has been using the WITH NOLOCK hint with their queries and expecting that it will improve the queries performance. Honestly, nolock is never the solution to performance problems. While it may seem that it solves the problem from the surface, most of the time the relief is temporary and rather creates bigger problems in the future.

Today we are going to see a very short video where I am going to discuss how Nolock can do dirty read and which can be dangerous for the future.

As you have seen in the video above, no lock hint can read the data which does not exist or maybe skip the data which was supposed to include in the result. If you enter the data which is retrieved by the query to another table, you potentially create a problem of inserting the data which does exist at all. This can create database consistency issues in the future.

Here is the script used in the video:

Script 1:

USE SQLAuthority
GO
CREATE TABLE Toys
	([Name] VARCHAR(100),
	Price INT)
GO
INSERT INTO Toys ([Name], Price)
VALUES ('Car',99),('Bird',100),('Bike',100)
GO
SELECT *
FROM Toys
GO
-- Transaction
BEGIN TRANSACTION
-- First Update
UPDATE Toys
SET Price = Price+1
WHERE [Name] = 'Car';
-- Wait
WAITFOR DELAY '000:00:10'
-- Another Update
UPDATE Toys
SET Price = Price+1
WHERE [Name] = 'Car';
-- Over
COMMIT
-- Clean up
DROP TABLE Toys
GO

Script 2:

USE SQLAuthority
GO
SELECT *
FROM Toys
WHERE Price = 100
GO
SELECT *
FROM Toys WITH (NOLOCK)
WHERE Price = 100
GO

If SQL Server has locked the data, there must be the reason for the locking. Without properly investigating the reason for the lock, just using no lock hint on the query is not a good idea. I often like to follow the systematic and scientific approach to isolate the performance bottleneck and remove it completely. You can read more about my methodology over here.

If you like this blog post and video, I suggest you subscribe to my YouTube Channel where I regularly post more such videos. If you want me to create a video on any topic, just leave a comment and I will be happy to add that idea to my list.

Reference: Pinal Dave (https://blog.sqlauthority.com)