CODEWITHSUNDEEP
sql-serverlocking
user1386187
user1386187

Reputation: 31

SQL Server mass update locking

Consider this statement:

update TABLE1 
set FormatCode = case T.FormatCode when null then TABLE1.FormatCode else T.FormatCode end,
    CountryCode = case T.CountryCode when null then TABLE1.CountryCode else T.CountryCode end 
    <SNIP ... LOTS of similar fields being updated> 
FROM TABLE2 AS T 
WHERE TABLE1.KEYFIELD = T.KEYFIELD

TABLE1 is used by other applications and so locking on it should be minimal TABLE2 is not used by anybody else so I do not care about it.

TABLE1 and TABLE2 contain 600K rows each.

Would the above statement cause a table lock on TABLE1?

How can I modify it to cause the minimal lock on it ?

Maybe use a cursor to read the rows of TABLE2 one by one and then for each row update the respective row of TABLE1?

Upvotes: 0

Views: 1350

Answers (2)

Chris Moutray
Chris Moutray

Reputation: 18399

As marc_s has suggested introducing a more restrictive WHERE clause to reduce the number of rows should help here.

Since your update occurs nightly it seems you'll be looking to only update the records that have updated since the previous update occurred (ie a days worth of updates). But this will only benefit you if a subset of the records have changed rather than all of then.

I'd probably try to SELECT out the Id's for the rows that have changed into a temp table and then joining the temp table as part of the update. To determine the list of Id's a couple of options come to mind on how you can do this such as making use of a last changed column on TABLE2 (if TABLE2 has one); alternatively you could compare each field between TABLE1 and TABLE2 to see if they differ (watch for nulls), although this would be a lot of extra sql to include and probably a maintenance pain. 3rd option that I can think of would be to have an UPDATE trigger against TABLE2 to insert the KEYFIELD of the rows as they are updated during the day into our temp table, the temp table could be cleared down following your nightly update.

Upvotes: 0

Namphibian
Namphibian

Reputation: 12221

Sql will use row locks first. If enough rows in a index page is locked SQL will issue a page lock. If enough pages are locked SQL will issue a table lock.

So it really depends on how many locks is issued. You could user the locking hint ROWLOCK in your update statement. The down side is that you will probably have thousand of row lock instead of hundreds of page locks or one table lock. Locks use resources so while ROWLOCK hints will probably not issue a table lock it might even be worse as it could starve your server of resources and slowing it down in anycase.

You could batch the update say 1000 at a time. Cursors is really going to news things up even more. Experiment monitor analyse the results and make a choice based on the data you have gathered.

Upvotes: 1

Related Questions