NOLOCK with referenced view. - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

NOLOCK with referenced view.

Author

Topic

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2005-11-07 : 09:45:09

I have a view on a production sytemt that looks like this (simplified)

select	ColumnA,
	ColumnB,
	.
	.
	.
	ColumnN
from	TableName
	inner join ViewName on TableName.Key = ViewName.Key

ViewName is very complex, consisting of a dozen UNION sub-views. The problem is that runnning by view is creating blocking on a production server. My question is, if I use NOLOCK in my statement, will that cause ViewName to execute with NOLOCK as well? What is the scope of NOLOCK with regard to referenced views?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-11-07 : 10:14:54

current Isolation level will apply to underlying views.

So "select <cols> from myView with (nolock)" will be equivilant to executing the underlying code of the view definition with isolation level set to (read uncommitted).

EDIT:
this is the code I used to confirm my belief:


use northwind

create table junk (i int primary key nonclustered)
go
create view junk_v as select i from junk with (nolock)
go
insert junk (i) 
values (1)

begin tran
 insert junk (i) 
 values (2)


--In other window (this one will be blocked)
--select * from junk_v

--In other window (this one won't be blocked)
--select * from junk_v with (nolock)

rollback tran
go
drop view junk_v
go
drop table junk

Be One with the Optimizer
TG

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2005-11-07 : 11:00:05

Thanks TG. We will give NOLOCK a try on my code.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources