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 |
|
geossl
Yak Posting Veteran
85 Posts |
 Posted - 2004-05-13 : 22:30:20
|
Dear All, When using SELECT statement, is it good to use WITH NOLOCK such as : SELECT * FROM Dept (NOLOCK) WHERE Code = 'ABC' What is the impact and advantage? When it is used in an ADO Query, when I change it to insert mode and post a new record, is there any advantage there? Thanks. |
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-05-13 : 22:44:21
|
| WITH(NOLOCK) can really help to reduce contention in a system. The drawback to it is that if any data is changed during the SELECT that data change will not be reflected. If you can live with that in the application, and more importantly the applicable business rules, then you are fine and should use it. Just be careful that you don't violate your data rules by selecting off of it and making decisions if there is the possibility of it having changed.I work in the mortgage business where only one person is allowed to touch a loan at any given time; therefore, we are able to use it quite frequently. This has allowed us to significantly reduce contention and speed up the environment in places.When it is used in an ADO Query, when I change it to insert mode and post a new record, is there any advantage there?You need to write a stored procedure for your inserts, updates, and selects and call these. It's not generally recommended to use them on an INSERT.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
 |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-05-14 : 01:03:08
|
| Be careful about using nolock. It can cause the query to return invalid results.It can get contradictory values from tables if other spids are in the middle of updates - it can also get values that are nothing to do with the tables being queried (although I haven't seen that for a while).I would only use it for looking at production data while the system was running (and I always use it in this situation) and I didn't want to deadlock other spids. I'd never use it for anything where the user expects correct (or consistent) data.==========================================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. |
|
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2004-05-14 : 07:26:29
|
| I really hate nolock in production code. Most of the time it is a cop-out for poorly written transaction or processes. I've never met an end user (person, application or otherwise) that was satisfied with potentially incorrect data.Jay White{0} |
|
|
|
ravilobo
Master Smack Fu Yak Hacker
1184 Posts |
Posted - 2004-05-14 : 09:17:59
|
quote:
Originally posted by geossl
Dear All, When using SELECT statement, is it good to use WITH NOLOCK such as : SELECT * FROM Dept (NOLOCK) WHERE Code = 'ABC' What is the impact and advantage? When it is used in an ADO Query, when I change it to insert mode and post a new record, is there any advantage there? Thanks.
The concept is also known as DIRTY READ. Don't go for it if you need accurate data. At the same time this is a good option to SELECT data from huge tables with less affect on the server.NOLOCK doesn't respect any locks. The data returned will be a snap shot of that perticular moment. Recommened only for SELECTS.------------------------I think, therefore I am |
|
|
|
|
|
|
|
|
WITH NOLock good to use