title: sql server- When does table get locked when updating with join
body: <p>Suppose my update query looks like the following:</p>
  
  <pre><code>UPDATE a SET 
      a.colSomething= 1
  FROM tableA a WITH (NOLOCK)
  INNER JOIN tableB b WITH (NOLOCK) 
          ON a.colA= b.colB
  INNER JOIN tableC c WITH (NOLOCK) 
          ON c.colC= a.colA
  </code></pre>
  
  <p>Let's say the above joins to tableB and tableC takes several minutes to complete. In terms of table/row lock, does the entire table get locked during the join? Or is sql compiler smart enough to avoid locking the entire table?</p>
  
  <p>And compared to the above query, is it <strong>less likely to get deadlocks</strong> by storing the result of the joins in a temp table first before the actual update, like the following?</p>
  
  <pre><code>SELECT a, b, c
  INTO    
  FROM tableA 
  INNER JOIN tableB b WITH (NOLOCK) 
      ON a.colA= b.colB
  INNER JOIN tableC c WITH (NOLOCK) 
      ON c.colC= a.colA
  
  UPDATE a SET a.colSomething=1 
  FROM tableA a INNER JOIN #tmp t ON a.colA= t.colA
  </code></pre>
  
  <p>Thanks!</p>