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:
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
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)