update view using WITH (NOLOCK) ? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

update view using WITH (NOLOCK) ?

Author

Topic

CorpDirect
Yak Posting Veteran

92 Posts

Posted - 2006-03-20 : 15:25:54

Can anyone tell me what would happen in terms of locking if an update was done against a view that uses the table hint WITH (NOLOCK) on one of its source tables? In this case update statements will only update tables that do not specify WITH (NOLOCK) in the view definition.

An example setup:

-- view definition
CREATE VIEW test_view
AS
SELECT one = a.col1, two = a.col2, three = b.col3, four = b.col4
FROM shared1 a WITH (NOLOCK), dedicated1 b
WHERE a.col1 = b.col1
AND a.col9 = b.col7

-- update record
UPDATE test_view
SET four = 'someval'
WHERE one = 'someid'
AND three = 'someotherval'

That illustrates the scenario. Here's the real-world application.

We use an Access app that uses SQL Server views as data sources for forms. These are simply defined as linked tables in Access. Users enter or modify data in fields on these forms. Since Access likes to open and lock entire tables, we are experiencing some locking issues with other applications that use the shared source tables.

Each view selects from one or more shared tables, and one or more tables dedicated to this application. Changes are permitted on the forms only to fields mapped back to columns in the app's dedicated tables. Access just sees the view as a table, though, and locks everything behind it.

If I modify the views in SQL Server to add the table hint WITH (NOLOCK) to the shared tables -- which will never be updated through Access -- what will happen in terms of locking when Access attempts to lock the form's datasource (view) for update? Will that override the hint and lock the tables anyway?

I'm willing to experiment if someone can tell me how to go about the investigation. Before I do, could I cause myself any other problems by attempting this?

Versions: SQL Server 2000, Access 2000

Thank you,

Daniel

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-03-20 : 15:58:58

with (nolock) locking hint only applies to select commands so it has no impact on your update statements. Concurrent requests that are SELECTing from this view should still be performing "dirty" reads.

EDIT:
>>I'm willing to experiment if someone can tell me how to go about the investigation.

Here is what I did to check it out:


set nocount on
use pubs

create table junk1  (junk1id int primary key)
create table junk11 (junk1id int primary key)

go
create table junk2  (junk1id int references junk1(junk1id), i int, v varchar(1), primary key (junk1id, i))
create table junk22 (junk1id int references junk1(junk1id), i int, v varchar(1), primary key (junk1id, i))
go

create view junk_vNoLock as

select	j1.junk1id, j2.i, j2.v
from	junk1 j1
join	junk2 j2 with (nolock)
		on j2.junk1id = j1.junk1id

go
create view junk_v2 as

select	j1.junk1id, j2.i, j2.v
from	junk11 j1
join	junk22 j2
		on j2.junk1id = j1.junk1id


go
insert junk1 values (1)
insert junk2 values (1,1,'a')
insert junk2 values (1,2,'a')

insert junk11 values (1)
insert junk22 values (1,1,'a')
insert junk22 values (1,2,'a')


/*
-----------In another query window-------------------
--run this (without the rollback to lock the tables)

begin tran 
	update junk_vNoLock set v = 'b' where junk1id = 1 and i = 2
	update junk_v2 set v = 'b' where junk1id = 1 and i = 2

--rollback
*/

--this statement returns (not blocked)
select * from junk_vNoLock

--this statement is blocked
select * from junk_v2


--Now rollback the transaction in the other window


go
drop view junk_vNoLock
drop view junk_v2
go
drop table junk2
drop table junk22
go
drop table junk1
drop table junk11

Be One with the Optimizer
TG

CorpDirect
Yak Posting Veteran

92 Posts

Posted - 2006-03-28 : 20:14:34

Thanks for the test setup, TG. I was also able to test and determine what happens when you use a view as a data source for an Access application and begin updating a record.

As you suggested, Access will "reach through" and lock all the tables that are part of the view, including those for which (NOLOCK) is specified. As you said, WITH (NOLOCK) applies only to reads.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources