SQL Update Efficiency and Blocking Concern Say I have some SQL similar to the below, which is an update with many joins. The number of rows actually being updated may be 1000's or potentially only a few. The actual join takes a long time as there are millions of rows involved, probably around 30s. Would the below only lock the trade table t on the rows that need to be updated or is it being locked as the joins are underway? So would it be better to use a CTE or temp table to gather this data and then update?

I'm having deadlock issues and wanted to make sure updates like this aren't part of the problem.

update
t
set
t.price = d.latestprice * f.rate
from
trade (nolock) t
inner join tradedetails (nolock) d on d.ID = t.ID
...
...
inner join fxdetails (nolock) f on f.CCY = t.CCY
Table locking is a good thing, it's done so your data doesn't get corrupted.

I wouldn't recommend using NOLOCK when writing data, you should have a look at the answers to Is the NOLOCK (Sql Server hint) bad practice?

An article linked from the question (SQL Server NOLOCK Hint &amp; other poor ideas) has as it's first recommendation:


  Do not use any HINTs until your testing proves that you have an issue
  that can’t be solved any other way than by using a HINT.


As ypercube commented, I would work out why your SELECT query is slow before applying lock hints to you query.