find and solve || findandsolve.com
Please wait.....

Understanding the SQL Server table hints – WITH (NOLOCK)

In MS SQL, the NOLOCK hint allows SQL to read data from tables by ignoring all any locks so therefore not get blocked by another process. This query keyword improves performance by removing the blocks but introduces the possibility of dirty reads.


MS SQL Server table hints are a special type of explicit command that is used to override the default behavior of the SQL Server query optimizer during the T-SQL query execution This is accomplished by enforcing a specific locking method, a specific index, or query processing operation, such index seeks or table scan, to be used by the SQL Server query optimizer to build the query execution plan. The table hints can be added to the FROM clause of the T-SQL query, affecting the table or the view that is referenced in the FROM clause only.


Example of SQL Server NOLOCK


I am going to create a new table EmployeeInfo and insert data in this table.

Create a table script in ms SQL server

CREATE TABLE dbo.EmployeeInfo
(
 EmployeeId INT PRIMARY KEY IDENTITY(1,1),
 FirstName VARCHAR(100),
 LastName VARCHAR(100),
);


Insert data in created EmployeeInfo table 

INSERT INTO dbo.EmployeeInfo VALUES('Sylvia','Neupane')

INSERT INTO dbo.EmployeeInfo VALUES('Rahul','Sharma')
INSERT INTO dbo.EmployeeInfo VALUES('Ubina','Thapa')
INSERT INTO dbo.EmployeeInfo VALUES('Debin','Bhattarai')
INSERT INTO dbo.EmployeeInfo VALUES('Ganesh', 'Khadka')
INSERT INTO dbo.EmployeeInfo VALUES('Rahul','Thapa')
INSERT INTO dbo.EmployeeInfo VALUES('Solina','Jharada')
INSERT INTO dbo.EmployeeInfo VALUES('Mekani','Laula')
INSERT INTO dbo.EmployeeInfo VALUES('Phadindra','Sewaro')
INSERT INTO dbo.EmployeeInfo VALUES('Silpu','Pokharel')
INSERT INTO dbo.EmployeeInfo VALUES('Baniya', 'Bhat')
INSERT INTO dbo.EmployeeInfo VALUES('Sokya', 'Lohoni')
INSERT INTO dbo.EmployeeInfo VALUES('Serang','jamkeri')


Here is a query that returns all of the data from the EmployeeInfo table. If I run this query you can see there is only one record that has a Suffix value for EmployoeeId= 3.



So the issue with using the NOLOCK hint is that there is the possibility of reading data that has been changed but not yet committed to the database. If we are running reports and do not care if the data might be off then this is not an issue, but if we are creating transactions where the data needs to be in a consistent state we can see how the NOLOCK hint could return false data.


Types of SQL Server Locks Used with NOLOCK


If you run SELECT  statement without NOLOCK you can see the locks that are taken if you use sp_lock. (To get the lock information I ran sp_lock in another query window while this was running.)


If you do the same for our SELECT with the NOLOCK  you can see these locks.

SELECT * FROM EmployeeInfo WITH (NOLOCK) WHERE EmployeeId < 10 









































Mahira  khanna

Mahira khanna

I have the skills you need for you company blog, website, or other content materials

If findandsolve.com felt valuable to you, feel free to share it.

Comments



Report Response