| Author |
Topic |
|
DBCowboy
Starting Member
26 Posts |
 Posted - 2003-05-05 : 16:59:10
|
| One of the developers created an often-run query at my company, and it raised an issue that I haven't been able to find a clear answer on. The query is based on a view, and selects with a NOLOCK hint from the view, e.g.:SELECT item1, item2 FROM tableview1 (NOLOCK)The view queries several other tables to generate a list of values, but the view's query has no hints on it.What I'm wondering is if the NOLOCK hint translates down to the underlying tables of the view? I'm trying it now to see if there's any change, but if anyone else has run into this, I'd appreciate hearing about your experiences.Jaysen |
|
|
simondeutsch
Aged Yak Warrior
547 Posts |
Posted - 2003-05-05 : 17:22:50
|
| The NOLOCK hint, of course, only affects SELECT statements. What it affects is that you can have dirty reads.You can try it by simultaneously updating one or more of the underlying tables, selecting from the view, and rolling back the updates. See if the values in your query from the view have the updated or original values.Sarah Berger MCSD |
 |
|
|
DBCowboy
Starting Member
26 Posts |
Posted - 2003-05-05 : 17:34:03
|
| I don't see how your reply fits what I'm asking. Maybe I wasn't clear.I know how NOLOCK hints work in general. I'm not updating based on the view, I'm selecting based on the view.The select statement upon the view has a NOLOCK hint. What I want to know is if this translates to a NOLOCK on every table that the view references.In other words, does:SELECT * from view1 (NOLOCK)translate to:SELECT blah from table1 a (NOLOCK)INNER JOIN table2 b (NOLOCK) on a.field1 = b.field1If the view is:CREATE VIEW view1ASSELECT blah from table1 a INNER JOIN table2 b on a.field1 = b.field1Jaysen |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-05-05 : 18:11:39
|
| Easy to test for yourself.in one query windowbegin transelect * from table1 (tablockx)) where 1 = 0in another select from the view and see if it gets blocked.(remember to kill the transaction).==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-05-05 : 18:27:38
|
| My educated guess would be that the NOLOCK hint does NOT apply to a NOLOCK hint on every table that the view references. I would bet that the NOLOCK hint would have to be in the DDL of the view in order for that to be true. [EDIT] I guess I'm wrong then (see jaser_smith's post). Oh well. [/EDIT]But as nr suggested, you can test it out just to make sure.HTH,TaraEdited by - tduggan on 05/05/2003 18:38:29 |
|
|
|
jasper_smith
SQL Server MVP & SQLTeam MVY
846 Posts |
Posted - 2003-05-05 : 18:33:15
|
The nolock hint on a select from a view is equivalent to setting READ UNCOMMITTED for the underlying select so the only locks taken out are Schema Stability locks, this applies to all tables and views in the selected from views definition. If you join a select on a view/table with nolock with another select on another table or view, the nolock hint only applies to the view/tables it is applied to and does not affect any other tables in the top level select.Hope this makes sense  - oh and the answer is Yes.HTHJasper Smith0x73656c6563742027546f6f206d7563682074696d65206f6e20796f75722068616e6473203f27 |
|
|
|
DBCowboy
Starting Member
26 Posts |
Posted - 2003-05-05 : 18:46:37
|
| Great, thanks. That was pretty much the behavior I was seeing, but I wanted to be more sure than just running a few tests on my own.That puts the problem back on the developer who is calling an update statement along with the select every 3 seconds against DBA request. :)Jaysen |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-05-06 : 09:28:28
|
quote:
Great, thanks. That was pretty much the behavior I was seeing, but I wanted to be more sure than just running a few tests on my own.
Why wouldn't you have done that first?esp when:quote:
The NOLOCK hint, of course, only affects SELECT statements. What it affects is that you can have dirty reads. You can try it by simultaneously updating one or more of the underlying tables, selecting from the view, and rolling back the updates. See if the values in your query from the view have the updated or original values. Sarah Berger MCSD I don't see how your reply fits what I'm asking. Maybe I wasn't clear.
Which I think is what Jasper is refering to in:quote:
The nolock hint on a select from a view is equivalent to setting READ UNCOMMITTED for the underlying select so the only locks taken out are Schema Stability locks, this applies to all tables and views in the selected from views definition.
So.....?Que?Brett8-) |
|
|
|
DBCowboy
Starting Member
26 Posts |
Posted - 2003-05-06 : 11:13:54
|
| I did my own testing, as I stated, and made changes regardless of the answer. Note the "I'm trying it now to see if there's any change" in the original post. I didn't feel that my testing granted a completely clear answer. As I stated, I wanted to see what other people knew about the subject. There are some behaviors with locking that I have seen are different from installation to installation, particularly with lock escalation, and locks that apply differently in different situations. Haven't you ever applied a WITH (ROWLOCK) to an update query and had it change it to a pagelock regardless of the hint?When something isn't in BOL, I like to get a variety of feedback.Jaysen |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-05-06 : 11:20:52
|
| Nope. I prefer to leave settings alone. Mostly because once you start messing with them, you have to Remeber what you did.I hate to have to remeber things...[cpu_100%]My brain froze[/cpu_100%]Wonder what the general concesus is though.Brett8-) |
|
|
|
|
NOLOCK on view