Using the SELECT statement with an ongoing INSERT or UPDATE statement, put an exclusive lock on rows or possibly on the whole table until the operation’s transaction is committed or rolled back. Suppose, you are working on a very big table with thousands of rows and the database table is not efficiently designed. Remember, in real life, you are not the only one working with a database, that database might be getting accessed on many users’ devices at the same time. So, if you are reading the table using the SELECT statement and someone else is trying to execute the INSERT statement, then a lock can occur and the two transactions block each other.
So the solution in SQL Server is that the NOLOCK table hint allows you to instruct the query optimizer to read a given table without obtaining an exclusive or shared lock.
Using NOLOCK
Step 1: Creating the Database Using the Name with Database
Query
CREATE DATABASE GFG_Demo;
USE GFG_Demo;
Output
GFG_Demo
Step 2: Table Definition
We have the following demo table in our GFG_Demo database.
Query
CREATE Table GFG_Demo_Table
( Order_date date, Sales int);
Output
GFG_Demo_Table
Step 3: Adding data to the table
Use the below statement to add data to the GFG_Demo_Table.
Query
INSERT INTO GFG_Demo_Table(Order_date,Sales)
VALUES('2021-01-01',20),('2021-03-02',32),('2021-02-03',45),
('2021-01-04',31),('2021-03-05',33),('2021-01-06',19),
('2021-01-01',20),('2021-03-02',32),
('2021-02-03',45),('2021-01-01',20),('2021-03-02',32),
('2021-02-03',45), ('2021-01-04',31),('2021-03-05',33),
('2021-01-06',19), ('2021-01-04',31),('2021-03-05',33),
('2021-01-06',19), ('2021-01-01',20),('2021-03-02',32),
('2021-02-03',45), ('2021-01-04',31),('2021-03-05',33),
('2021-01-06',19), ('2021-01-04',31),('2021-03-05',33),
('2021-01-06',19), ('2021-04-07',21),('2021-03-08',10),
('2021-02-09',40), ('2021-03-10',20),('2021-03-11',26),
('2021-04-12',22), ('2021-04-13',10),('2021-01-14',28),
('2021-03-15',15), ('2021-01-16',12),('2021-04-17',10),
('2021-02-18',18), ('2021-04-19',14),('2021-01-20',16),
('2021-02-21',12), ('2021-03-22',51),('2021-02-23',13),
('2021-03-24',15), ('2021-02-25',30),('2021-03-26',14),
('2021-04-27',16), ('2021-02-25',30),('2021-03-26',14),
('2021-04-27',16), ('2021-02-25',30),('2021-03-26',14),
('2021-04-27',16), ('2021-02-25',30),('2021-03-26',14),
('2021-04-27',16), ('2021-03-22',51),('2021-02-23',13),
('2021-03-24',15), ('2021-03-22',51),('2021-02-23',13),
('2021-03-24',15), ('2021-02-25',30),('2021-03-26',14),
('2021-04-27',16), ('2021-03-22',51),('2021-02-23',13),
('2021-03-24',15), ('2021-02-25',30),('2021-03-26',14),
('2021-04-27',16), ('2021-03-22',51),('2021-02-23',13),
('2021-03-24',15), ('2021-03-22',51),('2021-02-23',13),
('2021-03-24',15), ('2021-03-22',51),('2021-02-23',13),
('2021-03-24',15), ('2021-02-25',30),('2021-03-26',14),
('2021-04-27',16), ('2021-02-25',30),('2021-03-26',14),
('2021-04-27',16), ('2021-02-25',30),('2021-03-26',14),
('2021-04-27',16), ('2021-02-25',30),('2021-03-26',14),
('2021-04-27',16), ('2021-02-25',30),('2021-03-26',14),
('2021-04-27',16), ('2021-02-25',30),('2021-03-26',14),
('2021-04-27',16), ('2021-04-19',14),('2021-01-20',16),
('2021-02-21',12), ('2021-04-19',14),('2021-01-20',16),
('2021-02-21',12), ('2021-04-19',14),('2021-01-20',16),
('2021-02-21',12), ('2021-04-19',14),('2021-01-20',16),
('2021-02-21',12), ('2021-04-19',14),('2021-01-20',16),
('2021-02-21',12), ('2021-04-19',14),('2021-01-20',16),
('2021-02-21',12), ('2021-04-19',14),('2021-01-20',16),
('2021-02-21',12), ('2021-04-19',14),('2021-01-20',16),
('2021-02-21',12), ('2021-04-19',14),('2021-01-20',16),
('2021-02-21',12), ('2021-02-28',15),('2021-01-29',20),
('2021-01-30',18);
Output
output
Step 4: Using NOLOCK
Now that our table is ready, let’s do a test and see how NOLOCK is useful.
Query
BEGIN TRAN UPDATE GFG_Demo_Table
SET Sales= 25 WHERE Sales=20;
Step 5: The BEGIN TRAN statement will start the transaction that will start the transaction that will run the following UPDATE statement on the GFG_Demo_Table table under SQL session number 52 (it could be different in our system), without finishing the transaction by committing or rolling it back.
Query
SELECT * FROM GFG_Demo_Table;
Step 6: When you execute this query, you may notice that the SELECT statement is taking slightly longer time than usual. The difference is more noticeable in bigger tables. Try this, with thousands of rows. It is because the transaction is still not committed or rolled back. That is why it is blocking other queries that are trying to read the data from the table. You can check that using the following command.
Query
sp_who2 52
Note: For this particular table, the SELECT statement is not blocked for a long time as the table is not that big, but in the case of a table with thousands of data, you can see the session number of the query that is blocking the SELECT statement by running the above command.
Step 7: To run the blocked query, you have to kill or commit or roll back the transaction. However, this is not the most preferable solution. This is where the WITH (NOLOCK) comes into the picture.
Query
SELECT * FROM GFG_Demo_Table WITH (NOLOCK);
Now, run the above query and then check the status of the SELECT statement using the previous command.
Output
output
Step 8: The BlkBy column is blank, which means the lock is removed now. You can also use WITH (READUNCOMMITTED), it does the same thing as WITH (NOLOCK). It can also read the uncommitted data, without waiting for the UPDATE statement to release the lock.
Output
output
Note: You can only use these table hints with the SELECT statement, not with any other statement.
Using Set Transaction Isolation Level
Rather than using those table hints that allow dirty reads at the query level, you can change the transaction isolation level at the connection level to be READ UNCOMMITTED using the SET TRANSACTION ISOLATION LEVEL statement.
Query
SET TRANSACTION ISOLATION LEVEL READ
UNCOMMITTED;
SELECT * FROM GFG_Demo_Table;
Output
output
This query will also retrieve the same data directly, without using any table hint and without waiting for the UPDATE statement to release the lock it performed on the table.
Drawbacks of using NOLOCK
- Using NOLOCK might seem a good idea at first as we get the requested data faster without waiting for the other operation to be committed. However, dirty read is a huge problem, the result that we get after that, may not be accurate. So automatically adding NOLOCK hints is generally not a good practice in an Online Transaction Processing (OLTP) environment.
- Adding the NOLOCK hint or changing the isolation level does change how either locking is handled for either an individual query or for all commands in the session. Test these changes thoroughly to check if they meet your needs.
- If you are performing aggregate functions like SUM(), AVG(), MAX(), MIN(), etc without lock then the result is inconsistent due to ongoing updates by other transactions.
Last Updated :
19 Sep, 2023
Like Article
Save Article
Share your thoughts in the comments
Please Login to comment...