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)
2 Comments. Leave new
You shared this helpful information through video.. thanks for sharing!
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).