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 |
|
ppsivya
Starting Member
2 Posts |
 Posted - 2008-05-12 : 20:34:50
|
| Hi,I was sreading about NOLOCK that it could prevent deadlocks but could return data which is not committed yet.1) Should we use NOLOCK with select statements2) If the transaction isolation level is set appropriately (e.g. Serializable)in the component (for e.g COM+ component) but NOLOCK is specified in the select then would it return uncommitted data. I mean if the transaction is controlled at hihger level then what will be the Pros and Cons of using NOLOCK.Thanks |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-05-12 : 22:56:31
|
| 1) It depends. Do dirty reads matter? They would to financial systems.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Database maintenance routines:http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx |
 |
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2008-05-13 : 03:44:12
|
| NOLOCK will cause your selects to read dirty data and I have prevoiusly said that I belive this is perfectly acceptable in over 80% of the queries I have worked with over the years, and I still belive this is true. It will reduce locking in your database and increase performance of your queries. HOWEVER, it is absolutely essential that you know how it works and you need to think well through where to use it and not. It could cause severe inconsistencies in your database if not used properly and you should never use it in any insert/update operations. The general advice is that if you're not 100% sure it's ok to read the dirty data, then don't use NOLOCK.--Lumbago |
|
|
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2008-05-13 : 04:24:35
|
| <stuck record mode>Personally I like to use the multi row versioning. No reader is blocked by updates so NOLOCK goes away along with all the associated issues. It gives you data consistent as at the time your transaction started.</stuck record mode> |
|
|
|
|
|
|
NOLOCK Option ?