I performed a load testing on our environment,where for eg. stored procedure say "sp" was called multiple times to process on different set of ids.

During load testing this store procedure encountered multiple blocking over single record situated in table say "A"

Out of all these blocking, first blocking was occurred while the proc was deleting some records from table A in one session and blocker script was trying to insert few records in table B in another session.

But table A and B does not have any relation between them (no foreign key over each other and no triggers) Although they have primary key which is a foreign key on some other common table. But I think that should not be the issue.

the mentioned blocking has wait type : LCK_M_U

I could not figure out the exact reason, which is causing the blocking.

  • Try running sp_WhoisActive @get_locks = 1, and you can see the locks that both statements hold. That'll help you find out if there's a begin/end tran mismatch (like an earlier proc hasn't committed its transactions), or maybe there's a trigger between tables, or if the foreign keys are a problem. If you want help, post the output of the locks column XML as part of your question. – Brent Ozar Apr 20 '18 at 10:15
  • There's a trigger. There's always a trigger. – Erik Darling Apr 20 '18 at 10:28
  • You could also try capturing a blocked process report and post it. – Tom V - try topanswers.xyz Apr 20 '18 at 11:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.