控制锁的粒度

最新推荐文章于 2025-04-27 11:57:24 发布

最新推荐文章于 2025-04-27 11:57:24 发布 · 92 阅读

文章标签：

探讨了SQL Server中锁升级的机制及控制方法，包括锁升级的触发条件、跟踪标志1211的作用、ALTER TABLE SET LOCK_ESCALATION选项的应用，以及ALTER INDEX SET ALLOW_PAGE_LOCKS选项的影响。

作者：Kalen Delaney（SQL Server MVP）

日期：2009年5月3日

翻译：张洪举

原文：http://sqlblog.com/blogs/kalen_delaney/archive/2009/05/03/controlling-lock-granularity.aspx

在SQL Server 7.0 的所有版本中，引擎可以锁定行、页或整个表。此外，即使通过获取行或页锁的方式启动了查询，如果获取了太多的锁，SQL Server 也可能会将其提升到一个表锁。每个锁都需要使用内存，所以，当以一个表锁升级替换成千上万的细粒度锁，可以节省大量的资源。另一方面，一旦表以独占方式被锁定，则没有其他进程可以从中访问任何数据，从而大幅降低并发操作。

我们回到 SQL Server 6/6.5中，我们具有一些控制，像表的多少百分比被锁定才引发锁的粒度升级。但是，这些作为执行控制的配置选项在 SQL 7 中被移除掉了。所以，现在我们如何进行控制呢？

在下列情况下会发生锁升级（来自SQL Server 2008 内部，MS Press 2009）：

在一个对象或是对象的一个分区上单个语句持有的锁的数量超过了一个阈值，当前该阈值是 5000 个锁，但是在以后的 Service Pack 中有可能更改该值。如果锁散布在同一语句中的多个对象上，则不会发生锁升级，例如，在一个索引上有 3000 个锁，在另一个索引中也具有 3000 个锁。
如果 locks 配置选项设置为 0 ，当被锁使用的内存超过了非 AWE （ 32 位）或正常（ 64 位）可用内存的 40% ，将达到锁升级阈值。实际上，在这种情况下，锁的内存是根据需要动态分配的，因此 40% 值并不是一个常数。如果 locks 选项设置为非零值，在 SQL Server 启动时，为锁保留的内存是静态分配的。当 SQL Server 使用的内存超过了为锁资源保留的锁内存的 40% 以上时，将达到锁升级阈值。

我所见过的大部分对升级的讨论都是要阻止锁升级，从而能够最大化地访问数据。一种方法是在实例上启用跟踪标志1211，这可以防止从在任何情况下发生锁升级。这样做你需要非常小心，因为它会影响实例上的所有数据库中的表。另一种方法是跟踪 SQL Server 表的使用，因为即使有一个被不同连接锁定的行，也不会发生锁升级。所以，你可以将一个虚拟的行添加到表中，并开启一个事务进程，更新虚拟的行，然后将事务保持打开状态。虽然这可以防止任何人获得一个表锁，但此方法具有严重的副作用。即：只要该事务处于打开状态，日志不能截断过去这一点。但是，如果您可以将其与表的更新操作同步，并确保只要更新一完成就关闭虚拟更新，效果会好一些。

SQL Server 2008 为我们提供了更多的控制功能，在 ALTER TABLE 中添加一个新选项：

ALTER TABLE <table_name>

SET (LOCK_ESCALATION = [TABLE | AUTO |DISABLE]);

默认升级是 TABLE，并且在 SQL 2008之前这是唯一的升级选择。如果将该选项设置为 AUTO，锁可以升级到表或是一个分区（如果表已分区）。第三个选项是完全禁用此表的升级。此选项更易于管理对另一个对单行锁定的事务跟踪，以及更细粒度地禁止整个实例上的升级。

但是，你希望相反的行为怎么办？你总是希望通过表锁来节省资源怎么办？是这样的，锁提示可以请求为 TABLOCK 或 TABLOCKX，但这些设置必须在每个查询中指定。如果一些表一直想锁定在表级别怎么办？

SQL Server 在 ALTER INDEX 中提供了一个选项，用于禁止 ROW 和 PAGE 锁（在 SQL 7 和 2000中可以使用系统过程 sp_indexoption 来做这些事情)。如果索引是聚集索引，这意味着对于表，ROW 和 Page 锁是被禁止的。但是，如果表是一个堆，则不能使用此选项。

ALTER INDEX <index_name> ON <table_name>

SET (ALLOW_PAGE_LOCKS = OFF);

此外请注意如果将 ALLOW_PAGE_LOCKS 设置为 OFF，则不能 REORGANIZE （进行碎片整理）索引。

我通常在我的内部和调试课程中提及此 ALTER INDEX 选项，并且还有一次提到此选项是客户不恰当地关闭了 PAGE 和 ROW 锁，同时启动了许多莫名其妙的表锁。当你检查 sys.indexes （有名为 allow_row_locks 和 allow_page_locks的列，可能的值仅为 0 和 1）时，该行为不再是莫名其妙的。（对于 SQL Server 2005 之前的版本，可以使用 INDEXPROPERTY 函数来检查这些选项的状态)。这就是几年前我最后一次在遇到有人使用此选项来关闭精细粒度的锁，并且我正考虑从课程中删除对它的提及。

然而，上周在我的奥斯陆课上，一个学生询问为什么她具有 CTE 的查询在任何时候都启用表锁。我不相信它与 CTE 有关，但建议她等待一下，我们将在课程的第四天进行锁的讨论。她付出了很多关注，星期五的早上她回来对我说不是 CTE 的问题，而是有人关闭了 ALLOW_PAGE_LOCKS 和 ALLOW_ROW_LOCKS 选项！