We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

WITH (NOLOCK) FOR COMMITTED DATA ONLY

Medium Priority
572 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.
Comment
Watch Question

Commented:
If you want only unlocked data used READPAST
But it will not return any locked date even if it is committed.

Commented:
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.

Author

Commented:
I am looking at locked but committed data. How do we use READPAST?

Author

Commented:
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
Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
you shall not use NOLOCK, then, and use the best performance without that hint

Author

Commented:
Can you please explain more specifically how to use the best performance?
Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
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.
Alpesh PatelSolution Architect at TCS (SAFe®Agilist, TOGAF® Foundation, PSM I®, MCP, MCSD)
CERTIFIED EXPERT

Commented:
Please set Isolation level to Read Comitted
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
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.

Author

Commented:
This took care of 80 % of my issues
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.