确定锁升级的阈值 [英] Determining threshold for lock escalation

问题描述

我有一个包含大约 250 万条记录的表，将更新其中大约 70 万条记录，并且希望在更新这些记录的同时仍允许其他用户查看数据.我的更新语句如下所示:

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

我想知道是否有任何方法可以确定 sql server 在什么时候会升级放置在更新语句上的锁(因为我不希望整个表都被锁定)?

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?

谢谢！

推荐答案

根据BOL 一旦语句在对象的单个实例上获得了 5,000 个行或页级锁，就会尝试升级锁.如果这次尝试失败，因为另一个事务有一个冲突的锁，那么它会在每额外获得 1,250 个锁后重试.

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)

这篇关于确定锁升级的阈值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！