0

Azure SQL Managed Instance. If I am updating a temp table, and joining to another table or tables, do the joined tables also get locked, even though the update statement is only updating the temp table? I'm trying to figure out why this query would cause blocking.

UPDATE M SET M.MatchWgt = M.MatchWgt + 80
FROM #MatchPossibles M JOIN LostHD L ON M.PossibleMatchID = L.ID
JOIN LostHD L1 ON L1.PhoneNumber = L.PhoneNumber AND L1.ID = M.IDtoMatch;

Should I be using the NOLOCK hint on the "read only" tables?

8
  • 1
    How do you know this query is causing blocking?
    – Dale K
    Oct 6 at 22:36
  • @DaleK it is part of a series of similar queries in a stored procedure. The sp is causing blocking, which I have determined using a variety of tools including sp_who2.
    – CB_Ron
    Oct 6 at 22:46
  • 1
    Have you determined which tables are causing blocking?
    – Dale K
    Oct 6 at 22:47
  • 1
    Any table being accessed will have locked placed on it to ensure the ACID properties of the database. But if the table is only being read, then only shared locks should be placed which won't prevent reading elsewhere. The simple answer to "Should I be using the NOLOCK hint on the "read only" tables?" is usually NO, but ultimately its your decision - you just need to be very aware of the consequences of doing so.
    – Dale K
    Oct 6 at 22:48
  • 3
    sp_who and sp_who2 are quite unreliable. You're better off using sp_WhoIsActive to troubleshoot blocking scenarios. There's quite a bit of useful documentation about how to use it. Oct 6 at 23:17

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.