确定锁升级的阈值

Question

我有一个大约有250万条记录的表，将更新其中大约70万条记录，并希望在更新这些记录的同时仍然允许其他用户看到这些数据。我的update语句如下所示：

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

我想知道是否有任何方法可以计算出sql server将在什么时候升级update语句上的锁(因为我不希望整个表都被锁定)？

我没有权限运行服务器跟踪来查看锁是如何应用的，所以有没有其他方法可以知道锁将在什么时候升级到覆盖整个表？

谢谢!

Answer 1

According to BOL一旦语句在对象的单个实例上获得了5,000个行级或页级锁，就会尝试升级这些锁。如果由于另一个事务具有冲突的锁而导致此尝试失败，则它将在每获得1,250个额外的锁之后重试。

我不确定你是否真的可以把这些数字当做福音，或者是否还有更多的微妙之处(我猜你总是可以在任意数量的锁上达到实例的内存限制)

Answer 2

正如@Martin所说，BOL给出的数字是5000，但我看到实际数字在生产中有所不同。

您有两个选择：

1)批量更新，并尝试将批大小保持在5000以下

2)通过以下方式禁用锁升级(保持谨慎)：

• *ALTER TABLE (sql2k8)
• *Trace标志1211/1224

的(SQL Server 2008: Lock escalation changes)

• By其他锁定技巧

这里有一种方法，你可以用来系统地确定你的阈值。(假设您具有VIEW SERVER状态权限)。

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

Answer 3

ROWLOCK提示不会阻止锁升级，它只是通知服务器不应该采用初始锁级别，而是从行开始。

然后，可以将行锁提升为表锁。

要使表数据在更新期间可供读取，请使用SNAPSHOT事务隔离级别。