-1

I have been facing something new issue. SP_OAMethod is causing blocking with other normal queries. I have a procedure which includes select queries, and insert, update queries with begin transaction and end transaction respectively. I have checked if any query with missing nolock statement. But I found no missing query without any nolock statement. Later I got to know that SP_OAMethod is the actual culprit which is the headblocker. Could anyone please help me fix this issue?

8
  • "SP_OAMethod is causing blocking with other normal queries." - You've determined this how? "But I found no missing query without any nolock statement." - This is bad. You shouldn't use the NOLOCK hint at all.
    – J.D.
    Feb 12 at 13:14
  • 3
    I, personally, wouldn't use sp_OA* methods. dba.stackexchange.com/questions/333366/… Feb 12 at 14:41
  • @J.D. Well, if we stop using NOLOCK hint, how can we avoid blocking?
    – dbajayy
    Feb 13 at 8:54
  • Nolock hint will not give accurate results sometimes, but we have lot many update, insert, delete statements in live DB, which are resulting in lot many blockings if we don't use NOLOCK hint
    – dbajayy
    Feb 13 at 9:32
  • @dbajayy "we have lot many update, insert, delete statements in live DB, which are resulting in lot many blockings" - The NOLOCK hint doesn't prevent blocking between those kinds of statements from each other, i.e. writers blocking writers, FWIW. "if we stop using NOLOCK hint, how can we avoid blocking?" - Use the proper isolation levels for each context of concurrency you need. If you need writers to not block readers, and visa-versa, then use an optimistic concurrency isolation level such as RCSI. Also, you didn't answer my first question but see Sean's link on why sp_OA* is bad too.
    – J.D.
    Feb 13 at 13:36

0

Your Answer

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

Browse other questions tagged or ask your own question.