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

Question

Suppose my update query looks like the following:

UPDATE a SET 
    a.colSomething= 1
FROM tableA a WITH (NOLOCK)
INNER JOIN tableB b WITH (NOLOCK) 
        ON a.colA= b.colB
INNER JOIN tableC c WITH (NOLOCK) 
        ON c.colC= a.colA

Let's say the above joins to tableB and tableC takes several minutes to complete. In terms of table/row lock, does the entire table get locked during the join? Or is sql compiler smart enough to avoid locking the entire table?

And compared to the above query, is it less likely to get deadlocks by storing the result of the joins in a temp table first before the actual update, like the following?

SELECT a, b, c
INTO    
FROM tableA 
INNER JOIN tableB b WITH (NOLOCK) 
    ON a.colA= b.colB
INNER JOIN tableC c WITH (NOLOCK) 
    ON c.colC= a.colA

UPDATE a SET a.colSomething=1 
FROM tableA a INNER JOIN #tmp t ON a.colA= t.colA

Thanks!

Answer 1

Blocking vs. dead locking

I think you may be confusing locking and blocking with DEADLOCKS.

On any update query SQL server will lock the involved data. While this lock is active, other processes will be blocked ( delayed ) from editing the data. If the original update takes a long time ( from a users perspective' like a few seconds ) then front end system may seem to 'hang' or even timeout a users front end process and report an error.

This is not a deadlock. This blocking will resolve itself, basically non destructively by either delaying the user slightly or in some cases by forcing front end to be smart about the timeout. In the problem is blocking because of long running updates, you could fix the users having to resubmit by increasing the front end timeout.

A deadlock however cannot be resolved no matter how much you increase the timeout. One or the processes will be terminated with prejudice ( losing the update ).

Deadlocks have different root causes than blocking. Deadlocks are usually caused by inconsistent sequential logic in the front end, which accesses and locks data from two tables in different orders in two different parts of the front end. When these two parts operate concurrently in a multiuser environment they may basically, non deterministically , cause deadlocks, and essentially unsolvable data loss ( until the cause of the deadlocks is resolved ) as opposed to blocking which can usually be dealt with.

Managing blocking

Will SQL server choose row locks or whole table lock?

Generally , it depends and could be different each time. Depending on how many rows the query optimizer determines will be affected, the lock may be row or table. If its over a certain threshold, it will go table because it will be faster.

How can I reduce blocking while adhering to the basic tenets of transactional integrity?

SQL server is going to attempt to lock the tables you are joining to because their contents is material to generating the result set that gets updated. You should be able to show an estimated execution plan for the update to see what will be locked based on today's size of the tables. If the predicted lock is table, you can override perhaps with row lock hint, but this does not guarantee no blocking. It may reduce chance of inadvertent blocking of possibly unrelated data in the table. You will essentially always get blocking of data directly material to the update.

Keep in mind, however;

Also keep in mind the locks taken on the joined table will be Shared locks. Meaning other processes can still read from those tables, they just can't update them, until YOUR update is done using them as reference. In contrast, other processes will actively block on trying to simply READ data that you update has an exclusive lock on ( the main table being updated ).

So, joined table can still be read. Data to be updated will be exclusively locked as a group of records until the updates is complete or fails and is rolled back as a group.