1
  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

1 Answer 1

5

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
2
  • Thanks Scott for your reply. So, my question is that will the tables inside the view inherit the "dirty read" option as well if "NOLOCK" is not added as my entire block of code is started with "set transaction isolation level read uncommitted". Or do I still need to explicitly say "NOLOCK" in the view.
    – Tommy
    Commented Jan 30, 2017 at 19:31
  • From the documentation that I linked to, that is my understanding. You should be able to 'test' that theory by beginning a transaction in another query window and apply some sort of update to one of the underlying tables in the view. Then, in another query window, see if you can select from the view after setting read uncommitted at the connection level. Don't forget to rollback the transaction that you began. Commented Jan 30, 2017 at 19:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.