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?

  • 1
    How do you know this query is causing blocking?
    – Dale K
    Oct 6, 2021 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, 2021 at 22:46
  • 1
    Have you determined which tables are causing blocking?
    – Dale K
    Oct 6, 2021 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, 2021 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, 2021 at 23:17


Your Answer

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