How does SQL READUNCOMMITTED behave on VIEWS ?

Question

I have a view which contains a join of multiple tables. If I use 'WITH (READUNCOMMITTED)' on the SELECT FROM View, will this propagate and apply to tables that are joined by the view or not ?

Answer 1

The isolation is only for the table that it applies to.

Consider the following. In one session, set up the table and view:

create table x (a int, b varchar(10))

create table y (c int, d varchar(10))

insert into x (a, b) values (1, 'Q')
insert into x (a, b) values (2, 'W')
insert into x (a, b) values (3, 'E')

insert into y (c, d) values (1, 'A')
insert into y (c, d) values (2, 'S')
insert into y (c, d) values (3, 'D')


create view v_test
as
select x.a, x.b, y.d
from x with (readuncommitted)
     inner join 
     y
     on x.a = y.c

Select from the view from session1:

select * from v_test

a       b       c
----    ----    ----
1       Q       A
2       W       S
3       E       D

Now open another session, and fire up a transaction, updating only x at first:

begin transaction

update x
set b = 'R'
where a = 1

Go back to session 1, and execute the view. You'll now get this:

a       b       d
----    ---     ---
1       R       A
2       W       S
3       E       D

Note the new value for b in the first row.

Go back to session 2, with the transaction still open, update y:

update y
set d = 'F'
where c = 1

and then try querying the view back in session 1:

select *
from v_test

(You'll find it seems to be taking a rather long time.)

With the query still executing, go back to session 2 and commit the transaction:

(Session2 looks like this now:

begin transaction

update x
set b = 'R'
where a = 1

update y
set d = 'F'
where c = 1

commit

)

Look back at session1 and the results will now appear with this:

a       b       d
---     ---     ---
1       R       F
2       W       S
3       E       D

with the new value for d in the first row.

So, long story a little longer, the WITH (READUNCOMMITTED) is not propagated.