Determining threshold for lock escalation

问题:

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!

回答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)

回答2:

As @Martin states, 5000 is the number BOL gives, however i've seen the actual number vary in production.

You have two options:

1) Batch your updates and try to keep the batchsize under 5000

2) Disable lock escalation (becareful) via:

• *ALTER TABLE (sql2k8)

• *Trace Flags 1211/1224

  (SQL Server 2008: Lock escalation changes)

• By other locking tricks

Here's a method you can use to systematically determine your threshold. (Assuming you have VIEW SERVER STATE permissions).

DECLARE @BatchSize int;
SET @BatchSize = <Vary this number until you see a table lock taken>;

BEGIN TRAN

UPDATE TOP(@BatchSize) 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


SELECT
    * 
FROM
    sys.dm_tran_locks
WHERE
    [request_session_id] = @@spid


ROLLBACK

回答3:

ROWLOCK hint does not prevent lock escalation, it just informs the server that it should not assume initial locking level and start from rows.

Row locks then may be promoted to a table lock.

To make the table data available for reading during the update, use SNAPSHOT transaction isolation level.