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)