6

Our application interfaces with a partner's application by calling one of their stored procedures. They insist on sprinkling NOLOCK hints throughout their code and this is a long stored procedure. We are intermittently getting bad data and suspect a race condition.

Is there any way, short of manually changing their stored procedure, that we can override their NOLOCK hints when executing it?

Note -- our code making the stored procedure call is a .NET application, in case that gives us any other options.

Improve this question
3
  • 3
    No, you cannot. Short of pre-locking all tables involved to prevent any concurrent writes...
    – Remus Rusanu
    Commented Aug 21, 2012 at 21:26
  • Well, he got that 106k by offering his expertise and having the rest of us give him props for it. :) +1 and thanks to Remus as well.
    – Calvin Fisher
    Commented Aug 22, 2012 at 14:58
  • You have the 2008 R2 tag on your question, so I should point out, there does not seem to be any performance advantage to using the hint NOLOCK. stackoverflow.com/questions/15770402/…
    – James Jenkins
    Commented Apr 29, 2013 at 13:37

1 Answer 1

Reset to default
5

No, query-level hints and options override session and server-level settings. Basically, a hint applied at the query level is the lowest common denominator, and wins (except in the case of MAXDOP and Resource Governor, but that's slightly different than what we're talking about here anyway).

This used to be documented for SQL Server 2000, but this verbiage has been removed from more modern versions of the documentation.

Improve this answer
1
  • Good in-depth answer!
    – Calvin Fisher
    Commented Aug 22, 2012 at 15:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.