SQL Server 中的永久行级锁定

Question

我正在执行一个存储过程，它对几个非常小的表进行了一百多次更新，但我得到的性能非常差，并且所有并发用户似乎都在不断地互相阻塞。

虽然计划在今年晚些时候进行完整的存储过程重写，但在此期间，我希望通过强制对每个受影响的表使用行级锁来缓解问题。

在Sybase上，您可以（或至少可以，在2007年左右）使用以下语句为表强制使用行级锁：

alter table titles lock datarows

在SQL Server中，似乎唯一能获得相同效果的方法就是在每个更新或插入语句中都使用WITH(ROWLOCK)。即使如此，它也只是一个可能被忽略的提示。

在SQL Server中，有没有一种方法可以强制（或更强烈地）倾向于为给定表中的所有更新进行行级锁定？

- Cobus Kruger

---

也许这个问题的答案可以帮到你：https://dev59.com/anA75IYBdhLWcg3wxcHV - Bridge

你还应该考虑使用隔离级别“快照”。它在读取数据时避免了锁定，这是我们应用程序中的一个主要问题。 - Stefan Steinegger

Answer 1

首先，请确保更新操作不是表扫描。换句话说，您确实有适当的索引（UPDATE 也需要索引...）。在确保这一点之后，经过仔细考虑，禁用所使用的索引上的页面锁定：

ALTER INDEX ... WITH (ALLOW_PAGE_LOCKS = OFF, ALLOW_ROW_LOCKS = ON);

给这个操作同样的重视，就像你把TNT桶和凝固汽油瓶摇动时那样小心...

---

附加信息（来自下面的评论）：

• 您可以在聚集索引上禁用页面锁定，但无法在堆上禁用页面锁定（因为它们是物理结构，如果不锁定页面，则无法进行正确的锁层次结构）。

• 锁升级是一个（相关但）不同的主题。只有当语句选择行级锁粒度并在执行过程中决定升级到行集粒度级别时，锁才会升级。确实，OP可能是升级的受害者，但从我读到的OP来看，更可能的原因只是缺少索引（即由于扫描而事先选择了高锁粒度，未触发升级）。

• 初始粒度级别是引擎对手头任务的估计结果。如果估计表明将需要锁定大量行，则可能选择页面粒度，因为获取大量行锁通常存在问题。缺乏索引将触发扫描，通常会选择页面粒度。

• 此外，升级是从行/页面到行集（对象）粒度。通过中间页级别会出现并发问题，因此使用“大锤”。

- Remus Rusanu

---

为什么只在索引上呢？你也可以在表上强制使用行锁。 - Stefan Steinegger

@Stefan：实际上不行，参见 ALTER TABLE。你可以在聚集索引上禁用页面锁定，但是你不能在堆上禁用页面锁定（因为它们是物理结构，如果不锁定页面就无法进行正确的锁层次结构）。 - Remus Rusanu

锁升级是一个相关但不同的主题。只有当语句选择行级锁粒度并在执行期间决定升级到行集粒度级别时，锁升级才会出现。虽然OP可能实际上是升级的受害者，但从我阅读OP的方式来看，更可能的原因只是缺乏索引（即由于扫描而提前选择了高锁粒度，没有触发升级）。 - Remus Rusanu

我不理解其中的区别。我认为语句通常以行锁定开始，而页面锁定是由于锁升级而生成的。然而，当 OP 在并行事务中更新不同行并且出现锁定问题时，最有可能是由于升级引起的。 - Stefan Steinegger

初始的粒度级别是引擎对手头任务的估计结果。如果估计表明需要锁定大量行，则可能会选择页面粒度，因为获取大量行锁通常会出现问题。缺乏索引将触发扫描，通常会选择页面粒度。 - Remus Rusanu

升级也是从行/页到行集（对象）粒度的。首先通过中间页面级别会存在并发问题，因此使用“大锤子”。 - Remus Rusanu

Answer 2

您可以使用此选项来禁用锁升级：

ALTER TABLE titles SET (LOCK_ESCALATION=DISABLE)

请参阅关于锁升级的文档。

- Stefan Steinegger

Answer 3

1. 跟踪标志1211
2. 通过alter index关闭页面级别锁定

这两个的组合将为您提供行级锁定。除非SQL直接转向表锁定，否则不会出现问题 :) 还有1224标志，但它会忽略您在内存压力下的请求，而1211不会。内存消耗可能会大幅增加，请注意并进行一些用户活动模拟（RML或Benchmark Factory），并运行perfmon进行观察。我知道过去有人这样做过。

- Ilya