| Author |
Topic |
|
everurssantosh
Starting Member
9 Posts |
 Posted - 2008-02-25 : 16:50:11
|
| When the NOLOCK hint is included in a SELECT statement, no locks are taken when data is read. This gives a Dirty Read in a result set. The data retrieved in the select statement may not be correct since some other process might have updated the data while it was taken in the select statement.Now, Can I know what is the use of NOLOCK? In which way we can make efficient use of NOLOCK ??? |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-02-25 : 16:53:33
|
| If you want to get good performance avoiding locks. |
 |
|
|
everurssantosh
Starting Member
9 Posts |
Posted - 2008-02-25 : 16:54:58
|
| Good performance is ok, but while using NOLOCK, we may get bad data too.. this was my concern |
|
|
|
sakets_2000
Master Smack Fu Yak Hacker
1472 Posts |
Posted - 2008-02-25 : 17:05:24
|
| I use it to get count of rows.. bad and uncommitted data isn't a problem then |
|
|
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2008-02-25 : 19:25:49
|
| Check out the multi-row versioning features of 2005. It gets around all this by doing concurrency without locks. Queries on locked data will give you slightly different data than when locks are used but it is consistent, committed data. (It gives you the results as-at the start of your query rather than at the time all rows locked due to transactions have completed or rolled back).Personally I think it is a much better strategy, but then I have an Oracle background and this is how they do it. |
|
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2008-02-25 : 19:48:04
|
| You should not use NOLOCK in a production environment; I have never used it myself in a production application.It really violates the basic principle of transactional integrity, so you can be looking at data is in an inconsistent state, and produce problems that are almost impossible to debug.About the only time I use it is to look at the results of ad-hoc inserts, updates, or deletes before I commit the data.CODO ERGO SUM |
|
|
|
mahesh_bote
Constraint Violating Yak Guru
298 Posts |
Posted - 2008-02-26 : 00:41:41
|
| Using or Not Using NOLOCK depends on the domain of company. e.g. if company is working on Banking, Stock Exchange like domain, they should not use NOLOCK.thanks,Mahesh |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2008-02-26 : 09:33:55
|
quote:
Originally posted by mahesh_bote
Using or Not Using NOLOCK depends on the domain of company. e.g. if company is working on Banking, Stock Exchange like domain, they should not use NOLOCK.thanks,Mahesh
It’s a dangerous blanket assumption to think that clean, consistent data is only important in certain industries. I would go with the assumption that it is important to everyone.CODO ERGO SUM |
|
|
|
|
NOLOCK Clarifications