| Author |
Topic |
|
SamC
White Water Yakist
3467 Posts |
 Posted - 2003-02-25 : 23:05:03
|
| Would someone describe the benefits (performance?) of using WITH(NOLOCK)?I've got some long running querys that update and select from a table, with several processes competing to add rows. I don't care if rows are added during the select or update, so it may make sense not to lock the table.Will my SELECT query run slower or faster if I use WITH(NOLOCK), and will it be a benefit to the processes that are inserting rows?Sam |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-02-26 : 00:09:48
|
| The select will run faster as it won't have to gain locks ot wait for locks to be released.The insert processes may benefit as they won't have to wait for the shared locks to be released.Downside is that the select may get invalid data.I suggest that it should never be used if you are relying oin the result.I only use it for ad hoc queries or seeing how far processes have got.==========================================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. |
 |
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2003-02-26 : 00:18:16
|
| Thanks Nigel, I'll give it a try.Here's another point. This is actually a table that tracks users viewing web pages. There's a userid and a URL in every row.This table keeps getting elements added by many users while queries are generally retrieving history on behalf of a specific user (like what was the last page I visited?). There's no danger of a query occuring on behalf of a user while the same user was adding a row. Even if this did happen, it isn't a critical situation.It seems to me that not only the SELECT and UPDATE are safe, but I could INSERT WITH(NOLOCK) without getting into trouble?Sam |
|
|
|
Bambola
Posting Yak Master
103 Posts |
Posted - 2003-02-26 : 04:06:42
|
quote:
It seems to me that not only the SELECT and UPDATE are safe, but I could INSERT WITH(NOLOCK) without getting into trouble?Sam
You cannot INSERT without Locking. Same goes for UPDATE. You could however specify other lock hints (ROWLOCK, UPDLOCK etc). Check BOL for locking hints.Bambola. |
|
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2003-02-26 : 09:34:36
|
| I've read on about locking a little by doing a search on sqlteam's home page. This is a very interesting topic.[url]http://www.4guysfromrolla.com/webtech/101100-1.shtml[/url]Any other references on this topic would be appreciated.Sam |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-02-26 : 09:43:56
|
| As Bambola says nolock will only affect selects.Be cautious, in the past I have had completely nonsensical data returned - seemed to be due to invalid page links. This hasn't happenned for a while though so maybe it's not possible now.Have you condidered updating the database structure instead?How up to date do these selects have to be?Could replicate to another database for the selects (differential restore?) or create aggragate tables.==========================================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. |
|
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2003-02-26 : 11:05:52
|
| Any thoughts on when using UPDATE MyTable WITH (UPDLOCK)is a burden? The only guidance I have is "not when too many rows are locked". What is too many rows?Here's a related point: in Enterprise Manager, Management, Current Activities:Several locks displayed there seem persistent. Seems to me locks should come and go. Is this a problem?Sam |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-02-26 : 12:56:37
|
| This will take update locks instead of shared locks.Not sure it would have any effect for an update - maybe for the reows read but not updated.These locks will be held until te transaction is completed whereas without the updlock the shared locks would be released immediately.==========================================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. |
|
|
|
|
WITH(NOLOCK)