How does SQL Server With READUNCOMMITTED behave on VIEWS?

I have a view which co开发者_如何学JAVAntains 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 ?

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.

I think this was answered, to the contrary, here: "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED" and views

But they could have been wrong, I haven't done your steps yet Patrick, if you're correct then you should inform the folks on that thread!

If you run the example from Patrick with readuncommitted on both tables you will see that it will work even when the transaction is in progress. An you can also run the example without the with readuncommitted inside the view but pass with readuncommitted to the select from the view and it will propagate down to the select in the view if passed to the view. ex.

select *
from v_test with (readuncommitted)