Open In App

How to Use SELECT Without Locking a Table?

Improve
Improve
Like Article
Like
Save
Share
Report

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

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

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

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

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

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

SQL47-(1)

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
Previous
Next
Share your thoughts in the comments
Similar Reads