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

Sri 1 gravatar image

Sri 1
11 9 9 10

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

1 answer: sort newest

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][1]

[1]: http://msdn.microsoft.com/en-us/library/ms177404.aspx
more ▼

answered Nov 09, 2011 at 11:47 PM

WilliamD gravatar image

25.9k 17 19 41

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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

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: 1414 times

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