Determining threshold for lock escalation

Question

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!

Answer 1

According to BOL once the statement has acquired 5,000 row or page level locks on a single instance of an object an attempt is made to escalate the locks. If this attempt fails because another transaction has a conflicting lock then it will try again after every additional 1,250 locks are acquired.

I'm not sure if you can actually take these figures as gospel or not or whether there are a few more subtleties than that (I guess you could always hit the memory limit for the instance at any number of locks)