sql server- When does table get locked when updating with join -

suppose update query looks following:

update set      a.colsomething= 1 tablea (nolock) inner join tableb b (nolock)          on a.cola= b.colb inner join tablec c (nolock)          on c.colc= a.cola 

let's above joins tableb , tablec takes several minutes complete. in terms of table/row lock, entire table locked during join? or sql compiler smart enough avoid locking entire table?

and compared above query, less deadlocks storing result of joins in temp table first before actual update, following?

select a, b, c     tablea  inner join tableb b (nolock)      on a.cola= b.colb inner join tablec c (nolock)      on c.colc= a.cola  update set a.colsomething=1  tablea inner join #tmp t on a.cola= t.cola 

thanks!

blocking vs. dead locking

i think may confusing locking , blocking deadlocks.

on update query sql server lock involved data. while lock active, other processes blocked ( delayed ) editing data. if original update takes long time ( users perspective' few seconds ) front end system may seem 'hang' or timeout users front end process , report error.

this not deadlock. blocking resolve itself, non destructively either delaying user or in cases forcing front end smart timeout. in problem blocking because of long running updates, fix users having resubmit increasing front end timeout.

a deadlock cannot resolved no matter how increase timeout. 1 or processes terminated prejudice ( losing update ).

deadlocks have different root causes blocking. deadlocks caused inconsistent sequential logic in front end, accesses , locks data 2 tables in different orders in 2 different parts of front end. when these 2 parts operate concurrently in multiuser environment may basically, non deterministically , cause deadlocks, , unsolvable data loss ( until cause of deadlocks resolved ) opposed blocking can dealt with.

managing blocking

will sql server choose row locks or whole table lock?

generally , depends , different each time. depending on how many rows query optimizer determines affected, lock may row or table. if on threshold, go table because faster.

how can reduce blocking while adhering basic tenets of transactional integrity?

sql server going attempt lock tables joining because contents material generating result set gets updated. should able show estimated execution plan update see locked based on today's size of tables. if predicted lock table, can override perhaps row lock hint, not guarantee no blocking. may reduce chance of inadvertent blocking of possibly unrelated data in table. blocking of data directly material update.

keep in mind, however;

also keep in mind locks taken on joined table shared locks. meaning other processes can still read tables, can't update them, until update done using them reference. in contrast, other processes actively block on trying read data update has exclusive lock on ( main table being updated ).

so, joined table can still read. data updated exclusively locked group of records until updates complete or fails , rolled group.