Dirty Read on SQL Server

Posted on

Question :

  1. The SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED is at different part of the “BEGIN”. Is the below two query behave the same?

  2. 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

Leave a Reply

Your email address will not be published. Required fields are marked *