?
Solved

WITH (NOLOCK) FOR COMMITTED DATA ONLY

Posted on 2011-04-27
11
Medium Priority
?
550 Views
Last Modified: 2012-05-11
Hello All,

I have used with (nolock) in my sql strings to improve the performance of query retreival but because it returns me uncommitted data, I am facing issues at the moment. Is there any method I can specify for the SQL to return only committed data.
0
Comment
Question by:sritharanb
  • 5
  • 3
  • 2
  • +1
11 Comments
 
LVL 4

Expert Comment

by:nativ
ID: 35474265
If you want only unlocked data used READPAST
But it will not return any locked date even if it is committed.
0
 
LVL 4

Expert Comment

by:nativ
ID: 35474285
By the way, reading only committed data is the default SQL Behavior.
So if you do not put any query hit that is what you will get.
But that of course will not improve performance.
0
 

Author Comment

by:sritharanb
ID: 35474290
I am looking at locked but committed data. How do we use READPAST?
0
Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

 

Author Comment

by:sritharanb
ID: 35474370
If I do not use any WITH keyword, the query on the locked table just goes on forever. I want to view all data excluding the ones under transaction
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 35474519
you shall not use NOLOCK, then, and use the best performance without that hint
0
 

Author Comment

by:sritharanb
ID: 35474608
Can you please explain more specifically how to use the best performance?
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 35474688
usually, indexes or query rewrites are the best tools.
other methods are to eventually denormalize data, and/or materialized views ...

without seeing the full query, the table design and the indexes present one cannot give more concrete tips.
0
 
LVL 21

Expert Comment

by:Alpesh Patel
ID: 35474759
Please set Isolation level to Read Comitted
0
 
LVL 4

Accepted Solution

by:
nativ earned 1500 total points
ID: 35481180
READPAST is used the same as NOLOCK.
The difference is as follows:
NOLOCK: reads all records in their current state, ignoring any locks. Thus it will give data which is not committed.
READPAST: reads all records which have no locks on them, ignoring any records which have locks on  them: thus it will only give committed data.
BUT - if there is a record which has a lock on it, even if it not in the process of being updated, it will not be returned.
So it depends on what you want.
If you want unreliable data: use NOLOCK
If you want reliable but incomplete data : use READPAST
if you want reliable and complete data: do not put a lock hit.
If that is taking a long time, then you need to investigate why you have a lock for such a long time on the table.
The solution is not to try to bypass or ignore the lock, but rather to figure out which query do you have running that is creating a lock on the table. A query should put a lock on a table for the minimal amount of time possible, to let other queries get to the data.
0
 

Author Comment

by:sritharanb
ID: 35490351
I will go with nativ's answer. This has definitely taken care of a major chunk of my problem. As mentioned any other lock, has to be closely investigated.
0
 

Author Closing Comment

by:sritharanb
ID: 35490356
This took care of 80 % of my issues
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question