Dirty Read with NOLOCK – SQL in Sixty Seconds #110

Dirty Read with NOLOCK - SQL in Sixty Seconds #110 110-Nolock-NoGood-DirtyRead-yt-800x450 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)

SQL in Sixty Seconds, SQL Performance, SQL Scripts, SQL Server, Transaction Isolation
Previous Post
Undo Human Errors in SQL Server – SQL in Sixty Seconds #109 – Point in Time Restore
Next Post
Single Column Single Row and TABLE SCAN – SQL in Sixty Seconds #111

Related Posts

2 Comments. Leave new

  • You shared this helpful information through video.. thanks for sharing!

    Reply
  • Hi Pinal Dave!

    We are using Microsoft SQL Azure (RTM) – 12.0.2000.8 and a developer wants to put NOLOCK on all the tables in all the current stored procedures we use for reporting. Would this be a valid case to use NOLOCK in order to get some faster processing of our SPs??

    The developer’s comment: “Disadvantages: Data consistency Issues – , as it allows reading uncommitted data (dirty reads) in some cases.

    ” – , but in our database the data in the dbo schemas (Fact and Dim tables) are static. The ETL updates the data in the staging schema then there is a schema swap that happens at the end from staging to dbo. Thus the FACT and DIM tables in our database is static. So the disadvantages of using WITH(NOLOCK) does not apply to it as there will not be uncommitted data, unless someone is doing a manual update on the data you are looking at currently (that they are not allowed to do anyways).

    Reply

Leave a Reply