NOLOCK Hint & READ UNCOMMITTED Isolation level on table and Query/Session level - SQL Server by serverku

When we created a new database, it will be created with default isolation level and that is "READ COMMITTED". If some update transactions are running in with table rows under READ COMMITTED isolation level, How can we get data from a table in another session while running update transaction?

How can ?
NOLOCK hint or READ UNCOMMITTED isolation level help for the same as there are operating same. We have some other options other than this. But I am going to present the NOLOCK hint and READ UNCOMMITTED isolation level here.

For NOLOCK, we need to put this hint on table level, so it is required to put for every table level which are used in update transaction. So it is very lengthy and time consuming to put it everywhere, tables refers in the query. For READ UNCOMMITTED, We do not need to put it every tables level, just put at session level or query level and can be written on top of the query or stored procedure. Let us look on small demo to elaborate it. First checking here database default isolation level,

USE DEMO
GO
DBCC USEROPTIONS

Starting with creating a database and table objects.

IF (OBJECT_ID('TrnTable','U') > 0)
DROP TABLE TrnTable

CREATE TABLE TrnTable
(
TrnId INT ,
TrnData VARCHAR(100),
TrnDate DATETIME
)

GO

-- Inserting some sample records in table

INSERT INTO TrnTable(TrnId,TrnData,TrnDate)
SELECT 1,'TrnData-1',GETDATE()
UNION ALL
SELECT 2,'TrnData-2',GETDATE()
UNION ALL
SELECT 3,'TrnData-3',GETDATE()
UNION ALL
SELECT 4,'TrnData-4',GETDATE()
UNION ALL
SELECT 5,'TrnData-5',GETDATE()

GO

Now for the demo we will run the below script with session 1,

-- Script in session 1
-- Running query with transaction named TRAN1
BEGIN TRANSACTION TRAN1

UPDATE TrnTable
SET TrnData = 'Changed TrnData'
WHERE TrnId = 3
-- Not Committed/Rollback this transaction

After that we will get the same rows which are updated in above session, which are not committed yet in another session. It will be going on waiting to release the lock held by session 1,

We are not closing this transaction here, and created a new session and run following scripts having a NOLOCK hint on table level and READ UNCOMMITTED isolation level on query level.

-- Script in session 3
-- With NOLOCK hint
SELECT 
TrnId,
TrnData,
TrnDate
FROM TrnTable (NOLOCK)
WHERE TrnId = 3

GO

-- With READ UNCOMMITTED isolation level 
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED 

SELECT 
TrnId,
TrnData,
TrnDate
FROM TrnTable
WHERE TrnId = 3

GO

Do not forget to commit or rollback transaction TRAN1,
Commit Transaction TRAN1

I hope you liked this post. Please let me know what you are using among them or else something?