READ UNCOMMITTED changes session read isolation without adding NOLOCK to every table. A workshop procedure made the distinction useful.
-- Original WideWorldImporters demonstration; use a separate lab connection.
-- Table-hint form for the original two-table comparison:
SELECT ct.CityName, sp.StateProvinceName
FROM Application.Cities AS ct WITH (NOLOCK)
JOIN Application.StateProvinces AS sp WITH (NOLOCK)
ON ct.StateProvinceID = sp.StateProvinceID;
-- Session-level form:
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT ct.CityName, sp.StateProvinceName
FROM Application.Cities AS ct
JOIN Application.StateProvinces AS sp
ON ct.StateProvinceID = sp.StateProvinceID;
-- Restore the connection's intended isolation level before further work.
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;That procedure had more than 90 tables and 14 views. Editing each table temporarily was awkward. For ordinary reads, NOLOCK is equivalent to a READUNCOMMITTED table hint. A session setting has broader reach, while explicit hints and procedure behavior can affect statements.
Dirty reads can observe uncommitted data, miss rows or duplicate rows. Schema locks can still block them. They don’t rewrite stored data directly. Incorrect results can nevertheless drive incorrect decisions or downstream updates.
Use a separate test connection and restore its intended isolation level. READ COMMITTED assumes that was the previous level. That assumption doesn’t hold for every application. Diagnose the blocking chain and correctness requirements before choosing an isolation design.
Related reading
Dirty-read isolation is not a general blocking cure, it is a correctness tradeoff that needs an explicit decision.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
Discover more from SQL Authority with Pinal Dave
Subscribe to get the latest posts sent to your email.
12 Comments. Leave new
try with (nolock) with using cross apply or outer apply ??
means?
What permissions does the user running the query need for :
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
I don’t think there is a special permission needed. Are you getting any error when you run
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
Please help me to reproduce the error.
If both are same, then would prefer to use (NoLock) hint. Have flexibility to apply for the tables only which are fine to read dirty data.
Sure. Choice is yours.
What if SP don’t reach till last statement due to some error, will it affect other running SPs?
means? Can you give an example?
It doesn’t affect other SPs as its scope is for that session and command.
For SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED the control is with user. If user forgets and did a select & his session is active, we are not able to do truncate and reload as part of ETL process. Is there any way this can be set at DB or schema level by DBA?
Pinal, I’ve been following your website, I think since the late 90’s, maybe early 2000’s love the blog, keep up the great work!!
Question, shouldn’t there be a preface with Transaction Isolation Level read uncommitted? For example, if I have an EDW that has a nightly ETL batch starting at midnight, and as long as I don’t have end user read/running reports until after the nightly batch is finished then theoretically I don’t ever have to worry about a dirty read?? Am I understanding the Isolation level correctly? Thanks!!!
I added SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED on the top of the stored procedure eg.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
GO
Create PROCEDURE [dbo].xxxx
as
begin
zzzzz
end
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
GO
I did on Friday but on Monday from all stored procedure Dirty Read line were missing whereas other changes were still as it is.