CODEWITHSUNDEEP
sqlsql-server
c 2
c 2

Reputation: 1167

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

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!

Upvotes: 8

Views: 20698

Answers (3)

Clark Vera
Clark Vera

Reputation: 192

I had the exact same problem trying to update a table with 800K records joined to another table with 10 join conditions. This update took upwards of 30 minutes.

I reduced it down to 8 seconds by creating a temp table that contained ONLY the rows that needed to be updated from. Then I updated the first table with those results, only 20,000 actual rows had to be updated. The select command by default does not get logged, and I believe (but I am not sure), when you create a temporary table with a SELECT INTO , it also does not get logged (somebody please confirm).

When issuing an update on a large table joined with another large table, you are logging every updated field to the transaction log, then searching for the next available candidate, then logging the change again, and searching. If you can accept a dirty read, then creating a temp table with ONLY the records that will be updated FROM, you will drastically reduces your update time and therefore your chances of a deadlock.

It is also important to remove any functions in your where, such as ISNULL or even calculated string comparisons. These can radically increase your update time.

Upvotes: 0

Andyz Smith
Andyz Smith

Reputation: 708

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.

Upvotes: 5

jymbo
jymbo

Reputation: 1375

I would put indexes on your foreign keys, it can speed up update and delete operations as well as relieve your deadlock situation.

Upvotes: 0

Related Questions