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.
| Author |
Topic |
|
dewacorp.alliances
452 Posts |
 Posted - 2007-09-18 : 17:47:49
|
| I'm quite interesting what people comment about that topic which is below: The statement on BOL ("Isn't NOLOCK a default behaviour of SELECT according to BOL?") is a bit misleading. If you run a select - it runs under NOLOCK, in other words, data that you read, doesn't appear as locked to *other* selects. Whereas that is not the case in INSERT/UPDATE/DELETE. BUT when someone has locked any data using IUD commands, then you can get around that by forcing ReadUncommitted isolation level by adding NOLOCK to your selects. So my question is:1) What does it mean by "IUD commands".2) So I guess the NOLOCK is necessary for doing select both table and view, correct? How about if you are doing LINKED database?I'm appreciated your comment.Thanks |
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2007-09-18 : 17:54:17
|
| >>>1) What does it mean by "IUD commands".I am guessing - Insert/ update / Delete commands.>>2) So I guess the NOLOCK is necessary for doing select both table and view, correct? How about if you are doing LINKED database?It doesnt matter whether its linked server or not. Not sure what you mean by "NOLOCK is necessary for doing select..". By default, your data is Read Committed. So If someone is doing an I/U/D, you will see the latest changed data. If you dont care about the latest changes, you can use NOLOCK when doing a SELECT and get the pre-commited version of the data.So, NOLOCK is optional. You use it if you want to do "dirty read"s.Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
 |
|
|
dewacorp.alliances
452 Posts |
Posted - 2007-09-18 : 18:01:46
|
| If you use using the query for Reporting purpose which it doesn't matter whether or not someone else makes a change to a record or two during that query is running. In this schenario, I guess it's a good idea to use (NOLOCK).Thanks |
|
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2007-09-18 : 18:13:04
|
quote:
Originally posted by valdyv
If you use using the query for Reporting purpose which it doesn't matter whether or not someone else makes a change to a record or two during that query is running. In this schenario, I guess it's a good idea to use (NOLOCK).Thanks
Perhaps. If your reporting doesnt need THE latest data then yes you can benefit from the slight performance gain by using NOLOCK.Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2007-09-18 : 22:45:15
|
quote:
Originally posted by valdyv
If you use using the query for Reporting purpose which it doesn't matter whether or not someone else makes a change to a record or two during that query is running. In this schenario, I guess it's a good idea to use (NOLOCK).Thanks
Using NOLOCK is rarely a good idea.You should use it only when you don't care if your result is correct.CODO ERGO SUM |
|
|
|
dewacorp.alliances
452 Posts |
Posted - 2007-09-20 : 19:49:52
|
quote:
Using NOLOCK is rarely a good idea.You should use it only when you don't care if your result is correct.
Generally, I only use for snapshot reporting/listing BUT if the select is used by the application within series of queries (Insert/Update) .. I don't probably do that.BTW ... does this thing the same for VIEW though? Can I do this in VIEW?Thanks |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-09-20 : 20:32:27
|
| We never use NOLOCK in production queries, only in queries used for DBA admin stuff - e.g. to prevent long running queries blocking other people.Nigel posted that he changes the isolation level instead - i.e. getting the benefit of NOLOCK without having to remember to type it!Kristen |
|
|
|
|
|
|
|
|
Is NoLock necessary on select table/view