Question :
-
The SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED is at different part of the “BEGIN”. Is the below two query behave the same?
-
Will the views, viewOne and viewTwo, inherit the dirty read? Or the view needs to have an explicit “NOLOCK”
Query 1
set transaction isolation level read uncommitted
begin
select col1 from viewOne
select * from viewTwo
select * from table1
end
Query 2
begin
set transaction isolation level read uncommitted
select col1 from viewOne
select * from viewTwo
select * from table1
end
Answer :
From this documentation – SET TRANSACTION ISOLATION LEVEL – under REMARKS
Only one of the isolation level options can be set at a time, and it
remains set for that connection until it is explicitly changed. All
read operations performed within the transaction operate under the
rules for the specified isolation level unless a table hint in the
FROM clause of a statement specifies different locking or versioning
behavior for a table.
You can use this query to determine the isolation level you are currently at:
SELECT CASE transaction_isolation_level
WHEN 0 THEN 'Unspecified'
WHEN 1 THEN 'ReadUncommitted'
WHEN 2 THEN 'ReadCommitted'
WHEN 3 THEN 'Repeatable'
WHEN 4 THEN 'Serializable'
WHEN 5 THEN 'Snapshot' END AS TRANSACTION_ISOLATION_LEVEL
FROM sys.dm_exec_sessions
where session_id = @@SPID