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 |
|
evesnight
Starting Member
10 Posts |
 Posted - 2010-07-29 : 02:01:10
|
| Just looking for some context as to why someone would use this isolation level.Thanks! |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
evesnight
Starting Member
10 Posts |
Posted - 2010-07-29 : 02:23:41
|
| What I'm looking for then is some scenarios when it's best to use a WITH (NOLOCK) table hint. Clearly it must be when you're just reading data and not updating or inserting. In my mind it's appropriate to use when, for instance, one is running a search query that would take longer than say 1 second of query time. If someone is trying to insert a new record in one of the tables you are searching on you do not want to prevent that insert from occurring because you are searching on that table. Is this a correct understanding? |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-07-29 : 02:37:40
|
| DO NOT USE WITH (NOLOCK) EVERThat would be my advice.Then relax the policy only where you MUST.The ONLY place we use it is in some SysAdmin queries on logging tables with very high INSERT rates which are basically write-only - any blocking would be a disaster for the application. The sysadmins know the issues that may arise on those reports, and where the data can, and can not, be trusted.For instances where Reads are blocking Writes use READ_COMITTED_SNAPSHOT (which was the SQL2005 solution for what most people used NOLOCK for prior to that)If you use NOLOCK you will get the same record twice, and some records not at all.Unfortunately, although you will get this, it will be very (maybe incredibly) infrequent. And unreproducible. So the odd errors your users see will never be solved. And maybe someone will take a business decision on what they see on the screen - profit twice (or half) what it was 5 minutes ago </facetious>With NOLOCK a page split can occurring during retrieval. You may read the first, full, page and then then the half, new, page - thus getting the second half of the rows twice. Or with slightly different timing you will get the second page only, and thus miss the rows now in the first half-page.If your query further processes rows then things that are deleted in mid-process may cause application errors downstream (again hard/impossible to reproduce / diagnose)Avoid!!!!! |
|
|
|
evesnight
Starting Member
10 Posts |
Posted - 2010-07-29 : 02:47:36
|
| Gotcha so basically don't use NOLOCK and DO use SnapShot isolation when you don't want a read on a table to prevent a write to the same table correct? Would it then be best practice to use snapshot isolation whenever performing only reads on tables and readcommitted within a transaction when performing writes? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-07-29 : 03:01:31
|
| Personally we set the database to READ_UNCOMITTED_SNAPSHOT and then don't anything else (except use a TRANSACTION around any multi-statement database updates that need to be ATOMic) |
|
|
|
evesnight
Starting Member
10 Posts |
Posted - 2010-07-29 : 14:11:00
|
| Ok that makes sense. Snapshot Isolation is only available in SQL >= 2005 yes? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
|
|
|
|
Why do you use READ UNCOMMITTED or WITH (NOLOCK)