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.

t.price = d.latestprice * f.rate
trade (nolock) t
inner join tradedetails (nolock) d on d.ID = t.ID
inner join fxdetails (nolock) f on f.CCY = t.CCY
  • 1
    The (nolock) hints are an attempt to avoid deadlocks? – ypercubeᵀᴹ Feb 8 '13 at 10:58
  • 3
    How much time does the (corresponding) Select take? (SELECT t.price, d.latestprice, f.rate FROM ...) Have you checked the execution plan to find out why it is taking so much time to update only 1000 rows? – ypercubeᵀᴹ Feb 8 '13 at 11:01
  • Can you give some more information about the tables involved in the JOIN? Or post an execution plan of the SELECT to see why it's slow. – Tony Feb 8 '13 at 11:07
  • @Tony SQL-Server has execution plans for UPDATE as well. – ypercubeᵀᴹ Feb 8 '13 at 11:14
  • I guess my question is, say only 2 rows would be updated and I just updated these with 1 join across a temp table that has the latestprice and rate I need. In terms of locking the table t, is there any difference between this or using the above SQL which after all the joins will update the same 2 rows? So will the table t, just be locked on the 2 rows with the ID's I want to amend in both cases? Or is there a difference between the 2 approaches? – user2051876 Feb 8 '13 at 11:14

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.

| improve this answer | |

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


| improve this answer | |
  • Also try and find if there are any ways you can restrict the data before you join i.e. conditions in the WHERE clause. The earlier restriction takes place the less chance it will slow things down later in the query. – Baza Zeejay Ahdi Feb 8 '13 at 11:30
  • 1
    What happens if the data in the base table changes between the time you populate the temp table and the time the update statement runs? I'd be hesitant to recommend using a temp table without knowing that it wouldn't be an issue. – StrayCatDBA Feb 8 '13 at 18:26
  • @StrayCatDBA your concern is valid, but the same problem can occur with all other isolation levels except for serializable and snapshot. – A-K Feb 10 '13 at 3:15

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:

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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.