如何解决阻塞问题引起的 SQL Server 中的锁升级

文章翻译 文章翻译
文章编号: 323630 - 查看本文应用于的产品
展开全部 | 关闭全部

本文内容

概要

锁升级是将众多细粒度的锁 (如行或页锁) 转换为表锁的过程。Microsoft SQL Server 动态确定何时执行锁升级。此决定时,SQL Server 考虑的一个特定的扫描整个事务处理和正在使用的锁在系统中作为一个整体内存持有的锁数持有的锁的数量。 通常,SQL Server 的默认行为将导致发生只在那些点位置,它将提高性能,或当您必须减少过多的系统锁定内存多达到合理水平的锁升级。但是,一些应用程序或查询的设计可能会触发一次时,这是不值得,并提升的表锁可能会阻止其他用户的锁升级。本文讨论如何确定是否阻止和 $ 如何处理不需要的锁升级导致锁升级。

更多信息

如何确定是否锁定升级是否导致阻塞

锁升级不会导致大多数阻塞问题。若要确定是否围绕时间时,您会遇到阻塞问题出现的锁升级,启动 SQL 事件探查器跟踪,其中包括 锁定: 升级 事件。如果您看不到任何的 锁定: 升级 事件不在您的服务器上发生锁升级和本文中的信息不适用于您的具体情况。

如果出现的锁升级验证升级的表锁将阻塞其他用户。

有关如何识别的详细信息头的窗口阻止程序,以及如何确定所持有的头窗口阻止程序阻止其他服务器进程 id (spid) 的锁资源,请单击下面的文章编号,以查看 Microsoft 知识库中相应的文章:
224453了解和解决 SQL Server 7.0 或 2000年阻塞问题
如果阻塞其他用户锁定是 S 的 TAB 表级锁锁定模式的 (共享) 或 X (独占) 以外的其他,锁升级不是 S 的问题。在具体的而言 TAB 锁是意向锁 (如 IS、 IU,或 IX 锁定模式下),如果这不是锁升级的结果的。如果您阻塞问题不正在由锁升级,请参阅文章 Q224453 进行故障排除步骤。

如何防止锁定升级

若要防止锁升级最为简单、 最安全的方式是以保持事务简短并减少昂贵查询的锁定占用空间,以便不超过锁升级阈值。有几种方法来获取这一目标列出了其中的许多:
  • 分解成几个较小的操作的较大的批处理操作。例如对于假设您运行下面的查询来删除的审核表中的几个 100 千位旧记录,然后您发现它将导致一个锁升级,阻止其他用户的:
    DELETE FROM LogMessages WHERE LogDate < '2/1/2002'						
    按一次中删除这些记录几百可以极大地减少积累每个交易记录,并防止锁升级的锁数。例如:
    SET ROWCOUNT 500
    delete_more:
         DELETE FROM LogMessages WHERE LogDate < '2/1/2002'
    IF @@ROWCOUNT > 0 GOTO delete_more
    SET ROWCOUNT 0
  • 尽可能高效查询,从而减少查询的锁占用的空间。大扫描或大量的书签查找可能会增加锁升级的可能性 ; 此外,增加死锁的机会,并通常产生负面影响并发性和性能。 查找查询后,会导致锁升级,查找寻找机会创建新索引,或要将列添加到现有索引,若要删除索引或表的扫描和最大限度地提高索引的效率。请考虑将该查询粘贴到一个查询分析器的查询窗口可对其执行自动索引分析。若要执行此操作,在 查询 菜单上单击 索引优化向导 SQL Server 2000 中,或单击 执行索引分析 SQL Server 7.0 中。

    这种优化的一个目标是使索引搜索返回少的行,以最大限度地减少所需的书签查找 (最大化为特定的查询索引的选择性) 成本。如果 SQL Server 估计书签查找逻辑运算符可能返回多行,它可能使用一个预取执行书签查找。如果 SQL Server 确实使用预取对于的书签查找,则必须增加事务隔离级别的一部分在查询的可重复读到查询的一部分。这意味着什么可能看起来类似于在读提交的隔离级别的 SELECT 语句可能会获取数千个 (聚集的索引和上一个非聚集的索引),关键锁,这可能会导致超过锁升级阈值这样一个查询。这一点尤其重要,如果您发现升级的锁不但是,通常时检测不到默认的读提交的隔离级别的共享的表锁。如果一个带预取的书签查找子句导致升级,请考虑将其他列添加到查询计划中出现在索引查找或 $ 索引扫描逻辑运算符,下面书签查找逻辑运算符中的非聚集索引。它可能会创建一个覆盖索引 (索引,包括在查询中使用的表中的所有列),或是不切实际的至少一个涵盖了在如果所有内容包括在选择列列表中使用的联接条件,或在 where 子句中的列的索引。

    嵌套循环联接也可以使用预取,这将导致相同的锁定行为。

    有关详细的信息请单击下面的文章编号,以查看 Microsoft 知识库中相应的文章:
    260652使用嵌套的循环联接在"书签 LOOKUP....WITH 预取"可能会持有锁更长的时间
  • 如果不同的 SPID 当前持有一个不兼容的表锁,则不会发生锁升级。锁升级始终升级为表的锁,并不会页锁。此外,如果锁升级尝试将失败,因为另一个 SPID 持有的不兼容的 TAB 锁,尝试升级该查询不会阻止 TAB 锁在等待时。而是,获取锁在其原始的、 更精细的级别 (行、 键,或页) 将继续,定期进行其他问题上报尝试。因此,以防止在某一特定的表上的锁升级的一种方法是获取和保存与升级的锁类型不兼容的不同连接上的锁。ix (意向排它) 锁在表级别并不锁定任何行或页,但仍不兼容 (共享) 升级 s 或 X (独占) TAB 锁。例如对于假定您必须运行批处理作业的修改大量 mytable 的表中的行和已阻止会引起的出现是由于锁升级。如果此作业总是完成在小于一小时中,可能会创建 TRANSACT-SQL 作业包含下面的代码和安排新的作业启动批处理作业的开始时间之前的几分钟时间:
    BEGIN TRAN
    SELECT * FROM mytable (UPDLOCK, HOLDLOCK) WHERE 1=0
    WAITFOR DELAY '1:00:00'
    COMMIT TRAN				
    此查询获取并保存上 mytable ix 锁,这样可以防止在表上的锁升级在这段时间内的一个小时。该批处理并不修改任何数据或阻止其他查询 (除非其他查询强制使用 TABLOCK 提示表锁或如果管理员已禁用页或行锁通过使用一个 sp_indexoption 存储过程)。
