Nolock use or Read_Committed_snapshot isolation level

this is last time on this topic i want to clarify myself with the NOLOCK using in my script.

when i use some told me dirty read and some times it get essential to use as it locked the table and other who are using not able to run simple select as some of them running a typical query with lots of time cosumption.

and how use of it get differ with platform. i am specially interested with production envirionmnt.... question needs to be cleared from you respected expertise.......

also to know disadvantage and all point to keep in mind for Read_Committed_snapshot isolation level

more ▼

asked Nov 09, 2011 at 11:03 PM in Default

avatar image

Sri 1
11 9 9 11

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

Using (NOLOCK) is not a practice that I would consider in most situations, mainly for the reason you already mentioned (dirty reads).

If you are experiencing locking and blocking, I would first think that there are other issues on your system that need looking at that are causing you to look at NOLOCK and row versioning solutions.

Quite often, poor query design and inadequate indexing cause systems to experience the sort of issues you are hinting at.

Be that as it may, to answer on the Read Commited snapshot isolation level, take a look at the Row Verisioning information on MSDN

more ▼

answered Nov 09, 2011 at 11:47 PM

avatar image

26.2k 18 35 48

actually there are multiple sp are made such like that and i am also not getting point for using NOlock in every SP. as i am new for this company database i was analysing the script for all objects.

what you will suggest i sud use Read Commited snapshot isolation level on or saty with nolock in code. or i can analayse that

Nov 10, 2011 at 12:58 AM Sri 1

@Sri, This cannot be answered in YES OR NO. This all depends upon your environment. As William has rightly point out that there could be poor designs, inadequate indexes, long running queries etc. which should be get rid off. As far as NOLOCK is concerned, I am against it, but have seen its use while fetching fields of a table where some fields would remain static (would never be changed OR change after production hours) and rest of the fields can be changed during production hours (but not fetched in the query). I have also seen its usage in history tables and reporting. This purely tells you that you need to address this according to your needs. The exercise you are talking about, needs rigorous testing, quite a lot of time and good analytical skills.

Nov 10, 2011 at 05:00 AM Usman Butt
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Nov 09, 2011 at 11:03 PM

Seen: 1622 times

Last Updated: Nov 09, 2011 at 11:45 PM

Copyright 2017 Redgate Software. Privacy Policy