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