Determining threshold for lock escalation
- by Davin
I have a table with around 2.5 millions records and will be updating around 700k of them and want to update these while still allowing other users to see the data. My update statement looks something like this:
UPDATE A WITH (UPDLOCK,ROWLOCK)
SET A.field = B.field
FROM Table_1 A
INNER JOIN Table2 B ON A.id = B.id WHERE A.field IS NULL
AND B.field IS NOT NULL
I was wondering if there was any way to work out at what point sql server will escalate a lock placed on an update statement (as I don't want the whole table to be locked)?
I don't have permissions to run a server trace to see how the locks are being applied, so is there any other way of knowing at what point the lock will be escalated to cover the whole table?
Thanks!