此外,您可以通过启用跟踪标志 1211年禁用锁升级。但是,此跟踪标记将会禁用所有的锁升级的 SQL Server 实例中的全局范围内。锁升级 SQL Server 中都有非常有用的用途,通过最大限度地提高效率的查询,否则减速通过获取和释放的锁的几个千位的系统开销。锁定升级还有助于最大限度地减少所需的内存来跟踪的锁。 SQL Server 可以动态分配的锁结构的内存是有限的以便如果您禁用锁升级和 $ 锁定内存增长足够大,请为任何查询分配额外的锁的尝试可能失败,则出现下列错误:

错误: 1204 严重性: 19,状态: 1
该 SQL Server 无法获取锁定资源,这一次。重新运行您的语句,当有较少的活动用户时也可询问系统管理员联系,以检查 SQL Server 锁和内存配置。
注意将出现"1204"错误时, 它将停止当前语句的处理并导致的活动事务的回滚。回滚本身可能会阻止用户或如果您重新启动 SQL Server 服务导致长数据库恢复时间。

使用如 ROWLOCK 锁提示仅会改变初始锁计划。锁提示不能防止锁升级。

其他的方法防止锁升级本文内上文中讨论的是更好的选项,比启用跟踪标记。此外,其他方法通常会导致查询的性能比禁用锁升级为整个实例。Microsoft 建议启用仅以减轻严重阻止由其他的选项时的锁升级此跟踪标记,如那些曾进行过讨论前面此本文要调查。若要启用跟踪标记,使它处于打开状态时 SQL Server 启动时,将其添加为服务器启动参数。

若要向服务器启动参数用鼠标右键单击服务器在 SQL 企业管理器中,单击 属性,然后单击 常规 选项卡上的 启动参数,然后添加以下参数 (精确地如下所示):
-T1211
您必须重启 SQL Server 服务,为新的启动参数才能生效。如果您在查询分析器中运行以下查询跟踪标志将立即生效:
DBCC TRACEON (1211, -1)				
但是,如果没有添加该 -T1211 启动参数 traceon 命令的效果时将会丢失 SQL Server 服务关闭并重新打开。打开跟踪标志可防止任何将来的锁升级,但它不会对反向已经有一个活动事务中发生的任何锁升级。

属性

文章编号: 323630 - 最后修改: 2007年2月22日 - 修订: 10.3
这篇文章中的信息适用于:
  • Microsoft SQL Server 2000 标准版
  • Microsoft SQL Server 7.0 标准版
  • Microsoft SQL Server 2005 Standard Edition
  • Microsoft SQL Server 2005 Developer Edition
  • Microsoft SQL 2005 Server Enterprise
  • Microsoft SQL Server 2005 Express Edition
  • Microsoft SQL 2005 Server Workgroup
关键字:?
kbmt kbinfo KB323630 KbMtzh
机器翻译
注意:这篇文章是由无人工介入的微软自动的机器翻译软件翻译完成。微软很高兴能同时提供给您由人工翻译的和由机器翻译的文章, 以使您能使用您的语言访问所有的知识库文章。然而由机器翻译的文章并不总是完美的。它可能存在词汇,语法或文法的问题,就像是一个外国人在说中文时总是可能犯这样的错误。虽然我们经常升级机器翻译软件以提高翻译质量,但是我们不保证机器翻译的正确度,也不对由于内容的误译或者客户对它的错误使用所引起的任何直接的, 或间接的可能的问题负责。
点击这里察看该文章的英文版: 323630
Microsoft和/或其各供应商对于为任何目的而在本服务器上发布的文件及有关图形所含信息的适用性,不作任何声明。 所有该等文件及有关图形均"依样"提供,而不带任何性质的保证。Microsoft和/或其各供应商特此声明,对所有与该等信息有关的保证和条件不负任何责任,该等保证和条件包括关于适销性、符合特定用途、所有权和非侵权的所有默示保证和条件。在任何情况下,在由于使用或运行本服务器上的信息所引起的或与该等使用或运行有关的诉讼中,Microsoft和/或其各供应商就因丧失使用、数据或利润所导致的任何特别的、间接的、衍生性的损害或任何因使用而丧失所导致的之损害、数据或利润不负任何责任。

提供反馈

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com