SQL Update Efficiency and Blocking Concern

Question

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

Answer 1

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 & 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.

Answer 2

I have always found it is more efficient when you get the exact data that you want to update and store it in a temporary table. This will make the Update statement as simple as possible and will most definitely speed things up a bit.

Hope this helps

B

Answer 3

SQL Server will U-lock any row that it needs from the table or view or CTE referenced in the FROM clause. If it needs to scan the table, it U-locks it. If it needs to read 3 rows, it only U-locks them.

This is usually a good thing but you might try the following workaround:

UPDATE t1 SET ...
FROM table t1
JOIN table t2 on t1.ID = t2.ID
JOIN ...

Introduce a redundant join. Do all the filtering on t2 which is not locked. When the set of rows to be updated is determined SQL Server will probably seek into t1 and only lock exactly the rows that need to be updated.

The full discussion of this pattern and associated locking is too long for this answer. It is worth playing around with it.

Actually, you can also use a temp table to store the update information. That has more overhead though and not needed here.